Without wishing to complicate the subject, I have found that retrying a sqlite3_step() after receiving SQLITE_LOCKED works fine for me without needing sqlite3_reset(). I do this in a multi-threaded test app (based on some old SQLite test code) that I have left running with 10 threads for a long time and did not encounter any errors. I was using shared-cache.
Are there perhaps some cases where a reset is required on SQLITE_LOCKED, and other cases where it can be interpreted the same as SQLITE_BUSY? Cheers, Dave. -----Original Message----- From: Hynes, Tom [mailto:tom.hy...@inin.com] Sent: 06 March 2009 05:10 To: General Discussion of SQLite Database Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step() Thanks Dan! -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Sent: Thursday, March 05, 2009 10:08 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step() 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
