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

[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 
appreciated!

Paul