I used to have the same issue. I finally did two things: 1. The background worker thread is at least normal priority. If you own the DB, you need to get in and get out. I put sleeps in to make sure I wasn't hitting the DB too often from this thread
2. All connections to the database happen inside a "begin exclusive" transaction. I'm guaranteed not to hit deadlock this way. With those two in place, I believe you can let a busy handler spin (ie keep trying) forever because it is guaranteed to get in sooner or later. -----Original Message----- From: Barry Paul [mailto:[EMAIL PROTECTED] Sent: Friday, December 16, 2005 2:20 PM To: sqlite-users@sqlite.org Subject: [sqlite] Threads and locking Hi, I am having some unexpected locking issues with SQLite. I have a desktop application that uses SQLite. The application has a low priority worker thread that is constantly analyzing/adding/updating/deleting records in the database. The main application thread mainly reads from the database but also does some updating/deleting. Both threads have their own SQLite connection. My problem is that when I do updates in the main application thread I quite often fail with a return value of SQLITE_BUSY. I have messed around with busy_timeouts and busy_handlers without much success. My current busy handler (culled either from this list or the web) is: int busyHandler(void *pArg1, int iPriorCalls) { // sleep if handler has been called less than threshold value if (iPriorCalls < 20) { // adding a random value here greatly reduces locking if (pArg1 < 0) Sleep((rand() % 500) + 400); else Sleep(500); return 1; } // have sqlite3_exec immediately return SQLITE_BUSY return 0; } If I increase the transaction size on the low priority thread I get more update failures on the main thread. My schema is fairly simple and my tables contain < 90,000 rows. It would seem to me that with just two threads and this busy handler I should never (or very rarely) get SQLITE_BUSY. My theory is that the main application thread is getting locked out because it is waiting for the low priority thread to release the lock on the database. Meanwhile something else is happing on the machine at a higher priority and not letting the low priority thread back in to finish the transaction and release the lock. Does this sound reasonable and is there a good way of dealing with this situation? Should I try to increase the priority of the background thread when I get a lock? Or is there some way to make sure that transactions in the low priority thread are executed all at once without interruption? Thanks for your time, -- BP << www.planet-hood.com >> Welcome to our world << To find out more about Reuters visit www.about.reuters.com Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of Reuters Ltd.
