Limits of multithreaded INSERTS in sqlite

Question

my perl script is multi threaded and in each thread i have to write something to a sqlite3 database. But as you might guess, i get a lot of

DBD::SQLite::db do failed: database is locked at script.pl line 264.

messages. I read that sqlite3 is able to handle multi threaded situations, even INSERT statements but i think i expect to much when inserting fro 8 threads at the same time.

ok, so its not possible this way but isn't there a possibility to perform a check before inserting to see if the database is locked (or busy) and then wait until is free again?

I really don't want to change to a "real" DBMS cause its only a simple script.

Thank you

Answer 1

If you need to block until you can get to the database, try exclusive transactions, i.e.,

$dbh->do("begin exclusive transaction") or die $dbh->errstr;
#inserts here
$dbh->do("commit transaction") or die $dbh->errstr;

That way, you delegate the locking to SQLite, rather than doing it in Perl. This is safer for all sorts of reasons, not least of which is you might have the database open in something other than Perl, or in another Perl process rather than a thread.

And, as @mob commented, Perl threading is a somewhat funny beast. I'd just get the locking done by the database, where it belongs.