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
