SQLite Forum

What is the poll frequency of `sqlite3_busy_timeout`?
Login

What is the poll frequency of `sqlite3_busy_timeout`?

(1) By example-user on 2020-10-04 18:40:44 [link] [source]

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

(2) By Keith Medcalf (kmedcalf) on 2020-10-04 19:34:23 in reply to 1 [link] [source]

The default busy handler callback is defined in main.c line 1646.

For systems supporting (and compiled) with support for sub-second sleeping (aka usleep and Windows) the times for the first polls are:

{ 1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100 } ms

and 100 ms each thereafter. If the platform or compiler does not support (or has not indicated support) for sub-second sleep, then the frequency is once per second or whatever increment the platform supports (that is, if the platform only supports sleeping for a day-at-a-time then the polling frequency will be once per day).

(3.5) By Keith Medcalf (kmedcalf) on 2020-10-04 19:51:49 edited from 3.4 in reply to 2 [link] [source]

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.

(4) By example-user on 2020-10-04 19:42:25 in reply to 2 [source]

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?

(5) By Keith Medcalf (kmedcalf) on 2020-10-04 21:46:54 in reply to 4 [link] [source]

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);

(7) By example-user on 2020-10-04 23:18:15 in reply to 5 [link] [source]

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.

(8) By Rowan Worth (sqweek) on 2020-10-05 03:47:28 in reply to 5 [link] [source]

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.

(9) By example-user on 2020-10-05 14:37:01 in reply to 8 [link] [source]

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.

(6.2) By Keith Medcalf (kmedcalf) on 2020-10-04 22:22:07 edited from 6.1 in reply to 4 [link] [source]

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.