Re: [sqlite] sqlite3_busy_timeout not avoiding "database is locked" errors with multi-threaded updates
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 appreciated! Paul
Re: [sqlite] sqlite3_busy_timeout not avoiding "database is locked" errors with multi-threaded updates
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 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
