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
