SQLite and Writes

Most DBI tutorials will show you how to use prepare()/execute(). Something like this:

my $sth = $dbh->prepare( 'INSERT INTO table (foo, bar) VALUES (?, ?)' ) or die ...;
$sth->execute( 1, 2 ) or die ...;

Most of us write DBI this way when we don't use DBIx::Class or some other abstraction layer (or we use $dbh->do(), but it won't end up changing my point). For most databases, the above is fine.

On SQLite, you can lose data that otherwise would have gone in fine.

The trouble is with the way SQLite handles concurrent writes. A typical RDBMS is a sophisticated beast with finely-tuned concurrency model built off decades of experience with writing data to spinning platters. SQLite, however, is a little too simple-stupid for that. Some might say that its simplicity has a place, but at the very least, developers need to be aware of this problem.

SQLite databases exist in a single file, which is widely considered a feature. When SQLite goes to write to a table, it grabs an exclusive lock on the entire file. Other threads/processes trying to write will wait a given amount of time to acquire a lock (which you can see with $dbh->sqlite_busy_timeout()). After that, it will error out with "database is locked".

(Note that since it uses fcntl() to lock the file, things can go very badly if used on NFS.)

If we consider the DBI code at the beginning, we see that the error handling is fine for most databases. Most errors coming back from prepare(), execute(), or do() are unrecoverable. They're almost invariably about SQL syntax errors or the wrong number of placeholders. Unless you're writing some very dynamic SQL, you'll probably catch these errors during development.

For SQLite, the execute() or do() can fail due to locking contention. If you wrote the code above, you could lose otherwise valid data. If we want to make sure the data is written and don't care how long it takes, then you need to set $dbh->sqlite_busy_timeout( 0 ) to turn off the busy timeout. If you do care how long it takes, then don't use SQLite.

If somebody ran into this problem, then on one hand you could say that they should have read the manual. On the other hand, SQLite is such an obvious candidate for a quick-and-dirty database that I can't say I blame developers for using it that way. Everything should Just Work with a minimum of fuss.

SQLite arguably has a place as an integrated database, but I think its usable niche is smaller than generally assumed. I'm not much of a MySQL fan, either, but for a quick mockup database, MySQL isn't much harder to deploy and avoids these sorts of problems.


Something that causes a visible error like this is not what people normally refer to as "data loss". In fact, if you use that definition then hard disks can have data loss because if it's full, otherwise valid data will cause an error.

Data loss for programmers is usually when a system tells you it took your data but then drops it under certain circumstances. Yes, this is a limit of SQLite, but it's not "data loss".

Useful information, thanks for that.

Is there any particular reason for the manual error checks on each step rather than setting the RaiseError flag? (this modified version of 02_busy.t seems to indicate that it throws an error as expected, on first read of your post I thought you were talking about an "invisible" data loss situation)

Also, switching to mysql (or other DBs) won't help much here, there's still the chance of deadlocks - see http://stackoverflow.com/questions/2596005/working-around-mysql-error-deadlock-found-when-trying-to-get-lock-try-restarti for example. Robust code would have to assume that pretty much any query can fail - and know when it's worth retrying, and when it's just not going to work. I don't use DBI myself, but would be surprised if there weren't a few options in DBIx::* for this?

This is why, when I have an app that uses sqlite, I jump through hoops to avoid opening it more than once. I put blocking 'flock' calls in the 'open' method of my database class. And I have it print a big fat warning message if it blocked. If I have a brain fart and try to open more than once, I get a loud early warning. Before I've caused any failed database updates.

Leave a comment

About Timm Murray

user-pic I blog about Perl.