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 >>>> >>> -- >>> Marcus Grimm, MedCom GmbH Darmstadt, Rundeturmstr. 12, 64283 >>> Darmstadt >>> Tel: +49(0)6151-95147-10 >>> Fax: +49(0)6151-95147-20 >>> -------------------------------------------------- >>> MedCom slogans of the month: >>> "Vacation ? -- Every day at MedCom is a paid vacation!" >>> "Friday I have monday in my mind." >>> "MedCom -- Every week a vacation, every day an event, every hour a >>> cliffhanger, >>> every minute a climax." >>> "Damned, it's weekend again!" >>> _______________________________________________ >>> 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 >> > > -- > Marcus Grimm, MedCom GmbH Darmstadt, Rundeturmstr. 12, 64283 Darmstadt > Tel: +49(0)6151-95147-10 > Fax: +49(0)6151-95147-20 > -------------------------------------------------- > MedCom slogans of the month: > "Vacation ? -- Every day at MedCom is a paid vacation!" > "Friday I have monday in my mind." > "MedCom -- Every week a vacation, every day an event, every hour a > cliffhanger, > every minute a climax." > "Damned, it's weekend again!" > _______________________________________________ > 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
