Bug #46289 for DBD-SQLite: potential bug with transactions / locking

Thu May 21 16:33:42 2009 skhurst [...] clusterresources.com - Ticket created

Subject:	potential bug with transactions / locking
Date:	Thu, 21 May 2009 14:33:29 -0600 (MDT)
To:	bug-dbd-sqlite [...] rt.cpan.org
From:	"Steve K. Hurst" <skhurst [...] clusterresources.com>

Hi! I think I've encountered a bug in DBD::SQLite that allows a second process to try and access the db when another is in a transaction. It occurs when I have something like: begin write write write ... fetch ... write ... commit the 'fetch' inside the transaction seems to be what provokes the problem. The smallest code I found that recreates the problem (child dies with db locked error): #!/usr/bin/perl -w use strict; my $pid = fork(); if ( $pid ) { # parent write_db(); } elsif ( defined $pid ) { # child sleep 1; write_db(); exit(0); } waitpid($pid, 0); exit(0); sub write_db { require DBI; require DBD::SQLite; my $dbh = DBI->connect("dbi:SQLite:dbname=test.db","",""); $dbh->{RaiseError} = 1; $dbh->do('CREATE TABLE IF NOT EXISTS test (id text,text text)'); $dbh->do('CREATE TABLE IF NOT EXISTS test2 (id2 text,text2 text)'); $dbh->begin_work(); # comment the select, and I no longer break $dbh->do('SELECT * from test2'); $dbh->do("INSERT INTO test VALUES ($$, 'this is a test')"); sleep 2; $dbh->commit(); }

Thu May 21 22:02:41 2009 ishigaki [...] cpan.org - Correspondence added

This looks like a limitation/bug of sqlite itself, not of DBD::SQLite. I confirmed the same lock message after the second insert when I invoked two sqlite3 commands and issued the same statements one by one for both of them. Visit sqlite.org or its mailing list for more information or help. On Thu May 21 16:33:42 2009, skhurst@clusterresources.com wrote: Show quoted text

> Hi! > > I think I've encountered a bug in DBD::SQLite that allows a second > process to try and access the db when another is in a transaction. It > occurs when I have something like: > > begin > write write write ... fetch ... write ... > commit > > the 'fetch' inside the transaction seems to be what provokes the > problem. > > The smallest code I found that recreates the problem (child dies with > db locked error): > > > > #!/usr/bin/perl -w > > use strict; > > my $pid = fork(); > > if ( $pid ) > { > # parent > write_db(); > } > elsif ( defined $pid ) > { > # child > sleep 1; > write_db(); > exit(0); > } > > waitpid($pid, 0); > > exit(0); > > > > > sub write_db > { > > require DBI; > require DBD::SQLite; > > my $dbh = DBI->connect("dbi:SQLite:dbname=test.db","",""); > > $dbh->{RaiseError} = 1; > > $dbh->do('CREATE TABLE IF NOT EXISTS test (id text,text text)'); > $dbh->do('CREATE TABLE IF NOT EXISTS test2 (id2 text,text2 text)'); > > $dbh->begin_work(); > > # comment the select, and I no longer break > $dbh->do('SELECT * from test2'); > $dbh->do("INSERT INTO test VALUES ($$, 'this is a test')"); > > sleep 2; > > $dbh->commit(); > > } >

Thu May 21 22:02:42 2009 The RT System itself - Status changed from 'new' to 'open'

Thu May 21 22:02:44 2009 ishigaki [...] cpan.org - Status changed from 'open' to 'stalled'

Fri May 22 10:45:42 2009 skhurst [...] clusterresources.com - Correspondence added

Subject:	Re: [rt.cpan.org #46289] potential bug with transactions / locking
Date:	Fri, 22 May 2009 08:45:39 -0600 (MDT)
To:	bug-DBD-SQLite [...] rt.cpan.org
From:	"Steve K. Hurst" <skhurst [...] clusterresources.com>

Hi Kenichi, thanks for the quick response. I think the sqlite3 command line tool defualts to a 0 timeout - so if it isn't changed it correctly immediately returns a locked error. If you set the timeout in the second process to 30000 (as DBD::SQLite does when it starts a connection), It correctly waits for 30 seconds before returning the locked error. A small C program that does the same thing as the perl program below also behaves correctly (second process doesn't error - waits up to the timeout value before returning an error). Thanks - Steve Show quoted text

----- Original Message ----- From: "Kenichi Ishigaki via RT" <bug-DBD-SQLite@rt.cpan.org> To: skhurst@clusterresources.com Sent: Thursday, May 21, 2009 8:02:43 PM GMT -07:00 US/Canada Mountain Subject: [rt.cpan.org #46289] potential bug with transactions / locking <URL: https://rt.cpan.org/Ticket/Display.html?id=46289 > This looks like a limitation/bug of sqlite itself, not of DBD::SQLite. I confirmed the same lock message after the second insert when I invoked two sqlite3 commands and issued the same statements one by one for both of them. Visit sqlite.org or its mailing list for more information or help. On Thu May 21 16:33:42 2009, skhurst@clusterresources.com wrote:

> Hi! > > I think I've encountered a bug in DBD::SQLite that allows a second > process to try and access the db when another is in a transaction. It > occurs when I have something like: > > begin > write write write ... fetch ... write ... > commit > > the 'fetch' inside the transaction seems to be what provokes the > problem. > > The smallest code I found that recreates the problem (child dies with > db locked error): > > > > #!/usr/bin/perl -w > > use strict; > > my $pid = fork(); > > if ( $pid ) > { > # parent > write_db(); > } > elsif ( defined $pid ) > { > # child > sleep 1; > write_db(); > exit(0); > } > > waitpid($pid, 0); > > exit(0); > > > > > sub write_db > { > > require DBI; > require DBD::SQLite; > > my $dbh = DBI->connect("dbi:SQLite:dbname=test.db","",""); > > $dbh->{RaiseError} = 1; > > $dbh->do('CREATE TABLE IF NOT EXISTS test (id text,text text)'); > $dbh->do('CREATE TABLE IF NOT EXISTS test2 (id2 text,text2 text)'); > > $dbh->begin_work(); > > # comment the select, and I no longer break > $dbh->do('SELECT * from test2'); > $dbh->do("INSERT INTO test VALUES ($$, 'this is a test')"); > > sleep 2; > > $dbh->commit(); > > } >

Fri May 22 10:45:43 2009 The RT System itself - Status changed from 'stalled' to 'open'

Mon May 25 22:51:46 2009 adamkennedybackup [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #46289] potential bug with transactions / locking
Date:	Tue, 26 May 2009 12:51:27 +1000
To:	bug-DBD-SQLite [...] rt.cpan.org
From:	Adam Kennedy <adamkennedybackup [...] gmail.com>

In fact, as I read the documentation for BEGIN TRANSACTION, this is actually intended. It seems that the default transaction mode allows for two transactions running at the same time to hit each other, and one dies (rather than wait for the previous transaction to complete). Rather than ->begin_work, you might want to experiment with the different transaction mode settings and see if you can replicate the problem with one (but not with the other). Adam K 2009/5/22 Kenichi Ishigaki via RT <bug-DBD-SQLite@rt.cpan.org>: Show quoted text

> Queue: DBD-SQLite > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=46289 > > > This looks like a limitation/bug of sqlite itself, not of DBD::SQLite. > I confirmed the same lock message after the second insert when I > invoked two sqlite3 commands and issued the same statements one by one > for both of them. Visit sqlite.org or its mailing list for more > information or help. > > > On Thu May 21 16:33:42 2009, skhurst@clusterresources.com wrote:

>> Hi! >> >> I think I've encountered a bug in DBD::SQLite that allows a second >> process to try and access the db when another is in a transaction. It >> occurs when I have something like: >> >> begin >> write write write ... fetch ... write ... >> commit >> >> the 'fetch' inside the transaction seems to be what provokes the >> problem. >> >> The smallest code I found that recreates the problem (child dies with >> db locked error): >> >> >> >> #!/usr/bin/perl -w >> >> use strict; >> >> my $pid = fork(); >> >> if ( $pid ) >> { >> # parent >> write_db(); >> } >> elsif ( defined $pid ) >> { >> # child >> sleep 1; >> write_db(); >> exit(0); >> } >> >> waitpid($pid, 0); >> >> exit(0); >> >> >> >> >> sub write_db >> { >> >> require DBI; >> require DBD::SQLite; >> >> my $dbh = DBI->connect("dbi:SQLite:dbname=test.db","",""); >> >> $dbh->{RaiseError} = 1; >> >> $dbh->do('CREATE TABLE IF NOT EXISTS test (id text,text text)'); >> $dbh->do('CREATE TABLE IF NOT EXISTS test2 (id2 text,text2 text)'); >> >> $dbh->begin_work(); >> >> # comment the select, and I no longer break >> $dbh->do('SELECT * from test2'); >> $dbh->do("INSERT INTO test VALUES ($$, 'this is a test')"); >> >> sleep 2; >> >> $dbh->commit(); >> >> } >>

> > > >

Wed Mar 31 02:26:06 2010 ishigaki [...] cpan.org - Correspondence added

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-5-21 Thu 16:33:42, skhurst@clusterresources.com wrote: Show quoted text

> Hi! > > I think I've encountered a bug in DBD::SQLite that allows a second > process to try and access the db when another is in a transaction. It > occurs when I have something like: > > begin > write write write ... fetch ... write ... > commit > > the 'fetch' inside the transaction seems to be what provokes the > problem. > > The smallest code I found that recreates the problem (child dies with > db locked error): > > > > #!/usr/bin/perl -w > > use strict; > > my $pid = fork(); > > if ( $pid ) > { > # parent > write_db(); > } > elsif ( defined $pid ) > { > # child > sleep 1; > write_db(); > exit(0); > } > > waitpid($pid, 0); > > exit(0); > > > > > sub write_db > { > > require DBI; > require DBD::SQLite; > > my $dbh = DBI->connect("dbi:SQLite:dbname=test.db","",""); > > $dbh->{RaiseError} = 1; > > $dbh->do('CREATE TABLE IF NOT EXISTS test (id text,text text)'); > $dbh->do('CREATE TABLE IF NOT EXISTS test2 (id2 text,text2 text)'); > > $dbh->begin_work(); > > # comment the select, and I no longer break > $dbh->do('SELECT * from test2'); > $dbh->do("INSERT INTO test VALUES ($$, 'this is a test')"); > > sleep 2; > > $dbh->commit(); > > } >

Wed Mar 31 02:26:08 2010 ishigaki [...] cpan.org - Status changed from 'open' to 'resolved'