> 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) <michael.bla...@ngc.com> 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/sqlite-users@sqlite.org/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: sqlite-users-boun...@sqlite.org 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) > <michael.bla...@ngc.com> 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: sqlite-users-boun...@sqlite.org on behalf of liubin liu >> Sent: Thu 5/13/2010 2:07 AM >> To: sqlite-users@sqlite.org >> 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: sqlite-users-boun...@sqlite.org on behalf of liubin liu >>> Sent: Tue 5/11/2010 9:20 PM >>> To: sqlite-users@sqlite.org >>> 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: sqlite-users-boun...@sqlite.org on behalf of liubin liu >>>> Sent: Tue 5/11/2010 4:57 AM >>>> To: sqlite-users@sqlite.org >>>> 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 >>>> sqlite-users@sqlite.org >>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>> >>>> >>>> >>>> _______________________________________________ >>>> sqlite-users mailing list >>>> sqlite-users@sqlite.org >>>> 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 >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >>> >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> 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 >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
