> So as long as you're in the simple case (e.g. doing a simple SELECT while
> another process INSERTs). you should never see a deadlock, correct?
Yes, as long as you don't use transactions, or your transactions are
homogeneous (in terms of using only SELECTs or only
INSERTs/UPDATEs/DELETEs), or all your non-homogeneous transactions
start with BEGIN IMMEDIATE or BEGIN EXCLUSIVE.
> And the explanation would seem to indicate this:
> sqlite3_busy_handler() -- do this on your prepared statement
Busy handler is set per-connection, not per-statement.
> if sqlite3_step() returns SQLITE_BUSY then something is terribly wrong as
> this should only occur in a deadlock.
It occurs in a deadlock or when busy handler is tired of waiting and
decided that it's enough.
> I guess my question ends up being how does one guarantee this works:
If busy handler allows infinite waiting and there's no access to the
database from any other process then yes, this pseudo code will never
return SQLITE_BUSY.
Pavel
On Thu, May 13, 2010 at 8:58 AM, Black, Michael (IS)
<michael.bla...@ngc.com> wrote:
> OK...so after reading that it sounds like the situation you need to avoid is
> a deadlock.
>
> So as long as you're in the simple case (e.g. doing a simple SELECT while
> another process INSERTs). you should never see a deadlock, correct?
>
> And the explanation would seem to indicate this:
> sqlite3_busy_handler() -- do this on your prepared statement
> if sqlite3_step() returns SQLITE_BUSY then something is terribly wrong as
> this should only occur in a deadlock.
>
> I guess my question ends up being how does one guarantee this works:
>
> process1:
> sqlite3_open()
> while(1) {
> sqlite3_prepare_v2("insert stuff");
> sqlite3_busy_handler();
> sqlite3_step(); // this should NEVER return SQLITE_BUSY -- busy handler
> takes care
> sqlite3_finalize();
> }
>
> process2:
> sqlite3_open()
> while(1) {
> sqlite3_prepare_v2("select stuff");
> sqlite3_busy_handler();
> sqlite3_step(); // this should NEVER return SQLITE_BUSY
> sqlite3_finalize();
> }
>
> Michael D. Black
> Senior Scientist
> Northrop Grumman Mission Systems
>
>
> ________________________________
>
> From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov
> Sent: Thu 5/13/2010 7:40 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] multi processes,so many errores of SQLITE_BUSY and
> SQLITE_MISUSE
>
>
>
>> Hmmm...bad assumption on my part....seems so logical that if it's busy just
>> try again -- and that idea worked on Liubin's problem.
>
> Read 4th paragraph from here
> http://www.sqlite.org/c3ref/busy_handler.html ("The presence of a busy
> handler ...") and see why it's not a good idea to always wait while
> database is busy (word "locked" here would be better than "busy").
>
>> I see this http://www.sqlite.org/c3ref/stmt.html -- but it doesn't quite
>> address the BUSY condition.
>
> See here http://www.sqlite.org/c3ref/step.html:
>
> "SQLITE_BUSY means that the database engine was unable to acquire the
> database locks it needs to do its job. If the statement is a COMMIT or
> occurs outside of an explicit transaction, then you can retry the
> statement. If the statement is not a COMMIT and occurs within a
> explicit transaction then you should rollback the transaction before
> continuing."
>
> From the same page:
>
> "SQLITE_ERROR means that a run-time error (such as a constraint
> violation) has occurred. sqlite3_step() should not be called again on
> the VM. More information may be found by calling sqlite3_errmsg().
> With the legacy interface, a more specific error code (for example,
> SQLITE_INTERRUPT, SQLITE_SCHEMA, SQLITE_CORRUPT, and so forth) can be
> obtained by calling sqlite3_reset() on the prepared statement. In the
> "v2" interface, the more specific error code is returned directly by
> sqlite3_step().
>
> SQLITE_MISUSE means that the this routine was called inappropriately.
> Perhaps it was called on a prepared statement that has already been
> finalized or on one that had previously returned SQLITE_ERROR or
> SQLITE_DONE."
>
>
> I believe you can put words "error code" in this text instead of
> "SQLITE_ERROR", its meaning will be the same - if you call
> sqlite3_step after an error again then SQLITE_MISUSE will be returned
> (although from my experience in some cases repeated sqlite3_step can
> work, but generally you better call sqlite3_reset).
>
>
> Pavel
>
> On Thu, May 13, 2010 at 8:13 AM, Black, Michael (IS)
> <michael.bla...@ngc.com> wrote:
>> Hmmm...bad assumption on my part....seems so logical that if it's busy just
>> try again -- and that idea worked on Liubin's problem. I see this
>> http://www.sqlite.org/c3ref/stmt.html -- but it doesn't quite address the
>> BUSY condition.
>>
>> I also found this thread
>> http://www.mail-archive.com/sqlite-users@sqlite.org/msg06916.html
>>
>> Which doesn't mention using reset (perhaps he is but just doesn't mention
>> it) but using busy_handler instead. This guy seems to have stressed it
>> pretty hard.
>>
>>
>>
>>
>>
>> Michael D. Black
>> Senior Scientist
>> Northrop Grumman Mission Systems
>>
>>
>> ________________________________
>>
>> From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov
>> Sent: Thu 5/13/2010 6:40 AM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] multi processes,so many errores of SQLITE_BUSY and
>> SQLITE_MISUSE
>>
>>
>>
>>> I assume you want the sqllite3_stmt to work -- so you need to loop that
>>> while it's busy.
>>
>> Michael, don't give bad advices.
>> The most general advice when one gets SQLITE_BUSY is to reset/finalize
>> all statements and rollback current transaction. It's only in certain
>> type of transactions and certain type of statements one can loop while
>> sqlite3_stmt returns SQLITE_BUSY. But even in this case one must call
>> sqlite3_reset on the statement that returned SQLITE_BUSY. And this
>> call to sqlite3_reset will return SQLITE_BUSY again.
>>
>>
>> Pavel
>>
>> On Thu, May 13, 2010 at 7:20 AM, Black, Michael (IS)
>> <michael.bla...@ngc.com> wrote:
>>> It also means you're not handling the SQLITE_BUSY from the sqlite3_stmt()
>>> before it.
>>> I assume you want the sqllite3_stmt to work -- so you need to loop that
>>> while it's busy.
>>>
>>> Michael D. Black
>>> Senior Scientist
>>> Northrop Grumman Mission Systems
>>>
>>>
>>> ________________________________
>>>
>>> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
>>> Sent: Thu 5/13/2010 2:07 AM
>>> To: sqlite-users@sqlite.org
>>> Subject: Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and
>>> SQLITE_MISUSE
>>>
>>>
>>>
>>>
>>> I see many SQLITE_BUSY returned from sqlite3_finalize(). Are they memory
>>> leak
>>> because it don't succeed in finalizing the sqlite3_stmt pointer?
>>>
>>>
>>>
>>>
>>> Black, Michael (IS) wrote:
>>>>
>>>> SQLITE_BUSY is not an error...just a fact.
>>>>
>>>> All your processes cannot work on the database at the same time...at least
>>>> not when one of them is doing an insert. You could be changing the table
>>>> while you're scanning it. EXXCLUSIVE doesn't change that idea.
>>>>
>>>> Somebody please correct me if I'm wrong on this one...
>>>> I think sqlite can work with multiple processes just doing read-onliy
>>>> operations (like SELECT). It's just the write operations
>>>> (INSERT/UPDATE) which will cause SQLITE_BUSY to occur.
>>>>
>>>> Michael D. Black
>>>> Senior Scientist
>>>> Northrop Grumman Mission Systems
>>>>
>>>>
>>>> ________________________________
>>>>
>>>> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
>>>> Sent: Tue 5/11/2010 9:20 PM
>>>> To: sqlite-users@sqlite.org
>>>> Subject: Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and
>>>> SQLITE_MISUSE
>>>>
>>>>
>>>>
>>>>
>>>> Thank you very much!
>>>>
>>>> It may be because my system's resource is limited. It's a embedded system
>>>> containing 32M RAM, ARM9 CPU.
>>>>
>>>> My "reiterating 20 times" is already using usleep().
>>>>
>>>> After I add the loop in the prepare statements, the system performance is
>>>> still very bad... And there are still many errores of SQLITE_BUSY.
>>>>
>>>> The only improvement is the disappear of the error of SQLITE_MISUSE.
>>>>
>>>> And I tried to use the "BEGIN EXCLUSIVE TRANSACTION". The things are same
>>>> with them without using it.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> Black, Michael (IS) wrote:
>>>>>
>>>>> Your "reiterating 20 times" is not using a usleep so you'll blow by this
>>>>> most every time it's busy.
>>>>>
>>>>> Do this instead in all your proc's
>>>>>
>>>>> ret = sqlite3_step (p_stmt);
>>>>> if (SQLITE_BUSY == ret)
>>>>> {
>>>>> int n=0;
>>>>> usleep(100000); // try one more time before error
>>>>> while ((ret=sqlite3_step(p_stmt))==SQLITE_BUSY) {
>>>>> printf("proc1 ret==BUSY %d\n",++n);
>>>>> usleep(100000);
>>>>> }
>>>>> }
>>>>>
>>>>> And you'll also need to handle "database is locked" coming from your
>>>>> prepare statements. I saw that error too.
>>>>> You'll need to loop there too.
>>>>>
>>>>> The more you drop the usleep time the more times it will show as busy.
>>>>> 1/10th or 1/100th of second is about all you want I would think.
>>>>>
>>>>> And get rid of the usleep at the bottom of each proc -- it's pretty
>>>>> useless at 100 microseconds. You don't need to sleep unless you're busy.
>>>>>
>>>>> I tested your code with this and got no errors at all -- just a bunch of
>>>>> BUSY messages.
>>>>>
>>>>>
>>>>> Not sure what your purpose is in sqlrun.c with looping and killing.
>>>>> Looks
>>>>> pretty squirrely to me. You're not waiting for the forks to finish so
>>>>> what is your logic here?
>>>>>
>>>>> Michael D. Black
>>>>> Senior Scientist
>>>>> Northrop Grumman Mission Systems
>>>>>
>>>>>
>>>>> ________________________________
>>>>>
>>>>> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
>>>>> Sent: Tue 5/11/2010 4:57 AM
>>>>> To: sqlite-users@sqlite.org
>>>>> Subject: [sqlite] multi processes, so many errores of SQLITE_BUSY and
>>>>> SQLITE_MISUSE
>>>>>
>>>>> ...
>>>>>
>>>>> --
>>>>> View this message in context:
>>>>> http://old.nabble.com/multi-processes%2C-so-many-errores-of-SQLITE_BUSY-and-SQLITE_MISUSE-tp28522127p28522127.html
>>>>> Sent from the SQLite mailing list archive at Nabble.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
>>>>>
>>>>>
>>>>
>>>> --
>>>> View this message in context:
>>>> http://old.nabble.com/multi-processes%2C-so-many-errores-of-SQLITE_BUSY-and-SQLITE_MISUSE-tp28522127p28531394.html
>>>> Sent from the SQLite mailing list archive at Nabble.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
>>>>
>>>>
>>>
>>> --
>>> View this message in context:
>>> http://old.nabble.com/multi-processes%2C-so-many-errores-of-SQLITE_BUSY-and-SQLITE_MISUSE-tp28522127p28544420.html
>>> Sent from the SQLite mailing list archive at Nabble.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
