I just want to add another hint that I learned yesterday: when sqlite3_step() returns SQLITE_LOCKED be aware that sqlite3_reset() will most likely also return SQLITE_LOCKED and thus it is necessary to repeat calling it until it returns SQLITE_OK.
Maybe it would be agood idea to have these kind of info somewhere bundled in the sqlite docs? I found it sometimes difficult to get such issues extracted or collected from the sqlite pages, resulting in a lot of try and error loops and generating traffic on the mailing list... :-) Marcus > > On Mar 6, 2009, at 2:53 AM, Hynes, Tom wrote: > >> Dan, >> >> I am a little confused now about how to treat a SQLITE_LOCKED >> error. Is this to be treated the same as SQLITE_BUSY (i.e. can >> retry, unless an explicit transaction is in effect and the statement >> is not a COMMIT, in which case should roll back), or should it be >> treated differently? If the latter, should it generally be handled >> like most other db errors (e.g. SQLITE_CONSTRAINT, SQLITE_CANTOPEN, >> etc.), or is there some specific error handling for SQLITE_LOCKED >> that is recommended? > > Usually when sqlite3_step() returns something other than SQLITE_ROW, the > statement needs to be reset (sqlite3_reset()) before execution > can be re-attempted. However, SQLITE_BUSY errors are an exception. > After SQLITE_BUSY is returned, you can call sqlite3_step() again > immediately. > This I either didn't know, or forgot. > > An SQLITE_LOCKED error means there is something preventing execution > within the same process. For example a lock on a shared-cache table. > For some applications, for example if another thread may release the > lock shortly, it may be appropriate to retry the query. In other apps, > for example single-threaded apps, it may not. > > To retry a query that has failed with an SQLITE_LOCKED error, you need > to reset the statement before trying sqlite3_step() again. > > Dan. > > > > >> Thanks, >> >> Tom >> >> -----Original Message----- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org >> ] On Behalf Of Dan >> Sent: Thursday, March 05, 2009 12:38 PM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step() >> >> >> On Mar 6, 2009, at 12:22 AM, Marcus Grimm wrote: >> >>> Dan, >>> >>> I'm not sure what you mean by technically the SQLITE_BUSY is >>> also wrong. In the test program I get the SQLITE_BUSY quite >>> often and by retrying the sqlite3_step() it will sooner or >>> later succeed. >> >> Huh. My mistake. >> >> >> >>> Thanks for confirming that it may happend only for the >>> first call to sqlite3_step(). This is an important point and >>> it makes it in fact easy to workaround. >>> I've changed my test application accordingly and now it can >>> run endless, I even increased now to 10 reader threads and 2 writer >>> threads in >>> parallel without making sqlite particular nervous. Wonderful... :-) >>> >>> Thanks again >>> >>> Marcus >>> >>> Dan wrote: >>>> On Mar 5, 2009, at 11:14 PM, Marcus Grimm wrote: >>>> >>>>> Hi Dan, >>>>> >>>>> thank you! >>>>> >>>>> Yes! I was not aware that it is wrong to redo a step >>>>> when it previously returned the LOCKED state. >>>>> That was the reason for the (correct) MISSUSE return on >>>>> the 2nd attempt. >>>>> >>>>> I think this is the main difference in my case between >>>>> shared cache on/off: Without shared cache I never >>>>> get this lock state and it just does the BUSY handling correctly. >>>> >>>> Technically the SQLITE_BUSY handling is also wrong, for the >>>> same reasons. But it's harder to hit a real SQLITE_BUSY than it >>>> is to hit an SQLITE_LOCKED in shared-cache mode. >>>> >>>>> I'll change my locking handling accordingly, seems to be a problem >>>>> of the very first step after a prepare, so that's should to be easy >>>>> to reset... >>>> >>>> That's correct. An SQLITE_LOCKED or SQLITE_BUSY may only occur >>>> on the first call to sqlite3_step(). >>>> >>>> Dan. >>>> >>>> >>>> >>>>> Thanks again for your feedback >>>>> >>>>> Marcus >>>>> >>>>> Dan wrote: >>>>>> On Mar 5, 2009, at 9:57 PM, Marcus Grimm wrote: >>>>>> >>>>>>> Hi again, >>>>>>> >>>>>>> I did a little test program that simulates the multi threading >>>>>>> issue to understand better why I get a MISSUSE error even >>>>>>> when sqlite_prepare returns no error. >>>>>>> The test program will printout some errors on the console >>>>>>> and exits in that case. If I don't use the shared cache it >>>>>>> runs endless without any problem. >>>>>>> >>>>>>> The code is to long to be placed here, but I would be very happy >>>>>>> if somebody could take a look. I've put it on a web server to >>>>>>> download: >>>>>>> >>>>>>> http://www.exomio.de/sqlitethreadtest.c >>>>>>> >>>>>>> I'm using MS Visual Studio 2008, the program is started within a >>>>>>> dosbox to see the error output. Using it on my dual core PC >>>>>>> it allmost immediately stops with the missuse error, ....but why? >>>>>> do >>>>>> { >>>>>> rc = sqlite3_step(hs); >>>>>> >>>>>> if( (rc == SQLITE_BUSY) || (rc == SQLITE_LOCKED) ) >>>>>> { >>>>>> Sleep(SQLTM_TIME); >>>>>> >>>>>> n++; >>>>>> } >>>>>> }while( (n < SQLTM_COUNT) && ((rc == SQLITE_BUSY) || (rc == >>>>>> SQLITE_LOCKED))); >>>>>> >>>>>> If sqlite3_step() returns SQLITE_BUSY or SQLITE_LOCKED, you need >>>>>> to >>>>>> reset the statement before trying the sqlite3_step() again. >>>>>> >>>>>> Dan. >>>>>> >>>>>> >>>>>> >>>>>> >>>>>>> Thanks you >>>>>>> >>>>>>> kind regards >>>>>>> >>>>>>> Marcus Grimm >>>>>>> >>>>>>> Marcus Grimm wrote: >>>>>>>> Hi Ken, >>>>>>>> >>>>>>>> yes, I do check the return values also for the >>>>>>>> sqlite3_prepare_v2 call. This is allways >>>>>>>> successful, the first sqlite_step call right after >>>>>>>> this one returns the magic MISSUSE. >>>>>>>> >>>>>>>> Yes, I also tried to call sqlite_reset right >>>>>>>> after this first error and in this case >>>>>>>> sqlite_reset return the LOCK state. I didn't >>>>>>>> yet go on to handle this... >>>>>>>> >>>>>>>> I can't post the code of the original implementation here, >>>>>>>> but also for my debugging and tests I'll produce >>>>>>>> a simplified test application. Just to see if that >>>>>>>> can be used to reproduce the effect. If that one runs >>>>>>>> without any problem, it's my fault, if not: I'll post >>>>>>>> it here for further discussions... ;) >>>>>>>> >>>>>>>> Thanks for your feedback >>>>>>>> >>>>>>>> Marcus >>>>>>>> >>>>>>>> >>>>>>>>> Marcus, >>>>>>>>> >>>>>>>>> I'm not sure if this will help or not... >>>>>>>>> >>>>>>>>> But I noticed your sample code does not test the return value >>>>>>>>> from >>>>>>>>> the >>>>>>>>> sqlite3_prepare_v2 call. Could the code be entering the do >>>>>>>>> loop >>>>>>>>> when an >>>>>>>>> error was returned from prepare? >>>>>>>>> >>>>>>>>> Just an idea. >>>>>>>>> >>>>>>>>> Have you tried the reset call as DRH had suggested? >>>>>>>>> Your prior post indicated you had more code and another loop. >>>>>>>>> Can >>>>>>>>> you post >>>>>>>>> the full code for both loops? >>>>>>>>> >>>>>>>>> HTH >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> --- On Wed, 3/4/09, Marcus Grimm <mgr...@medcom-online.de> >>>>>>>>> wrote: >>>>>>>>> >>>>>>>>>> From: Marcus Grimm <mgr...@medcom-online.de> >>>>>>>>>> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on >>>>>>>>>> sqlite3_step() >>>>>>>>>> To: kennethinbox-sql...@yahoo.com, "General Discussion of >>>>>>>>>> SQLite >>>>>>>>>> Database" <sqlite-users@sqlite.org> >>>>>>>>>> Date: Wednesday, March 4, 2009, 4:31 PM >>>>>>>>>> Ken, >>>>>>>>>> >>>>>>>>>> you are of course right that it needs some checks >>>>>>>>>> for locks and busy states. I left that out to >>>>>>>>>> simplify the code given below. My original code >>>>>>>>>> checks that and it usually works quite well. >>>>>>>>>> that's basically the reason why I was puzzled >>>>>>>>>> by the randomly MISUSE results after I added the shared >>>>>>>>>> cache. >>>>>>>>>> >>>>>>>>>> I'm not yet finished with my debugging since it is a >>>>>>>>>> multithreading/collision issue - ugly to trace. >>>>>>>>>> >>>>>>>>>> My feeling is that it is related to the condition when >>>>>>>>>> one thread is attempting or holding an exclusive lock >>>>>>>>>> while another thread is just doing an sqlite_step (read >>>>>>>>>> only) >>>>>>>>>> on an allready created statement. Both threads use their >>>>>>>>>> own >>>>>>>>>> DB connections. For example: when I do a sqlite_reset >>>>>>>>>> right after sqlite_step returns SQLITE_MISUSE, as Richard >>>>>>>>>> suggest, >>>>>>>>>> I get immediately a SQLITE_LOCK return code from >>>>>>>>>> sqlite_reset in this case. Why I didn't get that before >>>>>>>>>> or >>>>>>>>>> from the sqlite_prepare ? >>>>>>>>>> >>>>>>>>>> Anyway, I'm going on to workaround this.... ALso I >>>>>>>>>> would like >>>>>>>>>> to mention once more that it is only during an artificial >>>>>>>>>> stress >>>>>>>>>> test, mainly to verify my implementation. Under normal >>>>>>>>>> usercondition it is very unlikely to happend and sqlite >>>>>>>>>> works perfect as expected. >>>>>>>>>> >>>>>>>>>> Thanks >>>>>>>>>> >>>>>>>>>> Marcus >>>>>>>>>> >>>>>>>>>>> Marcus, >>>>>>>>>>> >>>>>>>>>>> You might want to also add some checks in for >>>>>>>>>> sqlite_busy as on the result >>>>>>>>>>> of the prepare and the first call to sqlite_step. >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> On the inner loop test for the most common case first >>>>>>>>>> (SQLITE_ROW) then >>>>>>>>>>> test for errors... Slight performance improvement... >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> --- On Wed, 3/4/09, Marcus Grimm >>>>>>>>>> <mgr...@medcom-online.de> wrote: >>>>>>>>>>>> From: Marcus Grimm <mgr...@medcom-online.de> >>>>>>>>>>>> Subject: Re: [sqlite] shared cache and >>>>>>>>>> SQLITE_MISUSE on sqlite3_step() >>>>>>>>>>>> To: "General Discussion of SQLite >>>>>>>>>> Database" <sqlite-users@sqlite.org> >>>>>>>>>>>> Date: Wednesday, March 4, 2009, 10:25 AM >>>>>>>>>>>> Richard, thanks again for the feedback. >>>>>>>>>>>> >>>>>>>>>>>> However, I don't see how it can happend that >>>>>>>>>> the >>>>>>>>>>>> statement >>>>>>>>>>>> is completed internally without returning >>>>>>>>>> SQLITE_DONE. >>>>>>>>>>>> In the particular code of the "reading >>>>>>>>>> thread" I >>>>>>>>>>>> do something like: >>>>>>>>>>>> >>>>>>>>>>>> -- >>>>>>>>>>>> sqlite3_prepare_v2(db, "SELECT * FROM >>>>>>>>>> TableA", >>>>>>>>>>>> -1, &stmt, 0); >>>>>>>>>>>> /** step throu table result **/ >>>>>>>>>>>> do >>>>>>>>>>>> { >>>>>>>>>>>> rc = sqlite3_step(stmt); >>>>>>>>>>>> if( rc == SQLITE_MISUSE ) >>>>>>>>>>>> { fprintf(stderr, "ERROR...\n"); >>>>>>>>>> break; } >>>>>>>>>>>> else >>>>>>>>>>>> if( rc == SQLITE_ROW ) >>>>>>>>>>>> read_data_etc(); >>>>>>>>>>>> else >>>>>>>>>>>> break; >>>>>>>>>>>> }while( rc != SQLITE_DONE ); >>>>>>>>>>>> >>>>>>>>>>>> sqlite3_finalize(stmt); >>>>>>>>>>>> -- >>>>>>>>>>>> >>>>>>>>>>>> The prepare statement pointer is defined locally >>>>>>>>>> and no >>>>>>>>>>>> other >>>>>>>>>>>> thread can access it, except sqlite internal >>>>>>>>>> maybe. >>>>>>>>>>>> To me it looks that in case a parallel thread is >>>>>>>>>> inserting >>>>>>>>>>>> or >>>>>>>>>>>> updating data, the above loop is somehow affected >>>>>>>>>> and >>>>>>>>>>>> returns the >>>>>>>>>>>> MISUSE. >>>>>>>>>>>> >>>>>>>>>>>> Your reply so far indicates either a bug on my >>>>>>>>>> side or a >>>>>>>>>>>> missusage. >>>>>>>>>>>> I'll go on and try to find the reason why the >>>>>>>>>> magic >>>>>>>>>>>> number is resetted >>>>>>>>>>>> without knowing from calling functions. >>>>>>>>>>>> >>>>>>>>>>>> calling sqlite3_reset in these cases is difficult >>>>>>>>>> for me >>>>>>>>>>>> since it might >>>>>>>>>>>> not be the first step and previous data is >>>>>>>>>> allready in use >>>>>>>>>>>> in the upper loop. >>>>>>>>>>>> But maybe an option for the very first step if >>>>>>>>>> that >>>>>>>>>>>> fails... hm... I'll try that. >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> Thanks again >>>>>>>>>>>> >>>>>>>>>>>> kind regards >>>>>>>>>>>> >>>>>>>>>>>> Marcus Grimm >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> D. Richard Hipp wrote: >>>>>>>>>>>>> On Mar 4, 2009, at 9:35 AM, Marcus Grimm >>>>>>>>>> wrote: >>>>>>>>>>>>>> hi, >>>>>>>>>>>>>> >>>>>>>>>>>>>> OK, the value of p->magic is 519C2973 >>>>>>>>>>>> (VDBE_MAGIC_HALT) >>>>>>>>>>>>> That means the prepared statement has run to >>>>>>>>>>>> completion and needs to >>>>>>>>>>>>> be reset using sqlite3_reset() before you >>>>>>>>>> continue. >>>>>>>>>>>>> D. Richard Hipp >>>>>>>>>>>>> d...@hwaci.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 >>>>>> _______________________________________________ >>>>>> 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 >> >> >> _______________________________________________ >> 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
