Timm Murray

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 ...;
$sth->finish;

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.