using SQLite with mod_perl

Question

I have been successfully using SQLite as a data store for my web applications, but now I am implementing a web site with mod_perl, and am running into database locking issues.

As expected, my entire web application is loaded by the Plack Apache handler (Plack::Handler::Apache2) when the web server is started. Well, the first db query creates a lock on the entire database, and any subsequent query that has to modify the db fails.

What is my way out? Can I use SQLite in a persistent web environment or not? Should I be looking for some other db store?

I am not a fan of MySQL, and don't want to use it. I could potentially use PostGres, but I'd rather use something lightweight, and preferably sql-based as using key/value databases such as Tokyo Cabinet would require learning a whole new way. I'd rather really use SQLite.

Answer 1

If you have an open handle to the database, it can cause this issue. I have had problems when iterating over a result set during a log process causes the lock to stick around.

Try and fetch all the rows for the query and call $sth->finish() to clear up the lock. You will use a little more memory, but you will avoid the locking.

Knowing you are going to do this, you can make use of $sth->fetchall_arrayref() or $sth->fetchall_hashref()