Re: [sqlite] use of sqlite in a multithread C application
Rafi, Do not forget that if you do prepare and perform an execute in different threads, that you should not attempt to execute multiple statements compiled against the same sqlite3 database pointer concurrently. Trust me, I made this mistake. If you are preparing multiple statements, make sure they are executed in a serial fashion. On 3/2/07, Dennis Cote <[EMAIL PROTECTED]> wrote: Rafi Cohen wrote: > Another question: may I prepare all statements tha need to be prepared > in one thread, but for part of them apply the execution process > (bind-step-reset) I n the second thread or whould it be best to prepare > statements in the same thread I intend to execute them later? > > Rafi, It don't think it matters. Either way should work. From a design point of view it may be better to decouple the two threads by having each prepare its own statements. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] use of sqlite in a multithread C application
Rafi Cohen wrote: Another question: may I prepare all statements tha need to be prepared in one thread, but for part of them apply the execution process (bind-step-reset) I n the second thread or whould it be best to prepare statements in the same thread I intend to execute them later? Rafi, It don't think it matters. Either way should work. From a design point of view it may be better to decouple the two threads by having each prepare its own statements. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] use of sqlite in a multithread C application
Thanks Denis, this helped a lot and actually that's what I was thinking to do. Another question: may I prepare all statements tha need to be prepared in one thread, but for part of them apply the execution process (bind-step-reset) I n the second thread or whould it be best to prepare statements in the same thread I intend to execute them later? Thanks, Rafi. -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thursday, March 01, 2007 7:48 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] use of sqlite in a multithread C application Rafi Cohen wrote: > Hi, I read the good article on this subject and also the api > refference in the documentation and still feel that I need to ask the > following question. My application has 2 threads. The main one needs > to retrieve data thru select commands but does not change the > database, while the other thread will change the database quite often. > My questions are: should I open the database in each thread separately > or can I open it once for the whole program? in the second case, does it > matter inh which thread I open it? last, if the main thread happens to > retrieve data while the other thread is in a transaction changing the > database, I would prefer the main thread wait till the end of the > transaction and retrieve the most updated data. How do you suggest to > implement it? > looping on sqlite3_busy_handler should be the way to go? > Thanks, Rafi. > > Rafi, For your access you would be best off to have the main thread open the connection and pass a reference to it to the other thread (or you could use a global connection variable). Then use a mutex to have the threads block until they can get exclusive access to the connection. Once they have the mutex they can do their read or write with no concern for interruption from the other thread (it will block on the mutex if it tries to start a database access). HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.5/706 - Release Date: 2/28/2007 4:09 PM - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] use of sqlite in a multithread C application
I found that although sqlite claims thread safeness it is actually in your hands to implement a thread safe access pattern. Here is how I implemented my sqlite thread saftey. Each thread opens its on connection. All operations begin with a do { BEGIN EXCLUSIVE if (isbusy) (sleep some amount of time... ). } while (sqlite isbusy); YOUR SQL STATEMENTS HERE. And end with a COMMIT; The begin forces sqlite to lock the DB for exclusive access. This makes the remaining access error handling relatively simple. No need to check for busy and do rollbacks with restarting logic... Just handle errors, in my log the error to a file and returns a Failure code which typically causes the application to exit. Don't to forget to compile the sqlite library with -DTHREAD_SAFE Rafi Cohen <[EMAIL PROTECTED]> wrote: Hi, I read the good article on this subject and also the api refference in the documentation and still feel that I need to ask the following question. My application has 2 threads. The main one needs to retrieve data thru select commands but does not change the database, while the other thread will change the database quite often. My questions are: should I open the database in each thread separately or can I open it once for the whole program? in the second case, does it matter inh which thread I open it? last, if the main thread happens to retrieve data while the other thread is in a transaction changing the database, I would prefer the main thread wait till the end of the transaction and retrieve the most updated data. How do you suggest to implement it? looping on sqlite3_busy_handler should be the way to go? Thanks, Rafi.
[sqlite] use of sqlite in a multithread C application
Hi, I read the good article on this subject and also the api refference in the documentation and still feel that I need to ask the following question. My application has 2 threads. The main one needs to retrieve data thru select commands but does not change the database, while the other thread will change the database quite often. My questions are: should I open the database in each thread separately or can I open it once for the whole program? in the second case, does it matter inh which thread I open it? last, if the main thread happens to retrieve data while the other thread is in a transaction changing the database, I would prefer the main thread wait till the end of the transaction and retrieve the most updated data. How do you suggest to implement it? looping on sqlite3_busy_handler should be the way to go? Thanks, Rafi.