Vincent Chan 的巴士站 🚉
关于我
RSS
多线程 SQLite with C++ 踩坑汇总

因为工作的原因使用 SQLite 作为本地数据库,然而 SQLite 和 C++ 配合使用有许许多多的坑,此文用于记录工作过程中使用 SQLite 的坑。

Last Insert rowId

Last Insert Rowid

SQLite 中有一个函数可以获得最最近 insert 的一行的 id。也就是说,当你 insert 新的自增的一行时,你可以用 sqlite3_last_insert_rowid() 这个函数拿到这一行的 ID 这是一个取巧的办法,实际上这个函数是记录在 sqlite 实例里面的一个变量。这个变量的读写当然是没问题的,但是它需要和上一次 step 串行执行并上锁。

假设你有如下代码:

sqlite3_step(stmt);
std::int64_t id = sqlite3_last_insert_rowid(db);

你必须为这两句话上锁,因为如果这两句语句中间有其他线程插入了数据,你从 sqlite3_last_insert_rowid 拿到的是其他线程的数据。

sqlite3_step(stmt);
// 其他线程也执行了 sqlite_step() 导致下面语句 rowid 不对
std::int64_t id = sqlite3_last_insert_rowid(db);

正确写法

{
  std::lock_guard<std::mutex> guard(mt);
  sqlite3_step(stmt);
  std::int64_t id = sqlite3_last_insert_rowid(db);
}

附上 SQLite 获取 last_rowid 的源码:

/*
** Return the ROWID of the most recent insert
*/
sqlite_int64 sqlite3_last_insert_rowid(sqlite3 *db){
  return db->lastRowid;
}

事务

事务可以说是 SQLite 里面多线程使用最坑的东西了。SQLite 不像其他 C/S 数据库一样一条链接保持一个事务。它就是纯函数调用。

乱序提交

SQLite 本身可以保证 step 是线程安全的,也就是多个线程同时 step 是没问题的。但是会有乱序提交的问题。比如一个线程开启事务进行提交,另一个线程也同时进行提交,这个线程回滚导致了其他线程的提交也回滚了。

所以事务提交要上锁,主动上锁的话,一来性能下去了不说,二来忘了上锁就 gg,这个地方很坑。

多实例

既然 SQLite 是一个实例一个事务,那么每一个事务我开一个 SQLite 实例是不是就可以了呢。答案是依然有坑。

一个 DEFERRED 的事务(SQLite 事务默认是 DEFFERED)如果执行了一句写的语句,就会锁住整个数据库,直到 COMMIT,在写事务开始到结束之前,其他实例执行任何语句都会返回 SQLITE_BUSY(数据库繁忙)。不止 step 哦,是 prepare 也会哦,是不是很惊喜。如果你这个事务很长,其他实例也用不了数据库了(跟上锁也没啥区别)。

峰回路转,我终于在 SQLite 的 API 里面找到了一个 busy_handler,可以在 busy 的时候执行一个回调函数,让你去做一些操作,比如重试。刚好官网也实现了一个实现了一个重试的 busy handler,你只要设置好 busy_timeout 这个参数就可以了,这个默认的 handler 就会贴心地帮你重试。而它的做法就是过一会儿再帮你提交一遍,直到 timeout 为止,显而易见这种做法非常挫,这挫做法导致它比用户自己加锁更慢。

下面看一下 sqlite 的默认 busy handler 的源码:

/*
** This routine implements a busy callback that sleeps and tries
** again until a timeout value is reached.  The timeout value is
** an integer number of milliseconds passed in as the first
** argument.
*/
static int sqliteDefaultBusyCallback(
 void *ptr,               /* Database connection */
 int count                /* Number of times table has been busy */
){
#if SQLITE_OS_WIN || (defined(HAVE_USLEEP) && HAVE_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 (sizeof(delays)/sizeof(delays[0]))
  sqlite3 *db = (sqlite3 *)ptr;
  int timeout = 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 > timeout ){
    delay = timeout - prior;
    if( delay<=0 ) return 0;
  }
  sqlite3OsSleep(db->pVfs, delay*1000);
  return 1;
#else
  sqlite3 *db = (sqlite3 *)ptr;
  int timeout = ((sqlite3 *)ptr)->busyTimeout;
  if( (count+1)*1000 > timeout ){
    return 0;
  }
  sqlite3OsSleep(db->pVfs, 1000000);
  return 1;
#endif
}

这是一段退避重试的代码。可知重试时间是毫秒级的。这个延时速度真的还不如自己上锁呢……

正确写法

所以这里建议在 C++ 里面使用事务,还是使用单实例 + 使用的时候上锁来解决。另外要保证只有一个线程使用了事务,而且这个线程使用事务的时候,其他线程不能提交。

总结

SQLite 是一个很古老的数据库 lib。在业界也被广为使用。但是它的一些 api 设计真的有点坑。鉴于 SQLite 古老的历史,也许这些 api 不易修改得对多线程友好。所以在使用的时候还是要多多注意。写到这里,我想到了 sqlite 官方 FAQ 的一句话:https://www.sqlite.org/faq.html

Threads are evil. Avoid them.

同时还贴出了一篇论文:https://www2.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf

或许 SQLite 的作者真的不喜欢多线程,才设计成这个样子吧。