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. I guess you mean that there are condition when a BUSY state will NOT allow to retry the sqlite3_step like mentioned in the docs for sqlite3_step: "If the statement is not a COMMIT and occurs within a explicit transaction then you should rollback the transaction before continuing." I have to think about if I have such a condition.
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
