Ok well I guess I forgot to mention this is what has made me want to pull my hair out a few times :) the fact that you have to worry about both scenarios for two different reasons, if multiple threads are working with the same connection handle, then SQL will have a better understanding of the state of your connection and inform you of busy errors better. If you are using different DB handles what will happen is that SQLite may not care that some other thread is busy with another handle and all will work fine until one point, the connection handle needs to commit data and enter exclusive mode, it has to get an exclusive lock on the DB File and no matter that other connections have their own handles if they have any locks on the db, sqlite will go into busy handler mode and eventually timeout, depending on how long you wait. If a query keeps a read cursor open for some reason inevitably this will result in a database is locked error. The problem to watch out for is a deadlock, example
THREAD1 THREAD2 BEGIN BEGIN INSERT SOME INSERT SOME COMMIT (busy handler) COMMIT (busy handler) As you can see thread1 waits for thread2, they will deadlock, and unless you have a limit in your busy handler you will wait forever. As recommended, BEGIN IMMEDIATE should prevent thread2 from even starting a transaction if thread1 did so first, however I think this will only work correctly if the same connection handle is used in both, else they still may not know about eachother. So yes there is two ways to do this, one is that make sure your busy handler works properly and then let your applications just try and then fail on busy throw the exception and let the application try again until all locks are gone, Or two use a global mutex (IF your application runs in more than one process space) Or 3 (use a global critical section - this will be faster) if your application is just in one process space. Make sure that inserts/queries finish their business including begin and commit transaction in the critical If your application ONLY does queries for example you should have NO problem, Additionally if you are using the same DB handle across threads EVERY CALL to the library no matter what should be (serialized) locked in a critical section. Ive used these principles that is running fine now, so I will stick to this design Hope this helps -----Original Message----- From: Mark Brown [mailto:[EMAIL PROTECTED] Sent: 15 August 2007 04:34 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment Hi Andre- After rereading your post, I wanted to confirm something. In your example below, are thread1 and thread2 connected to the same database, or different databases? In my scenario, the threads are connected to different databases, so I'm not sure if it is the same situation. Thanks, Mark > -----Original Message----- > From: Andre du Plessis [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 15, 2007 5:05 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment > > > Being a newbie to SQLite I've had the same problems working > with SQLite > so maybe I can help, > It does not matter how well your database is synchronized, a common > pitfall I had was that I would have a query object with an open cursor > which prevents any other statement from committing to the database. > > So for example: > THREAD1 THREAD2 > LOCK > QUERY > UNLOCK LOCK > (Step through query) BEGIN TRANSACTION > INSERTS > COMMIT <- SQLite busy error here > UNLOCK > ------------------------------------------------------------------------ ----- To unsubscribe, send email to [EMAIL PROTECTED] ------------------------------------------------------------------------ ----- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
