Limits of multithreaded INSERTS in sqlite

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 situation开发者_运维知识库s, 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

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.

When writing to a database, SQLite locks the entire database file with fcntl(). When another process/thread tries to write to it, it will wait a set amount of time (30 seconds?) and then give up. See the SQLiteFAQ.

Note that while Perl's threads are indeed buggy and weird, that's not the fundamental issue here. I've come to the opinion that SQLite is an inappropriate choice for all but the simplest needs.

One alternative might be to have each thread insert into its own staging table, then use lock on a variable (a dummy variable is fine) and insert into the final table while the thread has the lock. In other words, something like...

sub ThreadStuff
{
    my $tid=threads->tid;
    #Thread is doing whatever it's doing
    $dbh->do("delete from staging_" . $tid) or die $dbh->errstr;
    $dbh->do("insert into staging_" . $tid . "stuff;") or die $dbh->errstr;

    {
        lock $hall_pass;
        $dbh->do("insert into final_table select * from staging_" . $tid) or die $dbh->errstr;
        #The lock on $hall_pass goes away as soon as we leave this block.
    }
    #Other stuff, maybe cleanup or whatever.
}