Hello Barry, Essentially, if it might lock ever, it shouldn't be done in the context of the GUI. You may not have run into it yet but, any locking in the GUI is sure to cause deadlocks when things get busy. At least in Microsoft Windows, message handling is all reentrant so, it's very easy to hold a lock, try to talk to a GUI element and deadlock. Microsoft specifically warns against talking to the GUI from a worker thread.
If you hold a lock in the GUI context but, don't try to update anything in the display, then this is a non-issue. For example, with a shared data structure, I might lock, copy then clear the shared struct, then unlock and display updates from the copy (feed a list control for example). This also lets the worker continue adding to the shared data while the GUI works using the local copy. As someone else suggested, I'd use a second worker thread for inserts (or a single worker that reads jobs out of a queue and does both insert and retrievals), then I'd post the status to the GUI, perhaps stalling the workers until the GUI says it's done with the data. In this way the GUI will never stall no matter how hard the worker threads are banging on the database. C Friday, December 16, 2005, 5:05:46 PM, you wrote: BP> Yes, but I think that will just lead to the same problem. Essentially that BP> is what SQLite is doing for me already. BP> What is happening is that the high priority user interface thread is waiting BP> for the low priority worker thread to complete its transaction. This BP> effectively is reducing the priority of the user interface which either BP> times out or becomes sluggish... BP> In the busy handler can you find out what thread has the lock? If so, I BP> could probably temporarily increase the priority of the locking thread and BP> speed up the transaction processing/unlocking... BP> Thanks again, BP> -- BP> BP BP> << www.planet-hood.com >> Welcome to our world << >> -----Original Message----- >> From: John Stanton [mailto:[EMAIL PROTECTED] >> Sent: Friday, December 16, 2005 1:07 PM >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] Threads and locking >> >> Have you thought of using a lock to synchronise access to the >> databaseso that only one thread at a time could change the >> database although both could read simultaneously? >> JS >> >> Barry Paul wrote: >> > >> > 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 << >> > >> > >> >> >> -- Best regards, Teg mailto:[EMAIL PROTECTED]
