Re: [sqlite] SQLITE_BUSY error in multi-threaded environment
I came across some OS's over the years which implemented file locks as a single global lock. Yours may do that. Mark Brown wrote: Hi John- There is a .lock file for each database. From my understanding, that should prohibit 2 connections from using the same database at the same time. However, that is not the situation I am wondering about. I am specifically wondering if database activity on a connection to DB 1 would have any effect on database activity on a different connection to DB2. Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
"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." Simply not true... If you have different connection handles to the same db.. Then it would be wise to use BEGIN EXCLUSIVE. The reason is that sqlite will acquire an EXCLUSIVE lock, in the file when you use begin EXLCUSIVE. Or it will return a sqlite error sqlite_busy, simply retry... BEGIN IMMEDIATE will acquire a reserved lock. Other uses may still be reading and this lock type must escalate to a PENDING and then to an EXCLUSIVE. While the reserved lock is enabled other users (threads) may perform reads. But they may not perform begin immediate/exlusive etc... Once an exclusive lock is acquired no other user (thread) may access the DB.. Example: THREAD1 THREAD2 sqlite3_prepare sqlite3_step (Step through query) BEGIN EXCLUSIVE -- Loop here on sqlite BUSY. INSERTS -- You should not get any qlite busy here! COMMIT-- Nor should you get sqlite busy here!!! The problem you were facing is that whith a begin immediate sqlite acquires a "RESERVED" lock. This is an intent to write lock. You could still actually get sqlite busy errors during your inserts or commit operations. But the thread wrting thread should eventually be able to acquire the lock and continue as long as the reading threads close off their locks before the writer times out. HTH. Ken
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
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 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 BEGINBEGIN 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] -
RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
If you have only one thread accessing the file. Then you shouldn't need to do any type of locking per se. I would leave the file locks. I would not induce your own mutex. Sqlites locking should be adequate. I have a system where there are two threads sharing a single db and each thread createing and release access to various DB's all without using any mutexes for sqlite synchronization. Is this a single database file with each thread having its own connection? If that is the case then sure you should expect and handle the sqlite_busy. Did you configure/compile with:configure --enable_threadsafe ? Mark Brown <[EMAIL PROTECTED]> wrote: No, not a soft link. :) Based on other posts I have read about threading performance and SQLite, it seems like most people like to use a single thread. I'm going to change our application to use a system-wide mutex for thread synchronization and see if that improves our results. I'm still thinking our problems may be low-level file i/o bugs with our OS, so perhaps taking out the file-based .lock scheme will help. Thanks, Mark > -Original Message- > From: Ken [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 15, 2007 12:39 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment > > > It should not. > > As long as those two connections are not used across threads > and point to truely different databases. > > They wouldn't be a soft link would they? I > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
No, not a soft link. :) Based on other posts I have read about threading performance and SQLite, it seems like most people like to use a single thread. I'm going to change our application to use a system-wide mutex for thread synchronization and see if that improves our results. I'm still thinking our problems may be low-level file i/o bugs with our OS, so perhaps taking out the file-based .lock scheme will help. Thanks, Mark > -Original Message- > From: Ken [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 15, 2007 12:39 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment > > > It should not. > > As long as those two connections are not used across threads > and point to truely different databases. > > They wouldn't be a soft link would they? I > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
It should not. As long as those two connections are not used across threads and point to truely different databases. They wouldn't be a soft link would they? I Mark Brown <[EMAIL PROTECTED]> wrote: Hi John- There is a .lock file for each database. From my understanding, that should prohibit 2 connections from using the same database at the same time. However, that is not the situation I am wondering about. I am specifically wondering if database activity on a connection to DB 1 would have any effect on database activity on a different connection to DB2. Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
--- Mark Brown <[EMAIL PROTECTED]> wrote: > There is a .lock file for each database. From my understanding, that should > prohibit 2 connections from using the same database at the same time. > However, that is not the situation I am wondering about. I am specifically > wondering if database activity on a connection to DB 1 would have any effect > on database activity on a different connection to DB2. Try your sqlite concurrency test under UNIX/Linux on a local filesystem to see if it produces the same serialized access. Based on my limited knowledge of sqlite, I think separate connections to different databases should not impede each other. Would the SQLite developers care to give the definitive statement on this? Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games. http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
Hi John- There is a .lock file for each database. From my understanding, that should prohibit 2 connections from using the same database at the same time. However, that is not the situation I am wondering about. I am specifically wondering if database activity on a connection to DB 1 would have any effect on database activity on a different connection to DB2. Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLITE_BUSY error in multi-threaded environment
My guess is that you will find your problem in the way file locking is implemented on your system. Is there a global file lock rather than locks associated with each file? A simple test program will resolve the issue. Mark Brown wrote: 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] -
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] -
RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
Hi Andre- Thank you for your insight. Looks like we have some redesign scheduled for today. :) 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, - To unsubscribe, send email to [EMAIL PROTECTED] -
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 As you can see here that even thought there are Global critical sections or Mutexes that completely locks on a global level without any other interferences (external connections) The query is busy stepping and has an open cursor, so commit or (spillover) of inserts will fail. In situations where this can be expected, I fetch all data into memory inside the lock and reset the query (sqlite3_reset) releases cursor lock. Then step through data in memory. The other solution you may hear is to use BEGIN IMMEDIATE before performing an operation, this will give any thread an immediate error when trying to begin the same transaction level, however I think that if you have separate database connections then they might not know this until they try to get an exclusive lock on the file for committing. Solution: THREAD1 THREAD2 LOCK QUERY (Read rows into memory) SQLite3_reset UNLOCK LOCK BEGIN TRANSACTION INSERTS COMMIT (no error) UNLOCK Hope this helps my implementation is running smoothly but it's not as concurrent as I would like it to be, but because SQLite is so fast, you can lock globally get in and out as soon as you can, and you should still be happy with the speed. -Original Message- From: Mark Brown [mailto:[EMAIL PROTECTED] Sent: 14 August 2007 10:25 PM To: sqlite-users@sqlite.org Subject: [sqlite] SQLITE_BUSY error in multi-threaded environment Hi- I've got an application that has many different SQLite databases. Each database connection is opened in its own thread. Each database has only one connection. I created some test cases that create a database and schema on the fly and perform various SELECT, INSERTS, UPDATES on it. The tests execute while the rest of the system is running normally. What I am seeing is that while I only have one database connection to my test case database, and my operations on this database are done sequentially, I have seen at random times a return of SQLITE_BUSY on either a prepare or execute of a statement. On a guess, I decided to stop all other database activity going on in the system (db activity on different threads on different databases), and so far, my test cases pass just fine. What I was wondering is if there is any chance that database activity into SQLite from other db connections could somehow influence my db activity on my test database in returning a SQLITE_BUSY error. I'm using SQLite 3.4.1 with the dotlock mechanism for thread protection on a vxWorks custom hardware configuration. With other problems I have had, they turned out to be some file i/o method failing due to our custom h/w, so most likely this is the problem, but just thought I would ask. Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLITE_BUSY error in multi-threaded environment
Hi- I've got an application that has many different SQLite databases. Each database connection is opened in its own thread. Each database has only one connection. I created some test cases that create a database and schema on the fly and perform various SELECT, INSERTS, UPDATES on it. The tests execute while the rest of the system is running normally. What I am seeing is that while I only have one database connection to my test case database, and my operations on this database are done sequentially, I have seen at random times a return of SQLITE_BUSY on either a prepare or execute of a statement. On a guess, I decided to stop all other database activity going on in the system (db activity on different threads on different databases), and so far, my test cases pass just fine. What I was wondering is if there is any chance that database activity into SQLite from other db connections could somehow influence my db activity on my test database in returning a SQLITE_BUSY error. I'm using SQLite 3.4.1 with the dotlock mechanism for thread protection on a vxWorks custom hardware configuration. With other problems I have had, they turned out to be some file i/o method failing due to our custom h/w, so most likely this is the problem, but just thought I would ask. Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
