Re: [sqlite] Busy management

[John Stanton]

It depends upon your application.  What are you trying to do?

Ludovic Ferrandis wrote:

Thanks for your answers,

but that still don't explain me which method is the best to manage Busy
statement, to do a loop or to use a busy callback. And as I'm very curious,
especially why one method is better than the other. :)

Thanks,

Ludovic

On 3/5/06, John Stanton <[EMAIL PROTECTED]> wrote:

I have a several of suggestions for that busy logic.
1.  Perform a "yield()" or Win sleep(0) to give up the current
    timeslice and thus have a busy wait with as little system impact
    as possible but fastest turnaround.  An upper limit of 100 probes
    before failing as deadlocked seems to be reasonable with that
    approach.
2.  Launch a "reset" call to restart the sqlite3_step on finding
    a BUSY to make sure that deadlock situations are broken by
    brute force, with all but one process yielding control.
3.  If the contention comes from competing threads within a process
    or competing processes in an application, define a semaphore or
    similar appropriate lock to synchronize each database and avoid the
    possibility of ever having a BUSY.  In windows it looks like this
             sqlite3_prep
             sqlite3_bind
             WaitOnSingleEvent
               sqlite3_step
               ....
             SetEvent
    This is a lower overhead, higher performance and generally more
    elegant approach than polling.  It does however assume that there
    are co-operating threads and processes.
JS

Jay Sprenkle wrote:

On 3/3/06, Ludovic Ferrandis <[EMAIL PROTECTED]> wrote:

I want to manage the SQLITE_BUSY error like this: If it fails, sleep X
ms then try the command Y times. I found 2 ways to do it:

I do it using sqlite3_step(); Using bound variables and step eliminates

the need

for escaping string data and prevents SQL injection attacks. I retry
the statement
up to 10 times in case another process has locked the database.
Psuedo code looks like this:

   // open database
   dbOpen();

   // Get configuration information for this website instance
   string sql = "SELECT blah"
                " FROM Setup"
                ;

   // prepare statement instead of building it to avoid sql injection

attacks

   if ( ! dbPrep( sql ) )
     throw ConException( string("Cannot prepare sql: ") + sql +
string(", ") +  + sqlite3_errmsg(db) );

   bool loop = true;
   for ( int i = 0; ( i < 10 ) && ( loop ); i++ )
     switch ( dbStep() )
       {
         // if database busy wait for a short time
         // to see if it becomes available
         case SQLITE_BUSY:
         case SQLITE_LOCKED:
           break;
         case SQLITE_ROW:
           // get results ( 0 based index!!! )
           blah  = dbColumn( 0 );
           break;
         case SQLITE_DONE:
           if ( CookieUser.empty() )
             throw ConException( string("Invalid configuration") );
           loop = false;
           break;
         default:
           throw ConException( string("Cannot execute sql: ") + sql );
           break;
       }

   // clean up when finished
   dbFinalize();
   dbClose();