SQLite Busy Handler

SQLite doesn ' t support high concurrency. In case of a lot of concurrent access from multi-process or multi-thread, calling a SQLite r/w function are prone to hit th e case, the database is locked by another process or thread. The default behavior is, the SQLite r/w function return sqlite_busy immediately, and the caller should retry Later.

Below is an example function. When the SQLite function returns SQLITE_BUSY, the caller sleep one second then retry. The sleep is required to optimize the CPU utilization, without the sleep, the CPU would be occupied by a lot of retries.

bool Execute(const string& sql) {
  char* errmsg = NULL;
  while (true) {
    int status = sqlite3_exec(db_, sql.c_str(), NULL, NULL, &errmsg);
    if (status == SQLITE_OK) {
      return true;
    } else if (status == SQLITE_BUSY) {
      ++total_busycount;
      cerr << "Thread " << threadindex_ << ": failed to execute " << sql << " (" << errmsg << ")\n";
      sqlite3_free(errmsg);
      sleep(1);
    } else {
      cerr << "Thread " << threadindex_ << ": failed to execute " << sql << " (" << errmsg << ")\n";
      sqlite3_free(errmsg);
      return false;
    }
  }
  return false;
}

In fact, the caller doesn ' t need to sleep explicitly, Calling sqlite3_busy_timeout before SQLite r/w function s'll make SQLite automaticlly insert sleep between the sequential calls of a r/w function. It's safe to set a big timeout value. SQLite splits the big timeout value into many small timeout values. With the optimization, if the lock was available while the caller was waiting, the caller won ' t need to wait to timeout. See below code,

1452  static int sqliteDefaultBusyCallback(
1453   void *ptr,               /* Database connection */
1454   int count                /* Number of times table has been busy */
1455  ){
1456  #if SQLITE_OS_WIN || HAVE_USLEEP
1457    static const u8 delays[] =
1458       { 1, 2, 5, 10, 15, 20, 25, 25,  25,  50,  50, 100 };
1459    static const u8 totals[] =
1460       { 0, 1, 3,  8, 18, 33, 53, 78, 103, 128, 178, 228 };
1461  # define NDELAY ArraySize(delays)
1462    sqlite3 *db = (sqlite3 *)ptr;
1463    int timeout = db->busyTimeout;
1464    int delay, prior;
1465
1466    assert( count>=0 );
1467    if( count < NDELAY ){
1468      delay = delays[count];
1469      prior = totals[count];
1470    }else{
1471      delay = delays[NDELAY-1];
1472      prior = totals[NDELAY-1] + delay*(count-(NDELAY-1));
1473    }
1474    if( prior + delay > timeout ){
1475      delay = timeout - prior;
1476      if( delay<=0 ) return 0;
1477    }
1478    sqlite3OsSleep(db->pVfs, delay*1000);
1479    return 1;
1480  #else
1481    sqlite3 *db = (sqlite3 *)ptr;
1482    int timeout = ((sqlite3 *)ptr)->busyTimeout;
1483    if( (count+1)*1000 > timeout ){
1484      return 0;
1485    }
1486    sqlite3OsSleep(db->pVfs, 1000000);
1487    return 1;
1488  #endif
1489  }

Another alternative is set a busy hander by calling Sqlite3_busy_handler. If the busy callback returns 0, then no additional attempts be made to access the database and Sqlite_busy was returned to The application. If the callback returns Non-zero, then another attempt are made to access the database and the cycle repeats.

SQLite Busy Handler