Woody Wu wrote:
> Hi, Simon
>
> On 7 January 2014 19:32, Simon Slavin <slav...@bigfraud.org> wrote:
>
>> On 7 Jan 2014, at 10:13am, Woody Wu <narkewo...@gmail.com> wrote:
>>
>>> Thanks for the clear guide. _busy_timeout is easier to use. By the
>>> way, i want confirm that if i am not in an explicit transaction, i can
>>> simply
>>> redo the _step() invoking, right?
>> However, if you are using _timeout() properly, you should never need to
(That's incorrect. _busy_timeout() can simplify program logic, but *it is not
sufficient* by itself. See below.)
> What means "using _timeout()" properly? The manual says, _timeout() can
> still make _step() returns SQLITE_BUSY or SQLITE_IOERR_BLOCKED if the user
> provided timeout value eventually accumulated. For example, if I set
> _timeout() to 3000ms, but after that time, the table I was trying to update
> still locked by another process. This is normal, right?
sqlite can return BUSY immediately if it detects dead-lock condition (e.g. two
connections issues BEGIN; SELECT ...; UPDATE ...; in parallel).
thread A thread B
BEGIN; BEGIN;
SELECT; /* acquires SHARED lock*/
SELECT; /* also acquires SHARED lock */
/* (two SHARED locks can coexist!) */
UPDATE; /* upgrades SHARED lock to RESERVED lock */
/* (one RESERVED lock can coexist with SHARED lock) */
UPDATE; /* tries to upgrade to RESERVED lock and
*fails*;
returns SQLITE_BUSY [*immediately*]*/
/* (only one connection can own RESERVED lock!) */
Note that there are no sense to wait and retry in thread B here, it won't fix
deadlock!
Moreover, if you keep transaction in thread B and thread A will issue COMMIT, it
will fail *too* (after waiting for _busy_timeout), as COMMIT requires EXCLUSIVE
lock, and it cannot be acquired before thread B will release its SHARED lock:
=== If you keep transaction in thread B: ===
thread A: thread B:
/* still have SHARED lock */
COMMIT; /* tries to upgrade lock to EXCLUSIVE and *fails* [after waiting for
busy_timeout] with SQLITE_BUSY too! (EXCLUSIVE lock cannot coexist with any
other lock) */
===
So, you should either properly handle that (ROLLBACK and retry transaction from
beginning in thread B), or use BEGIN IMMEDIATE with all transaction that *may*
modify database (at some point) [then BEGIN IMMEDIATE acquires RESERVED lock,
and so thread B will wait for _busy_timeout [or return SQLITE_BUSY] before
transaction start).
Note: above scenario apply to ROLLBACK-type journal, WAL is somewhat different
(but, IIRC, above scenario still *can* happen, with slight alterations).
>> recover to an understood situation after a SQLITE_BUSY, since a _BUSY
>> result will always indicate an unrecoverable error. Therefore you will
>> never need to figure out whether you should be doing a ROLLBACK. Just
>> _finalize() the operation (then probably _close() and quit the program) and
IMO, _finalize is somewhat overkill (unless you are planning to return error to
user terminate program right after that).
You only need to _reset() all affected (incomplete) statements.
>> it either worked or it didn't.
P.S.
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
