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