Re: [sqlite] sqlite3_busy_timeout not avoiding "database is locked" errors with multi-threaded updates

2005-01-09 Thread Paul Dixon
Stephen C. Gilardi wrote:
Of course I could implement this in the application code, but I'd 
rather understand why sqlite isn't able to do this first. The sqlite 
source is unfamiliar territory, but I'll have a poke around and try 
to trace how the busy callback is used.

Based on following the discussions on the mailing list and on my own 
experience, I believe the correct answer is for you to implement the 
retry in your application code.  Sqlite is coded to return SQLITE_BUSY 
in cases where (in the current implementation) not doing so would lead 
to deadlock.  The retry logic internal to sqlite is located at a level 
such that it can't do the same kind of retrying that you can do from 
the application.

Adding retry handling to my application has cleared up the problem, but 
I still feel this belongs inside the library. If I tell SQLite I'm 
prepared to wait up to 10 seconds to get a lock, then the API should be 
doing it's hardest to honour that. I'm new on the list, but I've seen 
similar sentiments expressed in the archives -  would it be such a big 
deal if additional retry handling was added just "underneath" the main API?

Don't get me wrong, I'm not griping here. It can be made to work as 
advertised without patching the source, I'm happy ;-)

I'm still tracing through the source to understand this deadlocking and 
busy handling issue better, but I had some interesting results from a 
test application. A pool of 3 threads attempted row inserts, sleeping 
for 1,2 or 3 seconds between inserts. When there was some contention, 
one of two things happened:

* in the minority of cases, SQLite would call the busy handler, and 
after a 1ms sleep, retried successfully.

* in the majority of cases, SQLite *never* calls the busy handler, 
immediate returning a busy code. My application level handler kicks in, 
but takes 15-30ms before SQLite handles the request. Interestingly, once 
SQLite successfully takes it, it generally calls the busy handler itself 
for another 8ms or so.

Anyway, thanks for all the suggestions and insights so far, very much 


Re: [sqlite] sqlite3_busy_timeout not avoiding "database is locked" errors with multi-threaded updates

2005-01-07 Thread Eli Burke
Paul Dixon wrote:
I'm having some problems with multi-threaded updates failing.
Multiple threads open their own sqlite connection with sqlite3_open()
and then call sqlite3_busy_timeout(handle, 3) to give a 30 second
timeout. These threads then perform some inserts on the same table, but
I get "database is locked" errors without the timeout being honoured.
I'm using sqlite 3.0.8 compiled for thread safety, running on Cygwin.
Any clues gratefully received while I try this on some other platforms...
This is a rather sticky problem that I am still struggling with. You 
will doubtless
get better long-term advice from others on the list, but I can tell you 
that the
best place to start from to get your program up and running is to wrap your
updates inside "BEGIN IMMEDIATE" or "BEGIN EXCLUSIVE" transaction
blocks. (there is an implied "BEGIN DEFERRED" transaction around every
sqlite_exec that does not already have a transaction active) Make sure 
you are
running Sqlite 3.08 or higher for this functionality.

An Immediate transaction locks the entire database so that only a single 
can *write*, while still allowing Deferred transaction threads to read. 
that reader threads may still get DB locked errors (SQLITE_BUSY) if the 
with the Immediate transaction writes to a table and must be tolerant of 
this case.

An Exclusive transaction locks the entire db completely. All other 
threads trying
to start transactions will block, calling the busy handler, until the 
transaction is committed or rolled back. Again, deferred transactions 
may get
SQLITE_BUSY after the Exclusive lock is granted.

These transaction types were introduced in 3.08 to try and resolve some 
of the
problems people had with multi-threaded access. Unfortunately, they do 
not provide
enough granularity to lock single database tables. As a result, you are 
using a database-centric mutex to restrict access to a single write 

Detailed documentation is a bit scattered, but you can read the sections on:
Transaction syntax here:
Locking and Concurrency:
And there are a number of previous threads on the mailing list on this 

I don't know if anyone will read all the way to the end of this or not, 
but can
anyone tell me if there is a way to determine via the API if a 
transaction is
already active or not for the current database handle?