Re: [sqlite] Do I really need rollback after sqlite3_step returns BUSY?

[Jay Kreibich]

On Jan 7, 2014, at 4:13 AM, Woody Wu  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?

No.  You executed a statement.  Regardless of if it worked or failed, you need 
to reset to the statement with _reset() before trying to execute it again.

 -j

--  
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I really need rollback after sqlite3_step returns BUSY?

[Yuriy Kaminskiy]

Woody Wu wrote:
> Hi, Simon
> 
> On 7 January 2014 19:32, Simon Slavin  wrote:
> 
>> On 7 Jan 2014, at 10:13am, Woody Wu  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 Athread 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

Re: [sqlite] Do I really need rollback after sqlite3_step returns BUSY?

[Simon Slavin]

On 7 Jan 2014, at 2:26pm, Woody Wu  wrote:

> 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?

Yes, if you do have a situation where a process can keep a database locked for 
3 seconds, then it is normal for another to get SQLITE_BUSY or 
SQLITE_IOERR_BLOCKED even if you've set a _timeout().

So it's usual to set a far longer timeout period -- whatever time you would 
really want your software to try before giving up and quitting.  You might set 
it to 30 seconds or even five minutes.  Getting _BUSY should indicate that you 
have serious hardware problems, or that one of your applications has crashed 
and needs attention.

There are other uses for shorter timeouts, especially in cases where you are 
doing clever multi-process multi-tasking, but usually in a simple database 
application on a proper computer you don't need that.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I really need rollback after sqlite3_step returns BUSY?

[Woody Wu]

Hi, Simon

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?




On 7 January 2014 19:32, Simon Slavin  wrote:

>
> On 7 Jan 2014, at 10:13am, Woody Wu  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
> 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
> it either worked or it didn't.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Life is the only flaw in an otherwise perfect nonexistence
-- Schopenhauer

narke
public key at http://subkeys.pgp.net:11371 (narkewo...@gmail.com)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I really need rollback after sqlite3_step returns BUSY?

[Simon Slavin]

On 7 Jan 2014, at 10:13am, Woody Wu  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 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 it either worked or 
it didn't.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I really need rollback after sqlite3_step returns BUSY?

[Woody Wu]

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?


On Tuesday, 7 January 2014, Simon Slavin wrote:

>
> On 7 Jan 2014, at 8:50am, Woody Wu >
> wrote:
>
> > Why I cannot just
> > sleep for a while
> > and redo the sqlite3_step()?
>
> If you want to sleep for a while then try _step() again you can get the
> same result without any programming.  Simply set sqlite3_busy_timeout() to
> the time you want to sleep:
>
> 
>
> This handles the initial attempt, the error handling, the sleep time, and
> the retry, all without any programming on your part.
>
> Once you have a correct timeout set, then you should treat SQLITE_BUSY the
> same way as any other unplanned unrecoverable result code.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Life is the only flaw in an otherwise perfect nonexistence
-- Schopenhauer

narke
public key at http://subkeys.pgp.net:11371 (narkewo...@gmail.com)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I really need rollback after sqlite3_step returns BUSY?

[Simon Slavin]

On 7 Jan 2014, at 8:50am, Woody Wu  wrote:

> Why I cannot just
> sleep for a while
> and redo the sqlite3_step()?

If you want to sleep for a while then try _step() again you can get the same 
result without any programming.  Simply set sqlite3_busy_timeout() to the time 
you want to sleep:



This handles the initial attempt, the error handling, the sleep time, and the 
retry, all without any programming on your part.

Once you have a correct timeout set, then you should treat SQLITE_BUSY the same 
way as any other unplanned unrecoverable result code.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I really need rollback after sqlite3_step returns BUSY?

[Clemens Ladisch]

Woody Wu wrote:
> The manual says that it should to rollback after sqlite3_step() returns 
> SQLITE_BUSY as long as
> the current statment is in a transaction. Is this true?

Yes.  If you have a transaction, you must _eventually_ commit or rollback.

> Why I cannot just sleep for a while and redo the sqlite3_step()?

In theory, you could do this, but sqlite3_step() can fail again.

Anyway, sleeping is better done in a callback registered with
sqlite3_busy_handler().

If you have such a callback, the only cases where you can get SQLITE_BUSY
is when your callback decides that sleeping does not make sense, or when
there is a deadlock.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users