答复: [sqlite] multi_thread for writing
Hi , Yes. I have read the document and I have known that SQLite does not support multiple simultaneous writers. My point is that the Thread A never gets the SQLITE_BUSY return code when executing the "END" statement because Thread A locks the db first. Thread B , I know, it gets SQLITE_BUSY because Thread A lock the db. Do I understand right? But I will try the sqlite3_busy_timeout() ad sqlite3_busy_handler instead. Best regards, Rex From: Kervin L. Pierre [mailto:[EMAIL PROTECTED] Sent: 2005-4-17 (星期日) 2:56 To: sqlite-users@sqlite.org Subject: Re: [sqlite] multi_thread for writing As far as I know, SQLite does not support multiple simultaneous writers. I believe you need to be ready to handle SQLITE_BUSY and SQLITE_SCHEMA errors on every call in a multi- threaded application. SQLITE_BUSY... http://www.sqlite.org/cvstrac/wiki?p=MultiThreading SQLITE_SCHEMA... http://www.sqlite.org/faq.html#q17 The wiki page for multithreaded applications suggests that you use a loop to keep retrying on SQLITE_BUSY but that does not work well, especially since that loop should check for SQLITE_SCHEMA and also have some count to prevent infinite loops. Also backing out of your multiple successful calls to sqlite3_steps() after a single call in a nested loop fails further complicates things, I think. Try use the sqlite3_busy_timeout() or sqlite3_busy_handler() instead. - Kervin RexChan(TP/HK) wrote: > Hi all, > > I meet the problem of multi_thread writing in version 3.2.1. First I create > two threads A and B and each thread has its own db structure. each thread > uses the following SQL commands to do the insert action. > > BEGIN; > . > insert record 200 times > . > . > END; > > Thread A does the insert first and Thread B inserts records during Thread A > is doing the insert action. > > Then the SQLITE_BUSY error is returned to Thread A when doing the "END;" SQL > statement. And Thread B is also returned to SQLITE_BUSY. > And my question is: > > 1. Is it a normal when the error is returned to Thread A when doing the > "END;" SQL statement? > > It seems the Thread A is locking the db although the insert 200 records > action has been done and it wants to do the "END" SQL statement. > > Because I am using ver 2.8.16 and using the same code, it does not happen the > SQLITE_BUSY using its own db structure in each thread. > Do I miss something to do the locking or setting some parameters? thanks! > > Best regards, > Rex >
Re: [sqlite] multi threading
yes it is illegal, cause I've copy/paste then I adapt by replacing th name of table, i've extract irrelevant info and I forgot the "and" but, your statement it's very well thanks and regards - Original Message - From: "Kurt Welgehausen" <[EMAIL PROTECTED]> To: Sent: Sunday, April 17, 2005 11:20 PM Subject: Re: [sqlite] multi threading SELECT id, code FROM a WHERE (code IN (SELECT code FROM (SELECT code, COUNT(code) AS c FROM a GROUP BY code) AS aaa WHERE c > 1) ) and ORDER BY code The "and" in your code is illegal, but it is better to use select id, code from a where code in (select code from a group by code having count(code) > 1) order by code Regards
Re: [sqlite] multi threading
> SELECT id, code FROM a WHERE > (code IN > (SELECT code FROM > (SELECT code, COUNT(code) AS c FROM a GROUP BY code) AS aaa > WHERE c > 1) > ) >and ORDER BY code The "and" in your code is illegal, but it is better to use select id, code from a where code in (select code from a group by code having count(code) > 1) order by code Regards
Re: [sqlite] multi threading
Thank you for your answer, Finally I can use sqlite very well on a multi-threading environment. I can run six threads with an maximum of 25% of processor busy. For each thread I have a new pointer for my wrapper class over sqlite(another connection.. etc...). Thank you. but, I have another question... It is possible this kind of statement? SELECT id, code FROM a WHERE (code IN (SELECT code FROM (SELECT code, COUNT(code) AS c FROM a GROUP BY code) AS aaa WHERE c > 1) ) and ORDER BY code so this query check if the code appears more than once a time in the table -A- I run this kind of query but i got an error. The query is functional, cause I run the same query in sql server, in a database with the same structure. Thank you a lot. - Original Message - From: "D. Richard Hipp" <[EMAIL PROTECTED]> To: Sent: Saturday, April 16, 2005 2:11 AM Subject: Re: [sqlite] multi threading On Fri, 2005-04-15 at 14:44 +0300, Cosmin Vlasiu wrote: Hello to everyone, I have a question... regarding multi-threading... the question is for microsoft windows (a visual c++ application)... I saw the documentation and I understood that for microsoft OS, the multi-threading is enabled by default. So, of course I start two threads, both of them make a loop into a "recordset". the first thread it works good, but the second, no way. I made kind of research in the documentation and I observed that I have to make new connections for each thread. All donne. But when I run the application I got the same problem. In the second thread the sqlite3_prepare function always return the 21 value that means SQLITE_MISUSE, because sqlite3SafetyOn retun 1. Can somebody tells me if there is a solution for that problem? Two separate threads may not use the same database handle at the same time. If they do, the SQLITE_MISUSE value is often returned. Separate threads should have their own database handles. I know you said above that you are using separate database handles in your two threads. But probably there is a bug in your code that is preventing this from happening really. My advice to *all* programmers is to never use more than one thread in the same address space. I have never in 20 years worked on a multiple threaded program that actually got all of the threading issues right. There are always subtle bugs that cause error that are very difficult to reproduce and fix. Multithreading is the fastest road to buggy code that I know of. Avoid it. If you absolutely, positively must have multiple threads of control, put each thread in its own address space (make it a process.) -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Problem storing integers
On Sun, 2005-04-17 at 08:03 -0400, Ken & Deb Allen wrote: > When can we expect a 3.2.2 release that includes this correction? I am > working on a database that depends on 64-bit integer values. > You can pull down the latest code from CVS anytime you want. The CVS client is available for windows and comes preinstalled on everything else. Or you can just look at the patch and fix the problem in your local copy. (The bug fix involves transposing two characters on a single line.) I do not have a schedule for the release of the next version of SQLite. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Problem storing integers
When can we expect a 3.2.2 release that includes this correction? I am working on a database that depends on 64-bit integer values. -ken On 15-Apr-05, at 7:35 AM, D. Richard Hipp wrote: On Fri, 2005-04-15 at 10:53 +0100, Richard Boulton wrote: I'm running the latest sqlite 3.2.1 command line tool on Windows XP I've just run some older versions of the command line tool and the last time the value 281474976710655 was stored correctly was 3.0.8 I was missing a couple of releases after 3.0.8 but saw the unexpected behaviour start in 3.1.2. Maybe it was introduced in 3.1? The bug was introduced by check-in [2246] on 2005-Jan-20 just prior to 3.1.0. Any integer between 140737488355328 and 281474976710655 is stored incorrectly. I'll put in a patch sometime today. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] ANN: new version of sqlite3Explorer (1.2)
Adds a simple report generator.