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!


Reply via email to