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. 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... 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
