RE: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
If they are different files then you should not have any of these problems. -Original Message- From: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] Sent: 16 August 2007 11:21 AM To: sqlite-users@sqlite.org Subject: Re: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment hi, Am not clear. Suppose i have 2 databases and a process spwans 2 threads and each thread opne the db will it result in any problem? They are independent files. thx ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: Andre du Plessis <[EMAIL PROTECTED]> Date: Thursday, August 16, 2007 4:36 pm Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment > 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 > intobusy 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 > BEGINBEGIN > INSERT SOME INSERT SOME > COMMIT (busy handler)COMMIT (busy handler) > As you can see thread1 waits for thread2, they will deadlock, and > unlessyou 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 > thenfail 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 > > wi
Re: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
hi, Am not clear. Suppose i have 2 databases and a process spwans 2 threads and each thread opne the db will it result in any problem? They are independent files. thx ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: Andre du Plessis <[EMAIL PROTECTED]> Date: Thursday, August 16, 2007 4:36 pm Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment > 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 > intobusy 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 > BEGINBEGIN > INSERT SOME INSERT SOME > COMMIT (busy handler)COMMIT (busy handler) > As you can see thread1 waits for thread2, they will deadlock, and > unlessyou 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 > thenfail 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 committ
