sqlite事务模型、性能优化tips、常见误区

0，前言

本文主要介绍sqlite的事务模型，以及基于事务模型的一些性能优化tips，包括事务封装、WAL+读写分离、分库分表、page size优化等。并基于手淘sqlite的使用现状总结了部分常见问题及误区，主要集中在多线程的设置、多线程下性能优化的误区等。本文先提出以下几个问题（作者在进行统一存储的关系存储框架优化过程中一直困惑的问题，同时也是客户端开发者经常搞错的问题）并在正文中进行解答：

• 1，sqlite的多进程安全是怎么实现的？性能如何？
• 2，sqlite的数据库连接是什么？
• 3，言sqlite必提的读写分离，具体指什么？能不能提升数据读写的性能？为什么
• 4，sqlite提供的WAL特性解决了什么问题？
• 5，sqlite的多线程设置是为了解决什么问题？与读写分离有什么关系？
• 6，什么情况下数据库会发生死锁？
• 7，有哪些性能优化的方案？

1，sqlite主要数据结构

在深入了解sqlite之前，最好先对sqlite的主要数据结构有个概要的理解，sqlite是一个非常完备的关系数据库系统，由很多部分组成（parser,tokenize,virtual machine等等），同时sqlite的事务模型相对简化，是入门学习关系数据库方法论的一个不错的选择；下文对事务模型的分析也基于这些核心数据结构。下面这张图比较准确的描述了sqlite的几个核心数据结构：

1.1 Connection

connection通过sqlite3_open函数打开，代表一个独立的事务环境（这里及下文提到的事务，包括显式声明的事务，也包括隐式的事务，即每条独立的sql语句）

1.2 B-Tree

B-Tree负责请求pager从disk读取数据，然后把页面(page)加载到页面缓冲区(page cache)

1.3 Pager

Pager负责读写数据库，管理内存缓存和页面（即下文提到的page caches），以及管理事务，锁和崩溃恢复

2，sqlite事务模型及锁

2.1 sqlite多进程安全及Linux & windows文件锁

• 关于建议锁（advisory lock）和强制锁（mandatory lock）

  • 建议锁并不由内核强制实行，如果有进程不检查目标文件是否已经由别的进程加了锁就往其中写入数据，内核也不会加以阻拦。因此，建议锁并不能阻止进程对文件的访问，而是需要进程事先对锁的状态做一个约定，并根据锁的当前状态和相互关系来确定其他进程是否能对文件执行指定的操作
  • 强制锁是由内核强制采用的文件锁——由于内核对每个read()和write()操作都会检查相应的锁，会降低系统性能

• 典型的建议锁

  • 锁文件；锁文件是最简单的对文件加锁的方法，每个需要加锁的数据文件都有一个锁文件（lock file）。但这种方式存在比较大的问题是无法强制保护需要加锁的文件，并且当加锁进程非正常退出之后，会造成其他进程的死锁
  • 记录锁；System V和BSD4.3引入了记录锁，相应的系统调用为lockf()和flock()。而POSIX对于记录锁提供了另外一种机制，其系统调用为fcntl()。记录锁和锁文件有两个很重要的区别：1）记录锁可以对文件的任何一部分加锁，这对DBMS有极大的帮助，2）记录锁的另一个优点就是它由进程持有，而不是文件系统持有，当进程结束时，所有的锁也随之释放。对于一个进程本身而言，多个锁绝不会冲突。（Windows中的锁都是强制锁，具体不是很熟，只知道在由于windows上文锁的限制，sqlite多进程下的并发性会受影响）

2.1.1 结论

sqlite的文件锁在linux/posix上基于记录锁实现，也就是说sqlite在文件锁上会有以下几个特点：

• 多进程使用安全，且不会因为进程异常退出引发死锁
• 单进程使用性能几乎不会受损，多进程使用的性能损耗会受一定的影响

2.2 事务模型(Without WAL)

sqlite对每个连接设计了五钟锁的状态(UNLOCKED, PENDING, SHARED, RESERVED, EXCLUSIVE), sqlite的事务模型中通过锁的状态保证读写事务（包括显式的事务和隐式的事务）的一致性和读写安全。sqlite官方提供的事务生命周期如下图所示，我在这里稍微加了一些个人的理解：

这里有几点需要注意：

• UNLOCKED、PENDING、SHARED、RESERVED状态是非独占的，也就是说同一个连接中多个线程并发只读不会被阻塞。
• 写操作的数据修改会先写入page cache，内容包括journal日志、b-tree的修改等；正是由于page cache的存在，很多耗时的“重”操作都可以不干扰其他连接和当前连接的读操作，真正意义上保证了sqlite可以同时处理一个写连接和多个读连接。
• 连接由RESERVED状态进入EXCLUSIVE状态，需要等待读线程释放SHARED锁，也即写操作会被读操作阻塞
• 连接由RESERVED状态进入EXCLUSIVE状态后（显式或隐式的调用commit），数据库进入独占状态，其他任何连接都无法由UNLOCK状态进入SHARED状态；也即写操作会阻塞所有连接的读操作（不包括已经进入SHARED状态的操作），直到page caches写入数据库文件（成功或失败）。
• 数据库独占状态越久，其他操作的等待时间越久，即SQLITE_BUSY产生的一个原因

2.2.1 结论

• 对于常规的事务模型（without WAL），读写（连接）分离，不同连接或同一个连接上的读和写操作仍互相阻塞，对性能提升没有明显帮助
• 写事务在拿到reserve锁之前在page cache里的操作不会影响其他连接的读写，所以使用事务进行批量数据的更新操作有非常大的性能优势
• 事务模型存在死锁的场景，如下图所示：

2.3 WAL对事务模型的影响

按照官方文档，WAL的原理如下：

对数据库修改是是写入到WAL文件里的，这些写是可以并发的（WAL文件锁）。所以并不会阻塞其语句读原始的数据库文件。当WAL文件到达一定的量级时(CheckPoint)，自动把WAL文件的内容写入到数据库文件中。当一个连接尝试读数据库的时候，首先记录下来当前WAL文件的末尾 end mark，然后，先尝试在WAL文件里查找对应的Page，通过WAL-Index来对查找加速（放在共享内存里，.shm文件），如果找不到再查找数据库文件。

这里结合源码，有下面几个理解：

• 数据的写操作写入WAL的过程不再需要SHARED锁、EXCLUSIVE锁，而是需要WAL文件锁
• 数据的写操作不会被读操作阻塞（写操作不再需要SHARED锁）
• 数据的读操作不会被写操作阻塞（写操作不再需要独占数据库）
• WAL文件写入数据库文件的过程，依然会被读操作阻塞，也会阻塞读操作
• WAL文件的大小设置很关键，过大的WAL文件，会让查找操作从B-Tree查找退化成线性查找(WAL中page连续存储)；但大的WAL文件对写操作较友好。

2.3.1 结论

• 只有开了WAL，再使用读写（连接）分离才能有较大的性能提升
• WAL本质上是将部分随机写操作（数据库文件和journal日志）变成了串行写WAL文件，并进行了锁分离
• WAL文件的大小设置很关键，过大的WAL文件，会让查找操作从B-Tree查找退化成线性查找(WAL中page连续存储)；但大的WAL文件对写操作较友好

2.4 多线程设置

• 多线程是sqlite使用过程中比较容易误解的一个概念，带来的问题要么是产生各种线程安全问题，要么是无法充分发掘sqlite的性能，这里结合代码我们简单分析一下并给出几个重要结论。
• 线程安全设置主要在设置bCoreMutex和bFullMutex，启用bFullMutex之后数据库连接和prepared statement都已加锁（社区各种文档都到此为止）；但还是感觉不够清晰：这两个锁会对我们使用sqlite有怎样的影响？best practice又是什么？

// 多线程的设置的实现：设置bCoreMutex和bFullMutex

#if defined(SQLITE_THREADSAFE) && SQLITE_THREADSAFE>0  /* IMP: R-54466-46756 */
    case SQLITE_CONFIG_SINGLETHREAD: {
      /* EVIDENCE-OF: R-02748-19096 This option sets the threading mode to
      ** Single-thread. */
      sqlite3GlobalConfig.bCoreMutex = 0;  /* Disable mutex on core */
      sqlite3GlobalConfig.bFullMutex = 0;  /* Disable mutex on connections */
      break;
    }
#endif
#if defined(SQLITE_THREADSAFE) && SQLITE_THREADSAFE>0 /* IMP: R-20520-54086 */
    case SQLITE_CONFIG_MULTITHREAD: {
      /* EVIDENCE-OF: R-14374-42468 This option sets the threading mode to
      ** Multi-thread. */
      sqlite3GlobalConfig.bCoreMutex = 1;  /* Enable mutex on core */
      sqlite3GlobalConfig.bFullMutex = 0;  /* Disable mutex on connections */
      break;
    }
#endif
#if defined(SQLITE_THREADSAFE) && SQLITE_THREADSAFE>0 /* IMP: R-59593-21810 */
    case SQLITE_CONFIG_SERIALIZED: {
      /* EVIDENCE-OF: R-41220-51800 This option sets the threading mode to
      ** Serialized. */
      sqlite3GlobalConfig.bCoreMutex = 1;  /* Enable mutex on core */
      sqlite3GlobalConfig.bFullMutex = 1;  /* Enable mutex on connections */
      break;
    }
#endif

• 如果FullMutex打开，则每个数据库连接会初始化一个互斥量成员（db->mutex），也就是社区各种文档上所说的“bFullMutex是对连接的线程保护”

if( isThreadsafe ){    // bFullMutex = 1
    db->mutex = sqlite3MutexAlloc(SQLITE_MUTEX_RECURSIVE);    // 每个数据库连接会初始化一个成员锁
    if( db->mutex==0 ){
      sqlite3_free(db);
      db = 0;
      goto opendb_out;
    }
  }

• 如果CoreMutex打开，则会设置全局的锁控制函数

/* If the xMutexAlloc method has not been set, then the user did not
    ** install a mutex implementation via sqlite3_config() prior to 
    ** sqlite3_initialize() being called. This block copies pointers to
    ** the default implementation into the sqlite3GlobalConfig structure.
    */
    sqlite3_mutex_methods const *pFrom;
    sqlite3_mutex_methods *pTo = &sqlite3GlobalConfig.mutex;

    if( sqlite3GlobalConfig.bCoreMutex ){
      pFrom = sqlite3DefaultMutex();
    }else{
      pFrom = sqlite3NoopMutex();
    }
    pTo->xMutexInit = pFrom->xMutexInit;
    pTo->xMutexEnd = pFrom->xMutexEnd;
    pTo->xMutexFree = pFrom->xMutexFree;
    pTo->xMutexEnter = pFrom->xMutexEnter;
    pTo->xMutexTry = pFrom->xMutexTry;
    pTo->xMutexLeave = pFrom->xMutexLeave;
    pTo->xMutexHeld = pFrom->xMutexHeld;
    pTo->xMutexNotheld = pFrom->xMutexNotheld;
    sqlite3MemoryBarrier();
    pTo->xMutexAlloc = pFrom->xMutexAlloc;

• 而CoreMutext未打开的话，sqlite3NoopMutex()的实现如下（CoreMutext未打开的话，对应使用的锁函数均为空实现）：

sqlite3_mutex_methods const *sqlite3NoopMutex(void){
  static const sqlite3_mutex_methods sMutex = {
    noopMutexInit,
    noopMutexEnd,
    noopMutexAlloc,
    noopMutexFree,
    noopMutexEnter,
    noopMutexTry,
    noopMutexLeave,
    0,
    0,
  };

  return &sMutex;
}

// CoreMutext未打开的话，对应使用的锁函数均为空实现
static int noopMutexInit(void){ return SQLITE_OK; }
static int noopMutexEnd(void){ return SQLITE_OK; }
static sqlite3_mutex *noopMutexAlloc(int id){ 
  UNUSED_PARAMETER(id);
  return (sqlite3_mutex*)8; 
}
static void noopMutexFree(sqlite3_mutex *p){ UNUSED_PARAMETER(p); return; }
static void noopMutexEnter(sqlite3_mutex *p){ UNUSED_PARAMETER(p); return; }
static int noopMutexTry(sqlite3_mutex *p){
  UNUSED_PARAMETER(p);
  return SQLITE_OK;
}
static void noopMutexLeave(sqlite3_mutex *p){ UNUSED_PARAMETER(p); return; }

• FullMutex保护了什么？

粗略看了一下，通过db->mutex(sqlite3_mutex_enter(db->mutex);)保护的逻辑块和函数主要如下列表：

sqlite3_db_status、sqlite3_finalize、sqlite3_reset、sqlite3_step、sqlite3_exec、
sqlite3_preppare_v2、column_name、blob操作、sqlite3Close、sqlite3_errmsg...

基本覆盖了所有的读、写、DDL、DML，也包括prepared statement操作；也就是说，在未打开FullMutex的情况下，在一个连接上的所有DB操作必须严格串行执行，包括只读操作。

• CoreMutex保护了什么？

sqlite3中的mutex操作函数，除了用于操作db->mutex这个成员之外，还主要用于以下逻辑块（主要是影响数据库所有连接的逻辑）：

shm操作(index for wal)、内存池操作、内存缓存操作等

2.4.1 结论

• 多线程设置是决定DDL、DML、WAL(包括SHM)操作是否线程安全的设置
• 多线程设置与读写（连接）分离没有任何关系，并不是实现读写（连接）分离的必要条件（很多人对这一点有误解）

3，性能优化tips

3.1 合理使用事务

由#2.2的分析可知，写操作会在RESERVED状态下将数据更改、b-tree的更改、日志等写入page cache，并最终flush到数据库文件中；使用事务的话，只需要一次对DB文件的flush操作，同时也不会对其他连接的读写操作阻塞；对比以下两种数据写入方式(这里以统一存储提供的API为例)，实测耗时有十几倍的差距（当然对于频繁的读操作，使用事务可以减事务状态的切换，也会有一点点性能提升）：

// batch insert in transaction with 1000000 records
//
AliDBExecResult* execResult = NULL;
_database->InTransaction([&]() -> bool {    // in transaction
  auto statement = _database->PrepareStatement("INSERT INTO table VALUES(?, ?)");
  for (auto record : records) {    // bind 1000000 records
    // bind record
    ...
    ...
    statement->AddBatch();
  }
  auto result = statement->ExecuteUpdate();
  return result->is_success_;
});


// batch insert with 1000000 records, no transaction
//
auto statement = _database->PrepareStatement("INSERT INTO table VALUES(?, ?)");
for (auto record : records) {    // bind 1000000 records
  // bind record
  ...
  ...
  statement->ExecuteUpdate();
}

3.2 启用WAL + 读写(连接)分离

启用WAL之后，数据库大部分写操作变成了串行写（对WAL文件的串行操作），对写入性能提升有非常大的帮助；同时读写操作可以互相完全不阻塞（如#2.3所述）。上述两点比较好的解释了启用WAL带来的提升；同时推荐一个写连接 + 多个读连接的模型，如下图所示：

3.2.1 读写连接分离的细节

• 读操作使用不同的连接并发执行，可以完全避免由于显式事务、写操作之间的锁竞争带来的死锁

• 所有的写操作、显式事务操作都使用同一个连接，且所有的写操作、显式事务操作都串行执行

  • 可以完全避免由于显式事务、写操作之间的锁竞争带来的死锁，如#2.2.1提到的死锁的例子
  • 并发写并不能有效的提高写入效率，参考如下伪代码，哪段执行更快？

// two transactions: 
void Transaction_1() {
        connection_->Exec("BEGIN");
      connection_->Exec("insert into table(value) values('xxxx')");
      connection_->Exec("COMMIT");
}

void Transaction_2() {
        connection_->Exec("BEGIN");
      connection_->Exec("insert into table(value) values('xxxx')");
      connection_->Exec("COMMIT");
}

// code fragment 1: concurrent transaction
thread1.RunBlock([]() -> void {
      for (int i=0; i< 100000; i++) {
            Transaction_1();
    }
});

thread2.RunBlock([]() -> void {
      for (int i=0; i< 100000; i++) {
            Transaction_2();
    }
});

thread1.Join(); thread2.join();

// code fragment 2: serial transaction
for (int i=0; i< 100000; i++) {
  Transaction_1();
}
for (int i=0; i< 100000; i++) {
  Transaction_2();
}

3.3 针对具体业务场景，设置合适的WAL SIZE

如#2.3提到，过大的WAL文件，会让查找操作从B-Tree查找退化成线性查找(WAL中page连续存储)；但大的WAL文件对写操作较友好。对于大记录的写入操作，较大的wal size会有效提高写入效率，同时不会影响查询效率

3.4 针对业务场景分库分表

分库分表可以有效提高数据操作的并发度；但同时过多的表会影响数据库文件的加载速度。现在数据库方向的很多研究包括Auto sharding,  paxos consensus, 存储和计算的分离等；Auto
application-awared optimization，Auto hardware-awared optimization，machine
learning based optimization也是不错的方向。

3.5 其他

包括WAL checkpoint策略、WAL size优化、page size优化等，均需要根据具体的业务场景设置。

4，常见问题 & 误区

4.1 线程安全设置及误区

• sqlites configuration options: https://sqlite.org/c3ref/c_config_getmalloc.html

• 按照sqlite文档，sqlite线程安全模式有以下三种：

  • SQLITE_CONFIG_SINGLETHREAD（单线程模式）

    • This option sets the threading mode to Single-thread. In other words, it disables all mutexing and puts SQLite into a mode where it can only be used by a single thread.

  • SQLITE_CONFIG_MULTITHREAD（多线程模式）

    • This option sets the threading mode to Multi-thread. In other words, it disables mutexing on database connection and prepared statement objects. The application is responsible for serializing access to database connections and prepared statements. But other mutexes are enabled so that SQLite will be safe to use in a multi-threaded environment as long as no two threads attempt to use the same database connection at the same time.

  • SQLITE_CONFIG_SERIALIZED（串行模式）

    • This option sets the threading mode to Serialized. In other words, this option enables all mutexes including the recursive mutexes on database connection and prepared statement objects. In this mode (which is the default when SQLite is compiled with SQLITE_THREADSAFE=1) the SQLite library will itself serialize access to database connections and prepared statements so that the application is free to use the same database connection or the same prepared statement in different threads at the same time.

4.1.1 误区一：多线程模式是线程安全的

产生这个误区主的主要原因是官方文档里的最后一句话：

SQLite will be safe to use in a multi-threaded environment as long as no two threads attempt to use the same database connection at the same time.

但大家往往忽略了前面的一句话：

it disables mutexing on database connection and prepared statement objects

即对于单个连接的读、写操作，包括创建出来的prepared statement操作，都没有线程安全的保护。也即在多线程模式下，对单个连接的操作，仍需要在业务层进行锁保护。

4.1.2 误区二：多线程模式下，并发读操作是安全的

关于这一点，#2.4给出了具体的解释；多线程模式下(SQLITE_CONFIG_MULTITHREAD)对prepared statement、connection的操作都不是线程安全的

4.1.3 误区三：串行模式下，所有数据库操作都是串行执行

这个问题比较笼统；即使在串行模式下，所有的数据库操作仍需遵循事务模型；而事务模型已经将数据库操作的锁进行了非常细粒度的分离，串行模式的锁也是在上层保证了事务模型的完整性

4.1.4 误区四：多线程模式性能最好，串行模式性能差

多线程模式下，仍需要业务上层进行锁保护，串行模式则是在sqlite内部进行了锁保护；认为多线程模式性能好的兄弟哪来的自信认为业务层的锁实现比sqlite内部锁实现性能更高？

5，参考文献

1. sqlite configuration reference, https://sqlite.org/c3ref/c_config_getmalloc.html
2. 深入理解sqlite, https://www.kancloud.cn/kangdandan/sqlite