> So as long as you're in the simple case (e.g. doing a simple SELECT while > another process INSERTs). you should never see a deadlock, correct?
Yes, as long as you don't use transactions, or your transactions are homogeneous (in terms of using only SELECTs or only INSERTs/UPDATEs/DELETEs), or all your non-homogeneous transactions start with BEGIN IMMEDIATE or BEGIN EXCLUSIVE. > And the explanation would seem to indicate this: > sqlite3_busy_handler() -- do this on your prepared statement Busy handler is set per-connection, not per-statement. > if sqlite3_step() returns SQLITE_BUSY then something is terribly wrong as > this should only occur in a deadlock. It occurs in a deadlock or when busy handler is tired of waiting and decided that it's enough. > I guess my question ends up being how does one guarantee this works: If busy handler allows infinite waiting and there's no access to the database from any other process then yes, this pseudo code will never return SQLITE_BUSY. Pavel On Thu, May 13, 2010 at 8:58 AM, Black, Michael (IS) <[email protected]> wrote: > OK...so after reading that it sounds like the situation you need to avoid is > a deadlock. > > So as long as you're in the simple case (e.g. doing a simple SELECT while > another process INSERTs). you should never see a deadlock, correct? > > And the explanation would seem to indicate this: > sqlite3_busy_handler() -- do this on your prepared statement > if sqlite3_step() returns SQLITE_BUSY then something is terribly wrong as > this should only occur in a deadlock. > > I guess my question ends up being how does one guarantee this works: > > process1: > sqlite3_open() > while(1) { > sqlite3_prepare_v2("insert stuff"); > sqlite3_busy_handler(); > sqlite3_step(); // this should NEVER return SQLITE_BUSY -- busy handler > takes care > sqlite3_finalize(); > } > > process2: > sqlite3_open() > while(1) { > sqlite3_prepare_v2("select stuff"); > sqlite3_busy_handler(); > sqlite3_step(); // this should NEVER return SQLITE_BUSY > sqlite3_finalize(); > } > > Michael D. Black > Senior Scientist > Northrop Grumman Mission Systems > > > ________________________________ > > From: [email protected] on behalf of Pavel Ivanov > Sent: Thu 5/13/2010 7:40 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] multi processes,so many errores of SQLITE_BUSY and > SQLITE_MISUSE > > > >> Hmmm...bad assumption on my part....seems so logical that if it's busy just >> try again -- and that idea worked on Liubin's problem. > > Read 4th paragraph from here > http://www.sqlite.org/c3ref/busy_handler.html ("The presence of a busy > handler ...") and see why it's not a good idea to always wait while > database is busy (word "locked" here would be better than "busy"). > >> I see this http://www.sqlite.org/c3ref/stmt.html -- but it doesn't quite >> address the BUSY condition. > > See here http://www.sqlite.org/c3ref/step.html: > > "SQLITE_BUSY means that the database engine was unable to acquire the > database locks it needs to do its job. If the statement is a COMMIT or > occurs outside of an explicit transaction, then you can retry the > statement. If the statement is not a COMMIT and occurs within a > explicit transaction then you should rollback the transaction before > continuing." > > From the same page: > > "SQLITE_ERROR means that a run-time error (such as a constraint > violation) has occurred. sqlite3_step() should not be called again on > the VM. More information may be found by calling sqlite3_errmsg(). > With the legacy interface, a more specific error code (for example, > SQLITE_INTERRUPT, SQLITE_SCHEMA, SQLITE_CORRUPT, and so forth) can be > obtained by calling sqlite3_reset() on the prepared statement. In the > "v2" interface, the more specific error code is returned directly by > sqlite3_step(). > > SQLITE_MISUSE means that the this routine was called inappropriately. > Perhaps it was called on a prepared statement that has already been > finalized or on one that had previously returned SQLITE_ERROR or > SQLITE_DONE." > > > I believe you can put words "error code" in this text instead of > "SQLITE_ERROR", its meaning will be the same - if you call > sqlite3_step after an error again then SQLITE_MISUSE will be returned > (although from my experience in some cases repeated sqlite3_step can > work, but generally you better call sqlite3_reset). > > > Pavel > > On Thu, May 13, 2010 at 8:13 AM, Black, Michael (IS) > <[email protected]> wrote: >> Hmmm...bad assumption on my part....seems so logical that if it's busy just >> try again -- and that idea worked on Liubin's problem. I see this >> http://www.sqlite.org/c3ref/stmt.html -- but it doesn't quite address the >> BUSY condition. >> >> I also found this thread >> http://www.mail-archive.com/[email protected]/msg06916.html >> >> Which doesn't mention using reset (perhaps he is but just doesn't mention >> it) but using busy_handler instead. This guy seems to have stressed it >> pretty hard. >> >> >> >> >> >> Michael D. Black >> Senior Scientist >> Northrop Grumman Mission Systems >> >> >> ________________________________ >> >> From: [email protected] on behalf of Pavel Ivanov >> Sent: Thu 5/13/2010 6:40 AM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] multi processes,so many errores of SQLITE_BUSY and >> SQLITE_MISUSE >> >> >> >>> I assume you want the sqllite3_stmt to work -- so you need to loop that >>> while it's busy. >> >> Michael, don't give bad advices. >> The most general advice when one gets SQLITE_BUSY is to reset/finalize >> all statements and rollback current transaction. It's only in certain >> type of transactions and certain type of statements one can loop while >> sqlite3_stmt returns SQLITE_BUSY. But even in this case one must call >> sqlite3_reset on the statement that returned SQLITE_BUSY. And this >> call to sqlite3_reset will return SQLITE_BUSY again. >> >> >> Pavel >> >> On Thu, May 13, 2010 at 7:20 AM, Black, Michael (IS) >> <[email protected]> wrote: >>> It also means you're not handling the SQLITE_BUSY from the sqlite3_stmt() >>> before it. >>> I assume you want the sqllite3_stmt to work -- so you need to loop that >>> while it's busy. >>> >>> Michael D. Black >>> Senior Scientist >>> Northrop Grumman Mission Systems >>> >>> >>> ________________________________ >>> >>> From: [email protected] on behalf of liubin liu >>> Sent: Thu 5/13/2010 2:07 AM >>> To: [email protected] >>> Subject: Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and >>> SQLITE_MISUSE >>> >>> >>> >>> >>> I see many SQLITE_BUSY returned from sqlite3_finalize(). Are they memory >>> leak >>> because it don't succeed in finalizing the sqlite3_stmt pointer? >>> >>> >>> >>> >>> Black, Michael (IS) wrote: >>>> >>>> SQLITE_BUSY is not an error...just a fact. >>>> >>>> All your processes cannot work on the database at the same time...at least >>>> not when one of them is doing an insert. You could be changing the table >>>> while you're scanning it. EXXCLUSIVE doesn't change that idea. >>>> >>>> Somebody please correct me if I'm wrong on this one... >>>> I think sqlite can work with multiple processes just doing read-onliy >>>> operations (like SELECT). It's just the write operations >>>> (INSERT/UPDATE) which will cause SQLITE_BUSY to occur. >>>> >>>> Michael D. Black >>>> Senior Scientist >>>> Northrop Grumman Mission Systems >>>> >>>> >>>> ________________________________ >>>> >>>> From: [email protected] on behalf of liubin liu >>>> Sent: Tue 5/11/2010 9:20 PM >>>> To: [email protected] >>>> Subject: Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and >>>> SQLITE_MISUSE >>>> >>>> >>>> >>>> >>>> Thank you very much! >>>> >>>> It may be because my system's resource is limited. It's a embedded system >>>> containing 32M RAM, ARM9 CPU. >>>> >>>> My "reiterating 20 times" is already using usleep(). >>>> >>>> After I add the loop in the prepare statements, the system performance is >>>> still very bad... And there are still many errores of SQLITE_BUSY. >>>> >>>> The only improvement is the disappear of the error of SQLITE_MISUSE. >>>> >>>> And I tried to use the "BEGIN EXCLUSIVE TRANSACTION". The things are same >>>> with them without using it. >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> Black, Michael (IS) wrote: >>>>> >>>>> Your "reiterating 20 times" is not using a usleep so you'll blow by this >>>>> most every time it's busy. >>>>> >>>>> Do this instead in all your proc's >>>>> >>>>> ret = sqlite3_step (p_stmt); >>>>> if (SQLITE_BUSY == ret) >>>>> { >>>>> int n=0; >>>>> usleep(100000); // try one more time before error >>>>> while ((ret=sqlite3_step(p_stmt))==SQLITE_BUSY) { >>>>> printf("proc1 ret==BUSY %d\n",++n); >>>>> usleep(100000); >>>>> } >>>>> } >>>>> >>>>> And you'll also need to handle "database is locked" coming from your >>>>> prepare statements. I saw that error too. >>>>> You'll need to loop there too. >>>>> >>>>> The more you drop the usleep time the more times it will show as busy. >>>>> 1/10th or 1/100th of second is about all you want I would think. >>>>> >>>>> And get rid of the usleep at the bottom of each proc -- it's pretty >>>>> useless at 100 microseconds. You don't need to sleep unless you're busy. >>>>> >>>>> I tested your code with this and got no errors at all -- just a bunch of >>>>> BUSY messages. >>>>> >>>>> >>>>> Not sure what your purpose is in sqlrun.c with looping and killing. >>>>> Looks >>>>> pretty squirrely to me. You're not waiting for the forks to finish so >>>>> what is your logic here? >>>>> >>>>> Michael D. Black >>>>> Senior Scientist >>>>> Northrop Grumman Mission Systems >>>>> >>>>> >>>>> ________________________________ >>>>> >>>>> From: [email protected] on behalf of liubin liu >>>>> Sent: Tue 5/11/2010 4:57 AM >>>>> To: [email protected] >>>>> Subject: [sqlite] multi processes, so many errores of SQLITE_BUSY and >>>>> SQLITE_MISUSE >>>>> >>>>> ... >>>>> >>>>> -- >>>>> View this message in context: >>>>> http://old.nabble.com/multi-processes%2C-so-many-errores-of-SQLITE_BUSY-and-SQLITE_MISUSE-tp28522127p28522127.html >>>>> Sent from the SQLite mailing list archive at Nabble.com. >>>>> >>>>> _______________________________________________ >>>>> sqlite-users mailing list >>>>> [email protected] >>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>>> >>>>> >>>>> >>>>> _______________________________________________ >>>>> sqlite-users mailing list >>>>> [email protected] >>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>>> >>>>> >>>> >>>> -- >>>> View this message in context: >>>> http://old.nabble.com/multi-processes%2C-so-many-errores-of-SQLITE_BUSY-and-SQLITE_MISUSE-tp28522127p28531394.html >>>> Sent from the SQLite mailing list archive at Nabble.com. >>>> >>>> _______________________________________________ >>>> sqlite-users mailing list >>>> [email protected] >>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>> >>>> >>>> >>>> _______________________________________________ >>>> sqlite-users mailing list >>>> [email protected] >>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>> >>>> >>> >>> -- >>> View this message in context: >>> http://old.nabble.com/multi-processes%2C-so-many-errores-of-SQLITE_BUSY-and-SQLITE_MISUSE-tp28522127p28544420.html >>> Sent from the SQLite mailing list archive at Nabble.com. >>> >>> _______________________________________________ >>> sqlite-users mailing list >>> [email protected] >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >>> >>> _______________________________________________ >>> sqlite-users mailing list >>> [email protected] >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
