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, 30000) 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 thread
can *write*, while still allowing Deferred transaction threads to read. Note
that reader threads may still get DB locked errors (SQLITE_BUSY) if the thread
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 Exclusive
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 essentially
using a database-centric mutex to restrict access to a single write resource.
Detailed documentation is a bit scattered, but you can read the sections on:
Transaction syntax here: http://www.sqlite.org/lang.html#transaction
Locking and Concurrency: http://www.sqlite.org/lockingv3.html
And there are a number of previous threads on the mailing list on this topic.
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?
-Eli
