http://qs321.pair.com?node_id=11140891

I needed to lock a SQLite(3) database in order to test whether my code for writing to it fails gracefully, or my code for reading circumvents the lock by, lamely, copying it to a new file.

After some help from Discipulus' google-fu I have ended using this for locking the DB: PRAGMA locking_mode = EXCLUSIVE; BEGIN EXCLUSIVE; . (unfortunately unlocking it with COMMIT; does not work for me and I unlock it with a disconnect).

The test file will use a fork() whose child will open the db and lock it as above and sleep for some time before disconnecting (thus unlocking it). The parent will try to open the DB and hopefully be able to detect if locked or not.

Here's the code:

#!/usr/bin/perl
use lib 'lib';
use strict;
use warnings;

# WARNING: Test::More obviously gets a bit confused with the fork
# there's also Test::Fork
use Test::More;
use DBI;
use DBD::SQLite;

my $dbfile = 'abc.sqlite';

my $pid = fork();
if( not $pid ){
  my $dbh = DBI->connect(
    "dbi:SQLite:dbname=$dbfile",
    '', '',
    {
      RaiseError => 0, # don't die on error
      sqlite_open_flags => DBD::SQLite::OPEN_READWRITE
        | DBD::SQLite::OPEN_CREATE
    }
  );
  ok(defined $dbh, "connect()");
  # this causes the driver to timeout and not to wait
  # for the lock forever
  $dbh->sqlite_busy_timeout(1000); # milliseconds
  my $SQL = 'PRAGMA locking_mode = EXCLUSIVE';
  my $ret = eval { $dbh->do($SQL) };
  ok(defined $ret, "do SQL: $SQL");
  $SQL = 'BEGIN EXCLUSIVE';
  $ret = eval { $dbh->do($SQL) };
  ok(defined $ret, "do SQL: $SQL");
  # now db is locked, we have 15 seconds
  # to test the locked db before unlock
  sleep 15;
  $dbh->disconnect();
  diag("DB is now unlocked.");
  exit;
}

# parent
# give some  time for our child to lock the db
sleep 2; # we now have 13 seconds to finish all tests before db unlock
+s

# check that the db is locked
my $dbh = DBI->connect(
  "dbi:SQLite:dbname=$dbfile",
  '', '',
  {
    RaiseError => 0, # don't die
    sqlite_open_flags => DBD::SQLite::OPEN_READONLY,
    TraceLevel => 1,
  }
);
ok(defined $dbh, "connected to db '$dbfile'.");
# set this to a short timeout (millis) because it can wait forever
# and our child will be dead soon!
$dbh->sqlite_busy_timeout(1000);
my $SQL = 'BEGIN IMMEDIATE';
my $ret = eval { $dbh->do($SQL) };
ok(!defined $ret, "database is locked and SQL must fail: '$SQL'.");
$dbh->disconnect;

wait; # for our child

done_testing();

[download]

Update:

And here is the code with Test::More Test::Fork which may be necessary as Test::More gets confused with the fork.

use strict;
use warnings;

use Test::More tests => 1+3+2;
use Test::Fork;
use DBI;
use DBD::SQLite;

my $dbfile = 'abc.sqlite';

fork_ok(3, sub {
  my $dbh = DBI->connect(
    "dbi:SQLite:dbname=$dbfile",
    '', '',
    {
      RaiseError => 0, # don't die on error
      sqlite_open_flags => DBD::SQLite::OPEN_READWRITE
        | DBD::SQLite::OPEN_CREATE
    }
  );
  ok(defined $dbh, "connect()");
  # this causes the driver to timeout and not to wait
  # for the lock forever
  $dbh->sqlite_busy_timeout(1000); # milliseconds
  my $SQL = 'PRAGMA locking_mode = EXCLUSIVE';
  my $ret = eval { $dbh->do($SQL) };
  ok(defined $ret, "do SQL: $SQL");
  $SQL = 'BEGIN EXCLUSIVE';
  $ret = eval { $dbh->do($SQL) };
  ok(defined $ret, "do SQL: $SQL");
  # now db is locked, we have 15 seconds
  # to test the locked db before unlock
  sleep 15;
  # TODO: how do we unlock the beast?
  # ROLLBACK; or COMMIT; nothing works
  $dbh->disconnect();
  diag("DB is now unlocked.");
});

# parent
# give some  time for our child to lock the db
sleep 2; # we now have 13 seconds to finish all tests before db unlock
+s

# check that the db is locked
my $dbh = DBI->connect(
  "dbi:SQLite:dbname=$dbfile",
  '', '',
  {
    RaiseError => 0, # don't die
    sqlite_open_flags => DBD::SQLite::OPEN_READONLY,
    TraceLevel => 1,
  }
);
ok(defined $dbh, "connected to db '$dbfile'.");
# set this to a short timeout (millis) because it can wait forever
# and our child will be dead soon!
$dbh->sqlite_busy_timeout(1000);
my $SQL = 'BEGIN IMMEDIATE';
my $ret = eval { $dbh->do($SQL) };
ok(!defined $ret, "database is locked and SQL must fail: '$SQL'.");
$dbh->disconnect;

#wait; # for our child, this is handled by fork_ok

#done_testing(); not needed

[download]

Another update without the fork. Thinking again about it, there is no need for a fork, see Re^2: Locking a SQLite DB for tests. Sohere is one without the fork:

use strict;
use warnings;

use Test::More;
use DBI;
use DBD::SQLite;

my $dbfile = 'abc.sqlite';

# we are opening the same database and at some point
# we have the same DB opened with 2 different handles
# First time here to lock it
  my $dbh_locked = DBI->connect(
    "dbi:SQLite:dbname=$dbfile",
    '', '',
    {
      RaiseError => 0, # don't die on error
      sqlite_open_flags => DBD::SQLite::OPEN_READWRITE
        | DBD::SQLite::OPEN_CREATE
    }
  );
  ok(defined $dbh_locked, "connect()");
  # this causes the driver to timeout and not to wait
  # for the lock forever
  $dbh_locked->sqlite_busy_timeout(1000); # milliseconds
  my $SQL = 'PRAGMA locking_mode = EXCLUSIVE';
  my $ret = eval { $dbh_locked->do($SQL) };
  ok(defined $ret, "do SQL: $SQL");
  $SQL = 'BEGIN EXCLUSIVE';
  $ret = eval { $dbh_locked->do($SQL) };
  ok(defined $ret, "do SQL: $SQL");
  # now db is locked, we have 15 seconds
  # to test the locked db before unlock
  diag("DB is now locked.");

# Second time, we open the DB here to check if it's locked
# check that the db is locked
my $dbh = DBI->connect(
  "dbi:SQLite:dbname=$dbfile",
  '', '',
  {
    RaiseError => 0, # don't die
    sqlite_open_flags => DBD::SQLite::OPEN_READONLY,
    TraceLevel => 1,
  }
);
ok(defined $dbh, "connected to db '$dbfile'.");
# set this to a short timeout (millis) because it can wait forever
# and our child will be dead soon!
$dbh->sqlite_busy_timeout(1000);
$SQL = 'BEGIN IMMEDIATE';
$ret = eval { $dbh->do($SQL) };
ok(!defined $ret, "database is locked and SQL must fail: '$SQL'.");
$dbh->disconnect;

$dbh_locked->disconnect;

done_testing();

[download]

Suggestions and improvements welcome. Also any advice on what Test module to use for code involving forks. There is Test::Fork which has many warnings and failed tests. See Update and also Re^2: Locking a SQLite DB for tests below by 1nickt..

bw, bliako

Replies are listed 'Best First'.
Re: Locking a SQLite DB for tests
by 1nickt (Canon) on Jan 27, 2022 at 13:00 UTC

    Hi bliako, for what it's worth we just introduced Test::Fork in one of our tests and it is working great.

    Hope this helps!


    The way forward always starts with a minimal test.
[reply]

      thanks 1nickt, my using it was also problem-free.

[reply]
[reply]
Re: Locking a SQLite DB for tests
by Anonymous Monk on Jan 28, 2022 at 07:41 UTC
    The test file will use a fork() whose child will open the db and lock it as above and sleep for some time before disconnecting (thus unlocking it). The parent will try to open the DB and hopefully be able to detect if locked or not.
    You're probably aware of that, but it bears repeating for other people that may find this thread that open SQLite connections don't handle fork() well. Kudos for getting it right and only opening the database after the fork()!
[reply]
[d/l]
[select]

      yes, thanks. fork() duplicating all handles, will create a mess and on top of that, it will try to close all duplicated handles on exit of child (with commits or rollbacks etc) even if child has not opened any itself! This last will be a hard nut to debug.

      The only thing it remains foggy, to me, is how testing should go with fork. To start with, test numbers are mixed and Test::More is randomly confused and fails. Using Test::Fork fixes that but with loosing test numbers which takes me to the real problem: I have to ensure a sequence. First the child opens the DB and locks it, *next* the parent opens the same DB and checks if it's locked and does whatever it has to do on it. That's why I have a sleep() just as the parent starts, to give time to child to lock the DB. But that's not guaranteed at all, although chances are miniscule.

      I believe/read that single SQLite DB is ok being accessed concurrently by many processes.

      Update: in a sort of rubber-ducking-way, I guess I don't need a fork at all!

      Just open the DB first and lock it, don't close it. Next (and sequentially), open the same DB and check if it's locked etc. At the end close both connections to the same DB. BTW my test case is to be able to just read the DB when it is locked, nothing fancy more than read. The real situation is Firefox locking aggressively its cookies SQLite db and I can not read the current cookies when FF is running.

[reply]
[d/l]
[select]

Back to Meditations