SQLite Busy Handler

Source: Internet
Author: User
Tags sqlite



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


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.