Skip Menu |
 

This queue is for tickets about the DBD-SQLite CPAN distribution.

Report information
The Basics
Id: 42205
Status: resolved
Priority: 0/
Queue: DBD-SQLite

People
Owner: Nobody in particular
Requestors: perl-cpan [...] bereft.net
Cc: TJC [...] cpan.org
AdminCc:

Bug Information
Severity: Normal
Broken in: 1.14
Fixed in: (no value)



Subject: Long running: database is locked(5) at dbdimp.c line 218
Download (untitled) / with headers
text/plain 1.2k
I have an ugly and not very well isolated test case that reliably fails with a "database is locked" on some platforms with DBD::SQLite 1.14 and it's default sqlite version. Failures have been encountered on Debian Etch, Windows XP. (Ubuntu 8.10, with perl: 5.10.0 64bit succeeded) There's a writer process, adding rows to a database, intermixing transactions and autocommit (seemingly required?) and a read-only process doing selects. It uses fork() to launch the reader (pseudo-processes on windows xp). The writer process seems to be the one failing, as the reader's prints continue with old values. The failure occurs at slightly different points in processing, around 280000 count on windows, 204000 count on etch. The numbers vary, but the windows output looks like: 276000 DBD::SQLite::db commit failed: database is locked(5) at dbdimp.c line 218 at db_ locked.pl line 36. DBD::SQLite::db commit failed: database is locked(5) at dbdimp.c line 218 at db_ locked.pl line 36. 99 277000 99 277000 99 The .db file produced by the test grows to around 500 Mb on windows before failing. (Runs without errors grow even larger) WinXP perl5.8.8, both ActiveState and Strawberry. I'll try to clarify the problem later if I can, Brad
Subject: db_locked.pl
Download db_locked.pl
text/x-perl 1.2k
#!/usr/bin/perl use strict; use warnings; use DBI; use DBD::SQLite; my $db_file = "test-$$.db"; #print "DBI vers: $DBI::VERSION\n"; print "DBD::SQLite vers: $DBD::SQLite::VERSION\n"; if (my $child = fork) { print "Parent $$\n"; print "Child $child\n"; writer($db_file); print "Killing child $child\n"; kill 9, $child; } else { sleep(2); reader($db_file); } sub writer { my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file",'','', { RaiseError => 1 }); $dbh->do('create table t1 (a)'); $dbh->do('create table t2 (b)'); for (1..1000) { $dbh->begin_work(); for (1..1000) { # s/rand() x 100/1/ ran to completion $dbh->do('insert into t1 (a) values (?)', {}, rand() x 100); } sleep(1); $dbh->commit(); $dbh->do('insert into t2 (b) values (?)', {}, $_); } } sub reader { my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file",'','', { RaiseError => 1 }); while (1) { print $dbh->selectrow_array('select count(*) from t1'), "\n"; print $dbh->selectrow_array('select max(b) from t2'), "\n"; sleep(1); } } END { #unlink $db_file; }
Download (untitled) / with headers
text/plain 669b
Below are my experiences of various combinations of perl, 32/64bit, operating system, and sqlite compiled with the internal version vs an external sqlite.. Note all were tested with a vanilla DBD-SQLite, not the vendor-supplied one. Perl 5.10.0 | Ubuntu 8.10 64bit | DBD::SQLite 1.14 = Good. Perl 5.10.0 | Ubuntu 8.10 64bit | DBD::SQLite 1.14 compiled against SQLite 3.6.6.3 = Good. Perl 5.8.8 | Debian Etch 32bit | DBD::SQLite 1.14 = bad. Perl 5.8.8 | Debian Etch 32bit | DBD::SQLite 1.14 w/SQLite 3.6.6.3 = good. Strawberry Perl 5.8.8 | Win32 XP 32bit | DBD::SQLite 1.14 = Bad. Strawberry Perl 5.8.8 | Win32 XP 32bit | DBD::SQLite 1.14 w/SQLite 3.6.6.3 = bad.
Download (untitled) / with headers
text/plain 181b
Further tests, both with: Strawberry Perl 5.10.0 | Win32 XP 32bit | SQLite 1.14 With both standard sqlite and with a version compiled against 3.6.6.3, it failed Brad's test script.
Download (untitled) / with headers
text/plain 929b
I attach a modified version of your db_locked.pl script. In this version, the transactions are wrapped in an eval {} block, and so a failure is reported, but is not fatal. Note that I attempt to rollback the DB handle in the exception handler too. This script demonstrates a more serious problem! Initially it will fail, then keep going for a while, then fail again. The failures occur more regularly as time goes by and DB grows. However after a bit, instead of recovering, the following error occurs: DBD::SQLite::Db begin_work failed: Already in a transaction at db_locked.pl line 33. So, it seems that the transaction cannot be rolled back, but can also not be started again- thus halting any future work. For the record, I've noticed that if I load up the test machines with heavier I/O from other processes, then the db_locked script fails earlier than without, which implies some kind of I/O race condition I think?
Download db_locked.pl
text/x-perl 1.4k
#!/usr/bin/perl use strict; use warnings; use DBI; use DBD::SQLite; my $db_file = "test-locked.db"; #print "DBI vers: $DBI::VERSION\n"; print "DBD::SQLite vers: $DBD::SQLite::VERSION\n"; if (my $child = fork) { print "Parent $$\n"; print "Child $child\n"; writer($db_file); print "Killing child $child\n"; kill 9, $child; } else { sleep(2); reader($db_file); } sub writer { my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file",'','', { RaiseError => 1 }); $dbh->do('create table t1 (a)'); $dbh->do('create table t2 (b)'); for (1..1000) { eval { $dbh->begin_work; for (1..1000) { # s/rand() x 100/1/ ran to completion $dbh->do('insert into t1 (a) values (?)', {}, rand() x 100); } sleep(1); $dbh->commit; $dbh->do('insert into t2 (b) values (?)', {}, $_); }; if ($@) { print "Transaction failed (writer): $@\n"; $dbh->rollback; sleep 1; } } } sub reader { my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file",'','', { RaiseError => 1 }); while (1) { eval { print $dbh->selectrow_array('select count(*) from t1'), "\n"; print $dbh->selectrow_array('select max(b) from t2'), "\n"; }; if ($@) { print "Transaction failed (reader): $@\n"; } sleep(1); } } END { # unlink $db_file; }
Download (untitled) / with headers
text/plain 514b
Hmm. Some more thoughts on this. DBD::SQLite sets the busy timeout period to 30 seconds by default. I would say that the db locked problem we're seeing is because, after the DB is big enough and/or the general disk i/o is high enough, that one of the processes needs more than 30 seconds to complete its transaction. However there is definitely a bigger problem, as seen by the case above where the process continues to retry, and gets into a state where it is impossible to rollback OR start a new transaction.
Download (untitled) / with headers
text/plain 918b
Seems that working with transactions and AutoCommit is tricky. Here's some code that works with debian/lenny. The commented items produce an error: #!/usr/bin/perl -w use DBI; my $dbh = DBI->connect("dbi:SQLite:dbname=test.sqlite",'','',{AutoCommit=>0}) or die $DBI::errstr; #$dbh->commit; $dbh->do('COMMIT'); #!!! Required as AutoCommit=>0 leaves a flapping transaction #$dbh->begin_work; print 'Version: '.$dbh->{sqlite_version}."\n"; # Version: 3.5.9 $dbh->func(10000,'busy_timeout'); $dbh->do('BEGIN EXCLUSIVE TRANSACTION'); $dbh->do("delete from test"); $dbh->do("insert into test values (3)"); $dbh->commit; #$dbh->do('COMMIT'); $dbh->disconnect; I think that SQLite's auto-commit uses an implied "BEGIN TRANSACTION" or something similar. If I don't use the line marked !!!, varoius errors ensue, including "database is locked" and "cannot start a transaction within a transaction". Hope this helps?
From: reply-2009 [...] mgn.org.uk
Download (untitled) / with headers
text/plain 446b
Further reading reveals that SQLite3 has _NO_ mechanism to explicitly change AutoCommit. It is on at the start of a connection. The only way to disable it is to issue a "BEGIN..." command. It is then re-enabled by the matching "COMMIT" or "ROLLBACK". I'd recommend avoiding $dbh->begin_work, $dbh->commit etc. Instead use: $dbh->do('BEGIN TRANSACTION'); and $dbh->do('COMMIT'). This, I believe will avoid the strange errors people see. Cheers.
Download (untitled) / with headers
text/plain 1.7k
Hi. A new "sqlite_use_immediate_transaction" dbh attribute introduced in DBD::SQLite 1.30_02 would fix your issue. This deadlock issue is explained in "The Definitive Guide to SQLite" (Apress), and http:// www.sqlite.org/lockingv3.html to some extent. If you still have the same issue (even with the above attribute or issuing "BEGIN IMMEDIATE" (or "BEGIN EXCLUSIVE") transaction), please reopen this ticket with a failing test/script. Thanks. On 2009-1-07 Wed 03:05:46, BOWMANBS wrote: Show quoted text
> I have an ugly and not very well isolated test case that reliably > fails with a "database is locked" on some platforms with > DBD::SQLite 1.14 and it's default sqlite version. > > Failures have been encountered on Debian Etch, Windows XP. > (Ubuntu 8.10, with perl: 5.10.0 64bit succeeded) > > There's a writer process, adding rows to a database, intermixing > transactions and autocommit (seemingly required?) and a read-only > process doing selects. It uses fork() to launch the reader > (pseudo-processes on windows xp). > > The writer process seems to be the one failing, as the reader's > prints continue with old values. > > The failure occurs at slightly different points in processing, > around 280000 count on windows, 204000 count on etch. > > The numbers vary, but the windows output looks like: > > 276000 > DBD::SQLite::db commit failed: database is locked(5) at dbdimp.c line > 218 at db_ > locked.pl line 36. > DBD::SQLite::db commit failed: database is locked(5) at dbdimp.c line > 218 at db_ > locked.pl line 36. > 99 > 277000 > 99 > 277000 > 99 > > The .db file produced by the test grows to around 500 Mb > on windows before failing. (Runs without errors grow even larger) > > WinXP perl5.8.8, both ActiveState and Strawberry. > > I'll try to clarify the problem later if I can, > > Brad


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.