Frequent "Database is locked" errors

[avuserow]

I'm writing an application using DB::SQLite to access a database from a Cro HTTP service and another standalone daemon. I'm hitting a surprising amount of "Database is locked" errors with non-obvious causes.

I've managed to replicate this with this test program (run concurrently):

use DB::SQLite;

sub MAIN() {
    my $path = 'tmp.sqlite';
    my $exists = $path.IO ~~ :e;
    my $db = DB::SQLite.new(filename => $path);

    unless $exists {
        say "setting up test data";
        $db.query('CREATE TABLE t(foo, bar)');
        $db.query('INSERT INTO t(foo, bar) values(?, ?)', 1, 2);
    }

    # DB gets locked here
    say ">> running a query that will lock the database";
    my $value = $db.query('SELECT * FROM t WHERE foo = ?', 1).hash;

    # demonstrate the lock with any other write operation
    say ">> running another write query";
    $db.query('INSERT INTO t(foo, bar) values(?, ?)', 2, 3);

    say ">> waiting...";
    sleep 100;
}

This reliably causes a "database is locked" error if I run the script in one terminal, and then run a second copy of the script while the first is waiting.

I'm not sure if this is a usage error on my side, but it seems surprisingly easy to get the database stuck like this.

The Perl 5 implementation of my application runs reliably with DBD::SQLite - though the usage pattern there is very standardized (almost always prepare, execute, return fetchall_arrayref({}), though no explicit finish calls).

I'm planning to move this application to Postgres for other reasons (primarily notify support), but I wanted to provide some feedback. Thanks.

[avuserow]

I should also note that I'm running these versions:

DB::SQLite:ver<0.7>:auth<github:CurtTilmes>:api<1>
DB:ver<0.5>:auth<github:CurtTilmes>:api<1>

raku installed via rakudobrew:

$ raku --version
Welcome to Rakudo(tm) v2021.05.
Implementing the Raku(tm) programming language v6.d.
Built on MoarVM version 2021.05.

Let me know if there's any other information that would help here.

[demanuel]

Sqlite doesn't support out of the box concurrency.

Do this:

    my $db = DB::SQLite.new(filename => $path);
   $db.execute('PRAGMA journal_mode=WAL;');

More info here: https://www.sqlite.org/threadsafe.html https://www.sqlite.org/wal.html

Sqlite is good for 1 writer with multiple readers, otherwise you'll have some headaches.

[avuserow]

Despite SQLite's limitations on concurrency, this works fine in Perl 5's DBI and Raku's DBIish. I don't think wal should be needed to do a single read from one process, wait many seconds, and then do a single write from a second process while the first is still running.

If you separate this into multiple scripts (a writer that does a single insert and sleeps, and a reader that does a single select), the reader is blocked by the writer, even tens or hundreds of seconds later. This scenario is one writer and one reader, just different processes, and I'd expect that to work fine.

Given that this does not manifest in other drivers, I still think DB::SQLite is hanging on to something extra that is causing extra locks.

I've attached a modified test case that hopefully makes this more obvious. Run test1.raku init. Now run test1.raku read, then in a separate terminal run test1.raku write. Feel free to wait a few minutes between the read and write.

test1.raku.txt

[demanuel]

I was able to reproduce it with the original test case. However, i also tested your example with DBIish and the issue doesn't happen.

I just gave a workaround, but after checking the DBIish code i believe it's a bad workaround. On dbiish it looks like it invokes the finish after each execute:

https://github.com/raku-community-modules/DBIish/blob/ca767febd63d1a3d39ae9df4c43597298206f008/lib/DBDish/SQLite/StatementHandle.pm6#L50

https://github.com/raku-community-modules/DBIish/blob/ca767febd63d1a3d39ae9df4c43597298206f008/lib/DBDish/StatementHandle.pm6#L47

In DB::Sqlite it looks that doesn't happen automatically (probably a bug here). So if you put a $db.finish; after the invocation to the .hash function the problem doesn't happen anymore.

my 2cents :-)