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
