How / why does your first query create a lock on the database that is never released?

Ideally, each HTTP request would query your database and if it writes, lock the database while updating and otherwise use no locks at all. I think you should create your database handle at server startup and then reuse that handle in each request. I'm not sure where you have problems, but as you haven't posted any code, it's hard to diagnose.

Re-using an open database handle would be bad advice if it is a forking server (see the last sentence in this SQLite FAQ answer), perhaps using something like DBIx::Connecter (like Apache::DBI in the mod_perl world) might be helpful to manage the database connections properly.

> How / why does your first query create a lock on
> the database that is never released?

The application is loaded when server starts via Plack::Handler::Apache2. As soon as I query, a lock a placed on the entire db (SQLite doesn't have a row-level lock), and never let go. Any subsequent query, as long as it is a SELECT, works, but any other query that UPDATEs or DELETEs fails.

>but as you haven't posted any code, it's hard to diagnose

I don't know what code to post, in the sense that it is my entire application. Of course, there is no problem in using SQLite if use cgi, but I really do want to move to a persistent environment.

Other than the above problem, SQLite is really brilliant. I can't quite imagine anything (CouchDB, MongoDB, or any other new fangled data storage technologies) quite matching what it can do.

--

when small people start casting long shadows, it is time to go to bed

What error are you getting? When I run code like this twice, I don't get errors, I just get waiting until the other transaction is committed:


use strict;
use warnings;
use DBI;

my $dbh = 
DBI->connect("dbi:SQLite:dbname=test.sqlite","","",
                { RaiseError => 1, AutoCommit => 1 });
$dbh->begin_work;
$dbh->do("UPDATE abc SET foo = 'heyo'");
my @x = <>;
$dbh->commit;
[download]

This is why I think it would be a poor choice in a web-application as it severly limits scalability.

As for the locks, you should ensure that your application either does a commit or a rollback on the database for every http-request it handles (if it touches the database at all).

This ensures that your applications leaves no locks in the database between requests.

According to this question in the SQLite faq, "Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however." If you aren't using the AutoCommit mode and you have an uncommitted transaction (which would lock the database), that may be your problem, so either commit soon after making an update or set AutoCommit on.

Alternatively, you may have kept the same database handle open across a fork call, which this mentions as the source of problems, so don't do that either.

Alternatively, you may have kept the same database handle open across a fork call, which this mentions as the source of problems, so don't do that either.

So, this may partly be just my ignorance. I am assuming that Apache2 is the forking server here. After all, it spawns little children to manage the web queries.

I am not sure how things work under a mod_perl (or even a fastcgi) kind of persistent perl environment under Apache2. I am assuming that my entire application is compiled and loaded when the server starts. At this point, a db handle is not created. Then, a user queries, which causes a db handle to be created. Since I don't want a new db handle created for subsequent queries by the same user, I reuse the db handle. And, I don't disconnect the db handle, because, if I do, then subsequent queries will fail. How do I ensure that a persistent, unique db handle is created for each user to my web site?

--

when small people start casting long shadows, it is time to go to bed