Skip Menu |

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

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

People
Owner: Nobody in particular
Requestors: chris [...] ex-parrot.com
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 1.08
Fixed in: (no value)



Subject: "database is locked" error in DBD::SQLite
Consider the following program: #!/usr/bin/perl -w # # create test.sqlite with: # echo 'create table t (c integer);' | sqlite3 test.sqlite # use strict; use DBI; use DBD::SQLite; my $d1 = DBI->connect( 'dbi:SQLite:dbname=test.sqlite', '', '', { AutoCommit => 0, RaiseError => 1 } ); $d1->func(30000, 'busy_timeout'); $d1->do('insert into t (c) values (0)'); sleep(15); $d1->commit(); $d1->disconnect(); If two copies of this program are run simultaneously, for instance with, ./test & sleep 1 ; ./test then the second fails with the error "DBD::SQLite::db do failed: database is locked(5) at dbdimp.c line 389 at ./test line 15." dbdimp.c line 389 is a call to sqlite3_step, which is presumably returning SQLITE_BUSY (code 5). I don't understand why this should happen, given that the busy_timeout function has been called and therefore SQLite should hold off rather than returning immediately with an error code. In any case it's possible to fix this behaviour by replacing calls to sqlite3_step in dbdimp.c with calls to do_sqlite3_step, defined as follows: static int do_sqlite3_step(sqlite3_stmt *S) { int i; while (SQLITE_BUSY = (i = sqlite3_step(S))) sleep(1); return i; } -- though the fact that this is necessary suggests that there's a bug in SQLite itself.
code should be /* ... */ while (SQLITE_BUSY == (i = sqlite3_step(S))) /* ... */ obviously.
From: ArchFool <hackswell [...] gmail.com>
[guest - Sun Feb 27 20:20:03 2005]: Show quoted text
> Consider the following program:
<<SNIP>> Show quoted text
> If two copies of this program are run simultaneously, for instance > with, > > ./test & sleep 1 ; ./test > > then the second fails with the error "DBD::SQLite::db do failed: > database is locked(5) at dbdimp.c line 389 at ./test line 15." > > dbdimp.c line 389 is a call to sqlite3_step, which is presumably > returning SQLITE_BUSY (code 5). I don't understand why this should > happen, given that the busy_timeout function has been called and > therefore SQLite should hold off rather than returning immediately > with an error code.
I tried hacking a while loop around SQLITE_BUSY, but the other process also seems to stick at this point, until the blocked process gives up. Maybe SQLITE is deadlocking? Show quoted text
> In any case it's possible to fix this behaviour by replacing calls to > sqlite3_step in dbdimp.c with calls to do_sqlite3_step, defined as > follows: > > static int do_sqlite3_step(sqlite3_stmt *S) { > int i; > while (SQLITE_BUSY == (i = sqlite3_step(S))) > sleep(1); > return i; > } > > -- though the fact that this is necessary suggests that there's a bug > in SQLite itself.
This is only a problem with AutoCommit set to off, or $dbh->begin_work; and $dbh->commit pairings. I was also able to hack around this problem by changing the "BEGIN TRANSACTION" to "BEGIN IMMEDIATE TRANSACTION". This grabs a DB lock right away. But I'm sure it's much slower. :( When I ran 4 concurrent writes to SQLite, I ALWAYS got 1-2 failures. With the simple BEGIN IMMEDIATE TRANSACTION, I stopped at 16 concurrent processes, because NONE were failing any more. Is there a way to toggle between BEGIN and BEGIN IMMEDIATE? Would it be useful? -Richard Balint (ARCHFOOL)
[guest - Tue Aug 9 18:01:30 2005]: when heaving insert + select BEGIN IMMEDIATE TRANSACTION can't solve the problem Show quoted text
> [guest - Sun Feb 27 20:20:03 2005]: >
> > Consider the following program:
> > <<SNIP>> >
> > If two copies of this program are run simultaneously, for instance > > with, > > > > ./test & sleep 1 ; ./test > > > > then the second fails with the error "DBD::SQLite::db do failed: > > database is locked(5) at dbdimp.c line 389 at ./test line 15." > > > > dbdimp.c line 389 is a call to sqlite3_step, which is presumably > > returning SQLITE_BUSY (code 5). I don't understand why this should > > happen, given that the busy_timeout function has been called and > > therefore SQLite should hold off rather than returning immediately > > with an error code.
> > I tried hacking a while loop around SQLITE_BUSY, but the other process > also seems to stick at this point, until the blocked process gives up. > Maybe SQLITE is deadlocking? >
> > In any case it's possible to fix this behaviour by replacing calls to > > sqlite3_step in dbdimp.c with calls to do_sqlite3_step, defined as > > follows: > > > > static int do_sqlite3_step(sqlite3_stmt *S) { > > int i; > > while (SQLITE_BUSY == (i = sqlite3_step(S))) > > sleep(1); > > return i; > > } > > > > -- though the fact that this is necessary suggests that there's a bug > > in SQLite itself.
> > This is only a problem with AutoCommit set to off, or $dbh->begin_work; > and $dbh->commit pairings. > > > I was also able to hack around this problem by changing the "BEGIN > TRANSACTION" to "BEGIN IMMEDIATE TRANSACTION". This grabs a DB lock > right away. But I'm sure it's much slower. :( > > When I ran 4 concurrent writes to SQLite, I ALWAYS got 1-2 failures. > With the simple BEGIN IMMEDIATE TRANSACTION, I stopped at 16 concurrent > processes, because NONE were failing any more. > > Is there a way to toggle between BEGIN and BEGIN IMMEDIATE? Would it be > useful? > > -Richard Balint > (ARCHFOOL)
On Tue Aug 09 18:01:30 2005, guest wrote: Show quoted text
> [guest - Sun Feb 27 20:20:03 2005]: >
> > Consider the following program:
> > <<SNIP>> >
> > If two copies of this program are run simultaneously, for instance > > with, > > > > ./test & sleep 1 ; ./test > > > > then the second fails with the error "DBD::SQLite::db do failed: > > database is locked(5) at dbdimp.c line 389 at ./test line 15." > > > > dbdimp.c line 389 is a call to sqlite3_step, which is presumably > > returning SQLITE_BUSY (code 5). I don't understand why this should > > happen, given that the busy_timeout function has been called and > > therefore SQLite should hold off rather than returning immediately > > with an error code.
> > I tried hacking a while loop around SQLITE_BUSY, but the other process > also seems to stick at this point, until the blocked process gives up. > Maybe SQLITE is deadlocking? >
> > In any case it's possible to fix this behaviour by replacing calls to > > sqlite3_step in dbdimp.c with calls to do_sqlite3_step, defined as > > follows: > > > > static int do_sqlite3_step(sqlite3_stmt *S) { > > int i; > > while (SQLITE_BUSY == (i = sqlite3_step(S))) > > sleep(1); > > return i; > > } > > > > -- though the fact that this is necessary suggests that there's a bug > > in SQLite itself.
> > This is only a problem with AutoCommit set to off, or $dbh->begin_work; > and $dbh->commit pairings. > > > I was also able to hack around this problem by changing the "BEGIN > TRANSACTION" to "BEGIN IMMEDIATE TRANSACTION". This grabs a DB lock > right away. But I'm sure it's much slower. :( > > When I ran 4 concurrent writes to SQLite, I ALWAYS got 1-2 failures. > With the simple BEGIN IMMEDIATE TRANSACTION, I stopped at 16 concurrent > processes, because NONE were failing any more. > > Is there a way to toggle between BEGIN and BEGIN IMMEDIATE? Would it be > useful? > > -Richard Balint > (ARCHFOOL)
Subject: car insurance<a href='http://www.insurance-top.com'
From: http://www.insurance-top.com
<a href='http://www.yahoo.com'></a>Thanks! http://www.insurance-top.com/auto/ <a href='http://www.insurance-top.com'>auto insurance</a>. <a href="http://www.insurance-top.com ">Insurance car</a>: car site insurance, The autos insurance company, compare car insurance. Also [url]http://www.insurance-top.com/car/[/url] and [link=http://www.insurance-top.com]insurance quote[/link] from site .
Subject: insurance auto<a href='http://www.insurance-top.com'
From: http://www.insurance-top.com
Thanks!!! http://www.insurance-top.com/company/ auto site insurance. [URL=http://www.insurance-top.com]home insurance[/URL]: car site insurance, The autos insurance company, compare car insurance. Also [url=http://www.insurance-top.com]cars insurance[/url] from website .
Subject: insurance auto<a href='http://www.insurance-top.com'
From: http://www.insurance-top.com
Hi! http://www.insurance-top.com/company/ auto site insurance. car site insurance, The autos insurance company, compare car insurance. from website .
From: David Muir Sharnoff <rtper11680 [...] trust.idiom.com>
I'm also having trouble with this locking problem. As far as I can tell, DBD::SQLite does not provide any way to call sqlite3_reset(). Please consider adding a way! Thanks, -Dave I found a post to the sqlite-users mailing list that may be apropos: http://www.mail-archive.com/sqlite-users@sqlite.org/msg09641.html Re: [sqlite] blocking - busy_timeout vs database is locked(5) Robert Simpson Thu, 18 Aug 2005 09:34:03 -0700 Show quoted text
----- Original Message ----- From: "Jonathan H N Chin" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Thursday, August 18, 2005 8:00 AM Subject: [sqlite] blocking - busy_timeout vs database is locked(5) [snip] but I still occasionally get failures: DBD::SQLite::db do failed: database is locked(5) at dbdimp.c line 403 Am I doing something wrong? What is the correct way to make accesses block/retry when the database is busy? I'm afraid you'll have to write your own internal retry mechanism. The busy_timeout only works in certain areas when the database is busy. When an update is in progress however, all attempts to read will return *immediately* with a failure message. You'll then have to call sqlite3_reset() to find out what that error message is. If its a SQLITE_SCHEMA you need to call sqlite3_prepare() again (don't forget to rebind your parameters if any), and if its a SQLITE_LOCKED then you need to sleep for some random amount of time and retry -- with hopefully an eventual timeout mechanism in place. Robert
1.13 will provide $sth->func('reset') though I'm not sure it'll be enough. It'll be interesting to find out. Closing the bug as resolved for now. Re-open if there are still issues once you've tried to get reset() working.