文件数据库sqlite3 C++ 线程安全和并发

事务是 SQLite 的核心概念。对数据库的操作 (绝大部分) 会被打包成一个事务进行提交，需要注意的是，这里的打包成事务是自动开启的。举例而言，如果简单在一个 for 循环语句里向数据库中插入 10 条数据，意味着将自动生成 10 个事务。但需要注意的是事务是非常耗时的，一般而言， SQLite 每秒能够轻松支持 50000 条的数据插入，但是每秒仅能够支持几十个事务。一般而言，事务速度受限于磁盘速度。所以在批量插入时需要考虑禁用自动提交，将其用 BEGIN ... COMMIT 打包成一个事务。

回滚模式和 WAL

为了保证写入正确，SQLite 在使用事务进行数据库改写时将拷贝当前数据库文件的备份，即 rollback journal，当事务失败或者发生意外需要回滚时则将备份文件内容还原到数据库中，并同时删除该日志。这是默认的 DELETE 模式。

而后 SQLite 也引入了 WAL 模式，即 Write-Ahead Log。在这种模式下，所有的修改会写入一个单独的 WAL 文件内。这种模式下，写操作甚至可以不去操作数据库，这使得所有的读操作可以在 "写的同时" 直接对数据库文件进行操作，得到更好的并发性能。

锁和并发

SQLite 通过五种锁状态来完成事务。

UNLOCKED ，无锁状态。数据库文件没有被加锁。
SHARED 共享状态。数据库文件被加了共享锁。可以多线程执行读操作，但不能进行写操作。
RESERVED 保留状态。数据库文件被加保留锁。表示数据库将要进行写操作。
PENDING 未决状态。表示即将写入数据库，正在等待其他读线程释放 SHARED 锁。一旦某个线程持有 PENDING 锁，其他线程就不能获取 SHARED 锁。这样一来，只要等所有读线程完成，释放 SHARED 锁后，它就可以进入 EXCLUSIVE 状态了。
EXCLUSIVE 独占锁。表示它可以写入数据库了。进入这个状态后，其他任何线程都不能访问数据库文件。因此为了并发性，它的持有时间越短越好。

一个线程只有拥有低级别锁时才能够获得更高一级的锁

** Lock the file with the lock specified by parameter eFileLock - one

** of the following:

** (1) SHARED_LOCK

** (2) RESERVED_LOCK

** (3) PENDING_LOCK

** (4) EXCLUSIVE_LOCK

** Sometimes when requesting one lock state, additional lock states

** are inserted in between. The locking might fail on one of the later

** transitions leaving the lock state different from what it started but

** still short of its goal. The following chart shows the allowed

** transitions and the inserted intermediate states:

** UNLOCKED -> SHARED

** SHARED -> RESERVED

** SHARED -> (PENDING) -> EXCLUSIVE

** RESERVED -> (PENDING) -> EXCLUSIVE

** PENDING -> EXCLUSIVE

** This routine will only increase a lock. Use the sqlite3OsUnlock()

** routine to lower a locking level.

总结

综上所述，要保证数据库使用的安全，一般可以采用如下几种模式

SQLite 采用单线程模型，用专门的线程/队列（同时只能有一个任务执行访问）进行访问
SQLite 采用多线程模型，每个线程都使用各自的数据库连接（即 sqlite3 *）
SQLite 采用串行模型，所有线程都公用同一个数据库连接。

因为写操作的并发性并不好，当多线程进行访问时实际上仍旧需要互相等待，而读操作所需要的 SHARED 锁是可以共享的，所以为了保证最高的并发性，推荐

使用多线程模式
使用 WAL 模式
单线程写，多线程读（各线程都持有自己对应的数据库连接)
避免长时间事务
缓存 sqlite3_prepare 编译结果
多语句通过 BEGIN 和 COMMIT 做显示事务，减少多次的自动事务消耗

二、WAL 机制的原理是：

修改并不直接写入到数据库文件中，而是写入到另外一个称为 WAL 的文件中；如果事务失败，WAL 中的记录会被忽略，撤销修改；如果事务成功，它将在随后的某个时间被写回到数据库文件中，提交修改。同步 WAL 文件和数据库文件的行为被称为 checkpoint（检查点），它由 SQLite 自动执行，默认是在 WAL 文件积累到 1000 页修改的时候；当然，在适当的时候，也可以手动执行 checkpoint，SQLite 提供了相关的接口。执行 checkpoint 之后，WAL 文件会被清空。在读的时候，SQLite 将在 WAL 文件中搜索，找到最后一个写入点，记住它，并忽略在此之后的写入点（这保证了读写和读读可以并行执行）；随后，它确定所要读的数据所在页是否在 WAL 文件中，如果在，则读 WAL 文件中的数据，如果不在，则直接读数据库文件中的数据。在写的时候，SQLite 将之写入到 WAL 文件中即可，但是必须保证独占写入，因此写写之间不能并行执行。

2.1 wal工作原理

在引入WAL机制之前，SQLite使用rollbackjournal机制实现原子事务。

rollback journal机制的原理是：在修改数据库文件中的数据之前，先将修改所在分页中的数据备份在另外一个地方，然后才将修改写入到数据库文件中；如果事务失败，则将备份数据拷贝回来，撤销修改；如果事务成功，则删除备份数据，提交修改。

WAL机制的原理是：修改并不直接写入到数据库文件中，而是写入到另外一个称为WAL的文件中；如果事务失败，WAL中的记录会被忽略，撤销修改；如果事务成功，它将在随后的某个时间被写回到数据库文件中，提交修改。

2.2 wal优点：

1. 读和写可以完全地并发执行，不会互相阻塞（但是写之间仍然不能并发）。

2. WAL在大多数情况下，拥有更好的性能（因为无需每次写入时都要写两个文件）。

3. 磁盘I/O行为更容易被预测。

2.3 wal缺点：

1. 访问数据库的所有程序必须在同一主机上，且支持共享内存技术。

2. 每个数据库现在对应3个文件：<yourdb>.db，<yourdb>-wal，<yourdb>-shm。

3. 当写入数据达到GB级的时候，数据库性能将下降。

4. 3.7.0之前的SQLite无法识别启用了WAL机制的数据库文件。

2.4 wal如何记录数据--checkpoint

使用WAL模式时，改写操作是附加（append）到WAL文件，而不改动数据库文件，因此数据库文件可以被同时读取。当执行checkpoint操作时，WAL文件的内容会被写回数据库文件。当WAL文件达到SQLITE_DEFAULT_WAL_AUTOCHECKPOINT（默认值是1000）页（默认大小是1KB）时，会自动使用当前COMMIT的线程来执行checkpoint操作。也可以关闭自动checkpoint，改为手动定期checkpoint。

为了避免读取的数据不一致，查询时也需要读取WAL文件，并记录一个结尾标记（end mark）。这样的代价就是读取会变得稍慢，但是写入会变快很多。要提高查询性能的话，可以减小WAL文件的大小，但写入性能也会降低。需要注意的是，低版本的SQLite不能读取高版本的SQLite生成的WAL文件，但是数据库文件是通用的。这种情况在用户进行iOS降级时可能会出现，可以把模式改成delete，再改回WAL来修复。

要对一个数据库连接启用WAL模式，需要执行“PRAGMA journal_mode=WAL;”这条命令，它的默认值是“journal_mode=DELETE”。执行后会返回新的journal_mode字符串值，即成功时为"wal"，失败时为之前的模式（例如"delete"）。一旦启用WAL模式后，数据库会保持这个模式，这样下次打开数据库时仍然是 WAL模式。要停止自动checkpoint，可以使用wal_autocheckpoint指令或sqlite3_wal_checkpoint()函数。手动执行 checkpoint可以使用wal_checkpoint指令或sqlite3_wal_checkpoint()函数。

三、开启WAL机制

 int DataSource::InitDataBaseToWal(std::string sPath, bool isWal)
    {
        char* zErrMsg;
 
        sqlite3* db = NULL;
 
        int rc = sqlite3_open_v2(sPath.c_str(), &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX, NULL);
 
        if (rc != SQLITE_OK)
        {
            Logger::LogD("DataSource::sqlite [%s] or [%s] open failed", sPath.c_str(), sqlite3_errmsg(db));
            Logger::LogO("DataSource::sqlite [%s] or [%s] open failed", sPath.c_str(), sqlite3_errmsg(db));
 
            sqlite3_close(db);
 
            return -1;
        }
 
        if(isWal == true)
        {
            rc = sqlite3_exec(db, "PRAGMA journal_mode=WAL;", NULL, 0, &zErrMsg);
 
            if (rc != SQLITE_OK)
            {
                sqlite3_free(zErrMsg);
 
                sqlite3_close(db);
 
                return -1;
            }
 
            rc = sqlite3_exec(db, "PRAGMA wal_autocheckpoint=100;", NULL, 0, &zErrMsg);
 
            if (rc != SQLITE_OK)
            {
                sqlite3_free(zErrMsg);
 
                sqlite3_close(db);
 
                return -1;
            }
        }
        else
        {
            rc = sqlite3_exec(db, "PRAGMA journal_mode=DELETE;", NULL, 0, &zErrMsg);
 
                   if (rc != SQLITE_OK)
               {
                sqlite3_free(zErrMsg);
 
                sqlite3_close(db);
 
                 return -1;
              }
            }
 
        return true;
      }

四、多线程并发写操作的安全性

sqlite实际支持的是多线程同时读但只支持同一时刻一个线程写，即所谓的多读单写，sqlite 支持 single-thread/multi-thread/serialized 三种不同的线程安全模式。可以在编译sqlite组件时进行配置，或者可以通过 sqlite3_threadsafe()/sqlite3_config() 在程序运行时进行查看并配置线程安全模式。经过实际写 demo 测试，进行 multi-thread 或 serialized 配置以后，多线程并发读的场景下，没有问题。但是多线程并发写时依旧会抛错 database is locked。事实证明Sqlite不支持并发执行写入操作，即使是不同的表，只支持库级锁，而且这个Sqlite本身没有实现，必须自己实现这个库级锁，通过查阅官网资料，发现sqlite提供两个 busy handle 函数sqlite3_busy_timeout()/sqlite3_busy_handle() 在并发访问失败时，会调用注册的 busy handle 函数，在注册的自定义的 busy handle 函数中可以进行处理（如重试n次等），这种处理方式必须建立在多线程多个数据库连接，多个数据库连接可以理解成，用sqlite3_open或者sqlite3_open_v2打开同一个数据库文件，每一个线程维护一个数据库连接对象，这样发生写竞争冲突的时候，可以通过回调函数重试，解决并发写。

线程安全：是指二个或三个线程可以同时调用独立的不同的sqlite3_open() 返回的"sqlite3"结构。而不是在多线程中同时使用同一个 sqlite3 结构指针。
一个sqlite3结构只能在调用 sqlite3_open创建它的那个进程中使用。你不能在一个线程中打开一个数据库然后把指针传递给另一个线程使用。这是因为大多数多线程系统的限制