sqliteperltransactionsdbi

Perl DBI SQLite script with multiple database handles fails due to locked database if transactions are used

I have an application that uses Perl's DBI module and the SQLite driver. The application processes some images, and stores data about the contents of the images in a database. For some uses, the data in the database can be discarded after more images come in, but in the current instance I want to archive the information to keep the main database from becoming too large. I move the relevant rows from the "pipeline" database to the "archive" database. I find that if I enable transactions for the archive database, the process fails with a "database is locked" error. This seems similar to, but different from this question. As seen below, I am using a single process to access the databases.

Here is a MWE of the problem. I create a simple "pipeline" DB with one table. I create an "archive" DB with the same schema. I move some of the entries from the working "pipeline" DB to the "archive" DB.

use strict;
use warnings;
use DBI;

unlink foreach glob('*.sdb');

#create the table in the pipeline db
my $dbname_pipe = 'pipeline.sdb';
my $dbh_pipe = DBI->connect("dbi:SQLite:dbname=$dbname_pipe","","",{RaiseError=>1,AutoCommit=>1}) or die $DBI::errstr;
my $sth_pipe = $dbh_pipe->prepare('CREATE TABLE files (filename varchar(128) NOT NULL, frame int, PRIMARY KEY(filename))') ;
$sth_pipe->execute();

#add some data to the pipeline table
$sth_pipe = $dbh_pipe->prepare('INSERT INTO files (filename, frame) VALUES (?, ?)');
$sth_pipe->execute(sprintf("file_%04d.png",$_),$_) foreach (1..20);

#create the table in the archive db
my $dbname_arch = 'archive.sdb';
my $dbh_arch = DBI->connect("dbi:SQLite:dbname=$dbname_arch","","",{RaiseError=>1,AutoCommit=>1}) or die $DBI::errstr;
my $sth_arch = $dbh_arch->prepare('CREATE TABLE files (filename varchar(128) NOT NULL, frame int, PRIMARY KEY(filename))') ;
$sth_arch->execute();

#move some entries from the pipeline db to the archive db
$dbh_arch->do(qq{ATTACH DATABASE "$dbname_pipe" AS pipeline});

$dbh_pipe->begin_work;
$dbh_arch->begin_work;
eval {
    $sth_arch = $dbh_arch->prepare('INSERT INTO files(filename, frame) SELECT filename, frame FROM pipeline.files WHERE frame < ?');
    $sth_pipe = $dbh_pipe->prepare('DELETE FROM files WHERE frame < ?');
    $sth_arch->execute(10);
    $sth_pipe->execute(10);
    $dbh_arch->commit;
    $dbh_pipe->commit;
};
if ($@) {
    warn "archiving transaction aborted because of $@";
    eval { $dbh_pipe->rollback };
    eval { $dbh_arch->rollback };
}

$dbh_arch->do(qq{DETACH DATABASE pipeline});
#disconnect
$dbh_arch->disconnect;
$dbh_pipe->disconnect;

#done
1;

The above code fails with DBD::SQLite::st execute failed: database is locked at dbi_lock_mwe.pl line 32. archiving transaction aborted because of DBD::SQLite::st execute failed: database is locked at dbi_lock_mwe.pl line 32.

If I disable transactions for the archive database by commenting out the individual lines:

$dbh_arch->begin_work;
$dbh_arch->commit;
eval { $dbh_arch->rollback };

then the script completes without error, and the tables in the two .sdb files are correct: frames 1-9 are in the archive.sdb file, frames 10-20 remain in the pipeline.sdb file.

How can I prevent the archive database from locking while still using transactions?

Solution

  • There is a setting called sqlite_use_immediate_transaction in DBD::SQlite to change when locks are aqcuired.

    The default transaction behavior of SQLite is deferred, that means, locks are not acquired until the first read or write operation, and thus it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed, and eventually cause a "deadlock". To avoid this, DBD::SQLite internally issues a BEGIN IMMEDIATE if you begin a transaction by calling begin_work or by turning off AutoCommit (since 1.38_01).

    If you set this for both of your database handles, your code works fine. Here is one of the two as an example.

    my $dbh_pipe = DBI->connect(
  "dbi:SQLite:dbname=$dbname_pipe", "", "", {
    RaiseError => 1,
    AutoCommit => 1,
    sqlite_use_immediate_transaction => 0,
}) or die $DBI::errstr;

    I don't understand why this works, but I suspect the locking happens in a weird order without this setting, and then the driver gets confused.

    After running the code like this, you end up with file 1 to 9 in archive, and 10 to 20 in pipeline.