Re: [sqlite] sqlite3_step and SQLITE_LOCKED/BUSY

[Simon Slavin]

On 5 Sep 2014, at 1:58pm, Jan Slodicka  wrote:

> 1) SQLITE_OK should not happen. (Official documentation allows /ANY/ result
> code. I hope this not true.)
> 2) If it happens, then this looks dangerous. 

You are correct.  If you use this routine only for SELECT commands or PRAGMAs 
which return results, SQLITE_OK should never happen.  You might get SQLITE_OK 
from a command that makes a change (e.g. INSERT, or a PRAGMA which changes 
things).

> I don't like a lot of things about the C# wrapper (heavy overhead,
> SQL_SCHEMA dealing, timeouts...). But those points are not crucial. What I
> ask is following:
> 
> a) If sqlite3_step() returns error code different from 100/101, should we
> call sqlite3_reset()? This looks like a bug to me. (Imagine we are reading a
> table.)

The statement is still valid, it just can't be used right now.  So you might 
want to use it later.  However, the only reason for wanting to call 
sqlite3_reset() is if you might want to execute the same command later.  Since 
this is a wrapper it seems unlikely that you would want to do this in your 
situation.

Instead, it is more likely that you would want to call sqlite3_finalize().  You 
would do this to tell SQLite that you are finished with the statement and want 
to release its memory and other resources.

> b) If we get Locked/Busy errors, is it safe and reasonable to retry
> sqlite3_step? Note that if we solve this way some multi-access conflicts,
> then this is a welcome feature.

It is probably not appropriate to retry sqlite3_step().  If the database was 
locked past whatever timeout you set, it will still be locked.  If it was busy 
it will still be busy.  The normal thing to do if you very Locked/Busy is to 
issue some kind of error message to the user.  In your situation you'd probably 
have that routine do something similar to what it would do for a badly 
formatted command.

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

Re: [sqlite] sqlite3_step and SQLITE_LOCKED/BUSY

[Jan Slodicka]

Thanks, Simon,

perhaps I did not formulate may question clearly enough. The class of course
works as you wrote, the only problem relates to the error handling.

If you are interested, the C# wrapper (original code) is  here

 
. (UnsafeNativeMethods class contains a collection of function pointers to
sqlite3.dll.)

This is the part of the code I want to discuss (a shortened version):


internal override bool Step(SQLiteStatement stmt)
{
  Random rnd = new Random();
  uint starttick = (uint)Environment.TickCount;
  uint timeout = (uint)(stmt._command._commandTimeout * 1000);

  while (true)
  {
SQLiteErrorCode n =
UnsafeNativeMethods.sqlite3_step(stmt._sqlite_stmt);
if (n == SQLiteErrorCode.Row) return true;
if (n == SQLiteErrorCode.Done) return false;

if (n != SQLiteErrorCode.Ok)   //*) 
{
  SQLiteErrorCode r = Reset(stmt);
  if (r == SQLiteErrorCode.Ok)
throw new SQLiteException(n, GetLastError());

  // Only Locked/Busy err code can pass here
  if ((uint)Environment.TickCount - starttick > timeout)
  throw new SQLiteException(r, GetLastError());
  System.Threading.Thread.Sleep(rnd.Next(1, 150));
}
  }
}

internal override SQLiteErrorCode Reset(SQLiteStatement stmt)
{
  SQLiteErrorCode n =
UnsafeNativeMethods.sqlite3_reset(stmt._sqlite_stmt);
  if (n == SQLiteErrorCode.Locked || n == SQLiteErrorCode.Busy || n ==
SQLiteErrorCode.Ok)
return n;
  throw new SQLiteException(n, GetLastError());
}


*) STRANGE:
1) SQLITE_OK should not happen. (Official documentation allows /ANY/ result
code. I hope this not true.)
2) If it happens, then this looks dangerous. 

I don't like a lot of things about the C# wrapper (heavy overhead,
SQL_SCHEMA dealing, timeouts...). But those points are not crucial. What I
ask is following:

a) If sqlite3_step() returns error code different from 100/101, should we
call sqlite3_reset()? This looks like a bug to me. (Imagine we are reading a
table.)

b) If we get Locked/Busy errors, is it safe and reasonable to retry
sqlite3_step? Note that if we solve this way some multi-access conflicts,
then this is a welcome feature.

Meanwhile I collected more info about the problem.

First, a reduced version of the sqlite3_step() official documentation that
applies to these conditions:
- Sqlite 3.7.15, SQLITE_OMIT_AUTORESET was not defined
- The statement was prepared using sqlite3_prepare_v2()
/
sqlite3_step returns SQLITE_BUSY, SQLITE_DONE, SQLITE_ROW, SQLITE_ERROR,
SQLITE_MISUSE, or any other result code
SQLITE_ROW/SQLITE_DONE... (description omitted)
SQLITE_BUSY means a failure to acquire a DB lock. If the statement is a
COMMIT or occurs outside of an explicit transaction, then you can retry the
statement. Otherwise you should rollback.
SQLITE_ERROR means a run-time error. sqlite3_step() should not be called,
sqlite3_errmsg() provides additional info.
SQLITE_MISUSE means that this routine was called inappropriately, such as on
a finalized statement. 
If sqlite3_step() returns anything other than SQLITE_ROW, then the next call
to sqlite3_step() automatically calls sqlite3_reset().
/

Another SQLite document  says   
"/SQLITE_LOCKED error may only occur on the first call to sqlite3_step()/".
And  Mr. Hipp

  
seems to have confirmed the same for SQLITE_BUSY. (Although 10 years ago)

Dan Kennedy  says

  
"/After SQLITE_BUSY is returned, you can call sqlite3_step() again
immediately/". (Note that this is not the same as the official documentation
says.)

Knowing now above info, I don't see the C# wrapper code that bad as
yesterday, though I am still far from being sure that it is 100% ok.




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/sqlite3-step-and-SQLITE-LOCKED-BUSY-tp77690p77707.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

Re: [sqlite] sqlite3_step and SQLITE_LOCKED/BUSY

[Simon Slavin]

> On 4 Sep 2014, at 4:21pm, Jan Slodicka  wrote:
> 
> Our C# wrapper (inspired by system.data.sqlite) uses this quasi-code:
> 
> bool Step(stmt)
> {
>  while(true)
>  {
>start time measurement
>rc = sqlite3_step(stmt);
>if( rc == SQLITE_ROW) return true;// ... reading column values
> possible
>if( rc == SQLITE_DONE) return false;  // ... done
> 
>rc = sqlite3_reset(stmt);// needed? looks strange to me
> 
>if( rc!=SQLITE_LOCKED && rc!=SQLITE_BUSY)
>throw an exception (message taken from sqlite3_errmsg())
>else {
>if timeout expired => throw a timeout exception
>else sleep a bit
>}
>  }
> }

Bad code.  No cookie.  A better structure would be

int stepResult = sqlite3_step(statement);
while (stepResult == SQLITE_ROW) {
// we received a legit row of data
// so handle it here then once it has been handled ...

stepResult = sqlite3_step(statement);
}
sqlite3_finalize(statement);
// now stepResult contains the reason there was no more data.
// if stepResult == SQLITE_DONE then we just finished receiving legit
// results and can carry on with the program.  But ...

if (stepResult != SQLITE_DONE) {
// handle other result codes here and halt/cancel/error
}

You don't need to sleep for timeouts yourself.  Just use this call once, after 
you've opened your connection, to set a timeout of a minute or so ...



and SQLite will handle timeouts and sleeping and exponential backoff itself 
without you having to write your own code to do it.  If the database is still 
locked after the amount of timeout you specified, /then/ you get SQLITE_BUSY.

> If I understand sqlite3_reset correctly (i.e. when it is called after some
> table rows are read, then it returns to the first row), then this code looks
> buggy.

A statement still exists until it has been sqlite3_reset() or 
sqlite3_finalized() and you should act as if it's taking up memory, pointers, 
or some other annoying stuff.  You reset it if you think you're going to start 
it again later, which will restart a SELECT from the first row again.  You 
finalize it if you're done with it and don't intend to execute it again later.  
Both of these calls release resources and set status so that when you 
eventually close the connection to the database SQLite knows you've tidied up 
and can release everything associated with it.

Theoretically you should not execute things like sqlite3_finalize() or 
sqlite3_close() without checking the values they returned to make sure they're 
SQLITE_OK.  I ommitted this check from my above example to make it more 
understandable.

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

[sqlite] sqlite3_step and SQLITE_LOCKED/BUSY

[Jan Slodicka]

I don't have any real problem this time, just wondered if our C# wrapper is
programmed correctly/efficiently.

Suppose we prepared a statement using sqlite3_prepare_v2(). Could please
anybody explain how to treat above mentioned error codes returned by
sqlite3_step()?

Our C# wrapper (inspired by system.data.sqlite) uses this quasi-code:

bool Step(stmt)
{
  while(true)
  {
start time measurement
rc = sqlite3_step(stmt);
if( rc == SQLITE_ROW) return true;// ... reading column values
possible
if( rc == SQLITE_DONE) return false;  // ... done

rc = sqlite3_reset(stmt);// needed? looks strange to me

if( rc!=SQLITE_LOCKED && rc!=SQLITE_BUSY)
throw an exception (message taken from sqlite3_errmsg())
else {
if timeout expired => throw a timeout exception
else sleep a bit
}
  }
}

If I understand sqlite3_reset correctly (i.e. when it is called after some
table rows are read, then it returns to the first row), then this code looks
buggy.

My web research yielded only unclear (sometimes even contradictory) results,
even from guys who are supposed to understand SQLite internals.

Could please somebody shed more light onto this problem?




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/sqlite3-step-and-SQLITE-LOCKED-BUSY-tp77690.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