As far as I understand sqlite3_busy_timeout, SQLite will poll up to the time provided to the argument.

https://www.sqlite.org/c3ref/busy_timeout.html

How does SQLite know when the DB file has been unlocked, and how often does it poll to get this status?

Thanks

Note that the Operating System is entirely responsible for determining the granularity of the sleep operation no matter what is requested. For example, Windows systems always awaken on a "tick" (which can be adjusted) but by default (on most hardware) is just shy of 16 ms.

So on Windows the "sleep" will be until the next "tick" that occurs after the requested interval is expired.

Thanks.

Does the sqlite3_busy_handler callback function get invoked after each of those intervals, or is the intention to let the application sleep inside the callback function then return true to define its own intervals?

The callback function is set using the sqlite3_busy_handler API as described here https://sqlite.org/c3ref/busy_handler.html

The callback function takes two parameters -- a void* and an int -- the int being the number of times it has been called.

busy = True;
times = 0;
while (busy && callbackFunction(void*, times))
{
  busy = amIstillBusy?
  times++
}
if (busy) throw SQLITE_BUSY error;
... carry on ...

So, the callback function is invoked as long as SQLite3 is still "busy" and uses the void* to retrieve the total timeout set for the connection and the "times" as an index into the array of delays (capped at the last element). It then uses the "times" to compute how long it has been waiting (more or less accurate depending on the OS, the hardware, the phase of the moon, and the existence of werewolves). If it determines that the timeout has not yet expired, it "goes to sleep" for whatever it decides is the appropriate interval, and then wakes up and returns True. If it determines that it has been called and the timeout total time has expired, it immediately returns False.

The callback function merely implements the "wait". The polling for whatever is causing the "busy" to clear is done by whatever thing decided that a "busyness" occurred.

The default busy handler is:

static int sqliteDefaultBusyCallback(
  void *ptr,               /* Database connection */
  int count                /* Number of times table has been busy */
){
#if SQLITE_OS_WIN || HAVE_USLEEP
  /* This case is for systems that have support for sleeping for fractions of
  ** a second.  Examples:  All windows systems, unix systems with usleep() */
  static const u8 delays[] =
     { 1, 2, 5, 10, 15, 20, 25, 25,  25,  50,  50, 100 };
  static const u8 totals[] =
     { 0, 1, 3,  8, 18, 33, 53, 78, 103, 128, 178, 228 };
# define NDELAY ArraySize(delays)
  sqlite3 *db = (sqlite3 *)ptr;
  int tmout = db->busyTimeout;
  int delay, prior;

  assert( count>=0 );
  if( count < NDELAY ){
    delay = delays[count];
    prior = totals[count];
  }else{
    delay = delays[NDELAY-1];
    prior = totals[NDELAY-1] + delay*(count-(NDELAY-1));
  }
  if( prior + delay > tmout ){
    delay = tmout - prior;
    if( delay<=0 ) return 0;
  }
  sqlite3OsSleep(db->pVfs, delay*1000);
  return 1;
#else
  /* This case for unix systems that lack usleep() support.  Sleeping
  ** must be done in increments of whole seconds */
  sqlite3 *db = (sqlite3 *)ptr;
  int tmout = ((sqlite3 *)ptr)->busyTimeout;
  if( (count+1)*1000 > tmout ){
    return 0;
  }
  sqlite3OsSleep(db->pVfs, 1000000);
  return 1;
#endif
}

and it is set on a connection using the equivalent of the following code:

 sqlite3* db = sqlite3_open ....
 sqlite3_busy_timeout(db, <value>);
 sqlite3_busy_handler(db, sqliteDefaultBusyCallback, db);

So you could write your own busy handler that causes a "poll" to occur every 32 ms for some specified time in seconds as follows:

int myBusyHandler(void* ptr, int times)
{
   if ((intptr_t)ptr < times * 0.032)
      return 0;
   usleep(32000);
   return 1;
}

and activate it with the following code:

db = sqlite3_open ...;
sqlite3_busy_handler(db, myBusyHandler, (void*)300);

Note that there is no API to retrieve the connection timeout in the API so for your own busy handler you cannot access the busy_timeout set for the connection.

Thanks for the explanation,

In your example you are calling both:

1. sqlite3_busy_timeout(db, val)
2. sqlite3_busy_handler(db, cbFn, db)

But for each db connection, you can only use one of these? The second call will clear the first's behaviour?

So to summarise:

• sqlite3_busy_timeout

  • Will block app code, poll the db file until either (lock is achieved OR timeout expires)

• sqlite3_busy_handler

  • Completely application defined polling - SQLite itself will not sleep or poll.
    • Return true = SQLite will immediately try to get a lock.
    • Return false = SQLite returns BUSY for the statement.
    • On sleep = SQLite is waiting for a return value.

sqlite3* db = sqlite3_open ....
sqlite3_busy_timeout(db, <value>);
sqlite3_busy_handler(db, sqliteDefaultBusyCallback, db);

Note that the call to sqlite3_busy_handler in this recipe is redundant -- sqlite3_busy_timeout is implemented via a call to busy_handler:

SQLITE_API int sqlite3_busy_timeout(sqlite3 *db, int ms){
  if( ms>0 ){
    sqlite3_busy_handler(db, sqliteDefaultBusyCallback, (void*)db);
    db->busyTimeout = ms;
  }else{
    sqlite3_busy_handler(db, 0, 0);
  }
  return SQLITE_OK;
}

To try and clarify the OP's understanding - sqlite invokes the busy callback as soon as a lock cannot be obtained, and it's up to the callback whether to sleep, update a GUI, abort immediately, etc etc. The busy_timeout mechanism is just there to provide a convenient default callback - if an app provides its own callback then the busy timeout is irrelevant.

I see, that makes sense.

The default callback using int tmout = ((sqlite3 *)ptr)->busyTimeout is private SQLite state that would not be used by the application.