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
