SQLite线程安全相关整理

文章目录

• 概述
• 线程模式的选择和设置
• 编译阶段线程模式的选择
• 启动阶段线程模式的选择
• 运行阶段线程模式的选择
• 线程安全问题
• 多个应用程序或同一个应用程序的多个实例可以同时访问单个数据库文件吗？
• SQLite线程安全吗？
• SQLite的事务
• 事务概述
• 两种方法提速事务提交
• 事务执行过程
• WAL模式
• 锁及死锁
• 锁
• SQLite死锁
• 死锁Example
• 死锁解决
• 线程安全小结及实践
• 小结
• 编程实践测试
• 参考

概述

SQLite支持三种不同的线程模式：

• 单线程。在此模式下，所有互斥锁都被禁用，并且SQLite一次不能在多个线程中使用。

• 多线程。在这种模式下，只要在两个或多个线程中不同时使用单个数据库连接，SQLite就可以被多个线程安全地使用。只要一个数据库连接不被多个线程同时使用就是安全的。源码中是启用bCoreMutex，禁用bFullMutex。实际上就是禁用数据库连接和prepared statement（准备好的语句）上的锁，因此不能在多个线程中并发使用同一个数据库连接或prepared statement。

• 序列化(也有翻译成串行的)。在序列化模式下，SQLite可以被多个线程安全地使用而没有任何限制。启用所有的锁，包括bCoreMutex和bFullMutex。因为数据库连接和prepared statement都已加锁，所以多线程使用这些对象时没法并发，也就变成序列化了。

可以在编译时（在从源代码编译SQLite库时）或在启动时（当打算使用SQLite的应用程序正在初始化时）或在运行时（当新的SQLite数据库时）选择线程模式正在创建连接。

一般来说，运行时覆盖启动时，启动时覆盖编译时。除此之外，单线程模式一旦被选中就无法覆盖。

默认模式是序列化

这里所说的初始化是指调用sqlite3_initialize()函数，这个函数在调用sqlite3_open()时会自动调用，且只有第一次调用是有效的

另一个要说明的是prepared statement，它是由数据库连接（的pager）来管理的，使用它也可看成使用这个数据库连接。因此在多线程模式下，并发对同一个数据库连接调用sqlite3_prepare_v2()来创建prepared statement，或者对同一个数据库连接的任何prepared statement并发调用sqlite3_bind_*()和sqlite3_step()等函数都会出错（在iOS上，该线程会出现EXC_BAD_ACCESS而中止）。这种错误无关读写，就是只读也会出错。文档中给出的安全使用规则是：没有事务正在等待执行，所有prepared statement都被finalized。

线程模式的选择和设置

编译阶段线程模式的选择

使用SQLITE_THREADSAFE的编译阶段参数来选择线程模式。

• -DSQLITE_THREADSAFE = 1, 将默认线程模式设置为序列化模式。
• -DSQLITE_THREADSAFE = 2, 将默认线程模式设置为多线程。
• -DSQLITE_THREADSAFE = 0, 将线程模式设置为单线程
• 如果不存在SQLITE_THREADSAFE编译时参数，则使用序列化模式

sqlite3_threadsafe()接口的返回值是由编译阶段线程模式的选择来决定的。

• 如果在编译阶段选择了single-thread模式，那么sqlite3_threadsafe()返回false。
• 如果选择的是multi-thread或者serialized，那么sqlite3_threadsafe()返回true。

sqlite3_threadsafe()接口早于multi-thread模式和启动阶段以及运行阶段的模式选择，所以不能区分multi-thread和serialized，也不能报告通知启动阶段和运行阶段模式的变更。

如果single-thread模式在编译阶段被选择了，那么关键的互斥逻辑在构建时就被省略掉了，也不可能在启动阶段或者运行阶段让multi-thread和serialized使能。（同上面的那句单线程模式无法被覆盖）

启动阶段线程模式的选择

假设编译阶段线程模型不是single-thread，那么线程模型就可以使用sqlite3_config() 接口在初始化过程中更改。

• SQLITE_CONFIG_SINGLETHREAD将SQLite置于单线程模式
• SQLITE_CONFIG_MULTITHREAD则启动多线程模式
• SQLITE_CONFIG_SERIALIZED是serialized模式。

运行阶段线程模式的选择

如果在编译时或启动时未选择单线程模式，则可以将单个数据库连接创建为多线程或序列化。无法将单个数据库连接降级为单线程模式。如果编译时或启动时模式是单线程，也不可能升级单个数据库连接。

一个单独的数据库的线程模式是由sqlite3_open_v2()的第三个参数中的flags给出决定的。

int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
int sqlite3_open16(
  const void *filename,   /* Database filename (UTF-16) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
int sqlite3_open_v2(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb,         /* OUT: SQLite db handle */
  int flags,              /* Flags */
  const char *zVfs        /* Name of VFS module to use */
);

• SQLITE_OPEN_NOMUTEX的标志将使得数据库连接处于multi-thread
• SQLITE_OPEN_FULLMUTEX标志则将连接处于serialized模式。
• 如果flags没有被指定 或者 没有使用sqlite3_open_v2()而是sqlite3_open() or sqlite3_open16()，那么默认使用的模式将由编译阶段和启动阶段的设置来决定

线程安全问题

多个应用程序或同一个应用程序的多个实例可以同时访问单个数据库文件吗？

多个进程可以同时打开同一个数据库。多个进程可以同时执行SELECT。但是，只有一个进程可以随时对数据库进行更改。

SQLite使用读取器/写入器锁来控制对数据库的访问。（在Win95 / 98 / ME下，缺少对读取器/写入器锁的支持，而是使用概率模拟。）但请注意：如果数据库文件保存在NFS文件系统上，则此锁定机制可能无法正常工作。这是因为许多NFS实现都会破坏fcntl（）文件锁定。如果多个进程可能同时尝试访问该文件，则应避免将SQLite数据库文件放在NFS上。在Windows上，Microsoft的文档说如果您没有运行Share.exe守护程序，则在FAT文件系统下锁定可能不起作用。对Windows有很多经验的人告诉我，网络文件的文件锁定是非常错误的，并且不可靠。如果他们说的是真的，我们知道没有其他嵌入式 SQL数据库引擎支持与SQLite一样多的并发性。SQLite允许多个进程一次打开数据库文件，并允许多个进程一次读取数据库。当任何进程想要写入时，它必须在更新期间锁定整个数据库文件。但这通常只需要几毫秒。其他流程只是等待作者完成然后继续他们的业务。
其他嵌入式SQL数据库引擎通常只允许单个进程一次连接到数据库。

但是，客户端/服务器数据库引擎（如PostgreSQL，MySQL或Oracle）通常支持更高级别的并发性，并允许多个进程同时写入同一数据库。这在客户端/服务器数据库中是可能的，因为总有一个良好控制的服务器进程可用于协调访问。如果您的应用程序需要大量并发，那么您应该考虑使用客户端/服务器数据库。但经验表明，大多数应用程序需要的并发性远低于设计人员的想象。

当SQLite尝试访问由另一个进程锁定的文件时，默认行为是返回SQLITE_BUSY。您可以使用sqlite3_busy_handler()或 sqlite3_busy_timeout() API函数从C代码调整此行为

SQLite线程安全吗？

线程是有弊端的。避免他们。

SQLite是线程安全的。我们做出这种让步，因为许多用户选择忽略前一段中给出的建议。但为了保证线程安全，必须在SQLITE_THREADSAFE预处理器宏设置为1的情况下编译SQLite。发布的Windows和Linux预编译二进制文件都是以这种方式编译的。如果您不确定要链接的SQLite库是否被编译为线程安全，您可以调用sqlite3_threadsafe() 接口来查找。

SQLite是线程安全的，因为它使用互斥锁来序列化对公共数据结构的访问。但是，获取和释放这些互斥锁的工作会使SQLite略微减慢。因此，如果您不需要SQLite是线程安全的，则应禁用互斥锁以获得最佳性能。有关其他信息，请参阅线程模式文档。

在Unix下，你不应该通过fork（）系统调用将一个开放的SQLite数据库带入子进程

SQLite的事务

事务概述

数据库只有在事务中才能被更改。所有更改数据库的命令（除SELECT以外的所有SQL命令）都会自动开启一个新事务，并且当最后一个查询完成时自动提交。

而BEGIN命令可以手动开始事务，并关闭自动提交。当下一条COMMIT命令执行时，自动提交再次打开，事务中所做的更改也被写入数据库。当COMMIT失败时，自动提交仍然关闭，以便让用户尝试再次提交。若执行的是ROLLBACK命令，则也打开自动提交，但不保存事务中的更改。关闭数据库或遇到错误时，也会自动回滚事务。

经常有人抱怨SQLite的插入太慢，实际上它可以做到每秒插入几万次，但是每秒只能提交几十次事务。因此在插入大批数据时，可以通过禁用自动提交来提速

两种方法提速事务提交

默认情况下，每个INSERT语句都是自己的事务。但是如果用BEGIN … COMMIT包围多个INSERT语句，则所有插入都被分组到一个事务中。提交事务所需的时间在所有随附的insert语句中分摊，因此每个insert语句的时间大大减少。

另一种选择是运行PRAGMA synchronous = OFF。此命令将导致SQLite不等待数据到达磁盘表面，这将使写入操作看起来更快。但是，如果在事务中间断电，则数据库文件可能会损坏

事务执行过程

事务在改写数据库文件时，会先生成一个rollback journal（回滚日志），记录初始状态（其实就是备份），所有改动都是在数据库文件上进行的。当事务需要回滚时，可以将备份文件的内容还原到数据库文件；提交成功时，默认的delete模式下会直接删除这个日志。这个日志也可以帮助解决事务执行过程中断电，导致数据库文件损坏的问题。但如果操作系统或文件系统有bug，或是磁盘损坏，则仍有可能无法恢复。

WAL模式

而从3.7.0版本（对应iOS 4.3）开始，SQLite还提供了Write-Ahead Logging模式。与delete模式相比，WAL模式在大部分情况下更快，并发性更好，读和写之间互不阻塞；而其缺点对于iPhone这种嵌入式设备来说可以忽略，只需注意不要以只读方式打开WAL模式的数据库即可。

使用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()函数。

还有一个很重要的知识点需要强调：事务是和数据库连接相关的，每个数据库连接（使用pager来）维护自己的事务，且同时只能有一个事务（但是可以用SAVEPOINT来实现内嵌事务）。

也就是说，事务与线程无关，一个线程里可以同时用多个数据库连接来完成多个事务，而多个线程也可以同时（非并发）使用一个数据库连接来共同完成一个事务。

锁及死锁

锁

实现事务，就不得不用到锁。
一个SQLite数据库文件有5种锁的状态：

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

典型的读者写者问题。

一个线程只有在拥有低级别的锁的时候，才能获取更高一级的锁。SQLite就是靠这5种类型的锁，巧妙地实现了读写线程的互斥。同时也可看出，写操作必须进入EXCLUSIVE状态，此时并发数被降到1，这也是SQLite被认为并发插入性能不好的原因。

另外，read-uncommitted和WAL模式会影响这个锁的机制。在这2种模式下，读线程不会被写线程阻塞，即使写线程持有PENDING或EXCLUSIVE锁

SQLite死锁

死锁Example

连接1：BEGIN （UNLOCKED）
连接1：SELECT ... （SHARED）
连接1：INSERT ... （RESERVED）
连接2：BEGIN （UNLOCKED）
连接2：SELECT ... （SHARED）
连接1：COMMIT （PENDING，尝试获取EXCLUSIVE锁，但还有SHARED锁未释放，返回SQLITE_BUSY）
连接2：INSERT ... （尝试获取RESERVED锁，但已有PENDING锁未释放，返回SQLITE_BUSY）

现在2个连接都在等待对方释放锁，于是就死锁了。当然，实际情况并没那么糟糕，任何一方选择不继续等待，回滚事务就行了。

死锁解决

不过要更好地解决这个问题，就必须更深入地了解事务了。
实际上BEGIN语句可以有3种起始状态：

• DEFERRED：默认值，开始事务时不获取任何锁。进行第一次读操作时获取SHARED锁，进行第一次写操作时获取RESERVED锁。
• IMMEDIATE：开始事务时获取RESERVED锁。
• EXCLUSIVE：开始事务时获取EXCLUSIVE锁

现在考虑2个事务在开始时都使用 IMMEDIATE 方式：

连接1：BEGIN IMMEDIATE （RESERVED）
连接1：SELECT ... （RESERVED）
连接1：INSERT ... （RESERVED）
连接2：BEGIN IMMEDIATE （尝试获取RESERVED锁，但已有RESERVED锁未释放，因此事务开始失败，返回SQLITE_BUSY，等待用户重试）
连接1：COMMIT （EXCLUSIVE，写入完成后释放）
连接2：BEGIN IMMEDIATE （RESERVED）
连接2：SELECT ... （RESERVED）
连接2：INSERT ... （RESERVED）
连接2：COMMIT （EXCLUSIVE，写入完成后释放）

而EXCLUSIVE方式则更为严苛，即使其他连接以DEFERRED方式开启事务也不会死锁：

连接1：BEGIN EXCLUSIVE （EXCLUSIVE）
连接1：SELECT ... （EXCLUSIVE）
连接1：INSERT ... （EXCLUSIVE）
连接2：BEGIN （UNLOCKED）
连接2：SELECT ... （尝试获取SHARED锁，但已有EXCLUSIVE锁未释放，返回SQLITE_BUSY，等待用户重试）
连接1：COMMIT （EXCLUSIVE，写入完成后释放）
连接2：SELECT ... （SHARED）
连接2：INSERT ... （RESERVED）
连接2：COMMIT （EXCLUSIVE，写入完成后释放）

不过在并发很高的情况下，直接获取EXCLUSIVE锁的难度比较大；
而且为了避免EXCLUSIVE状态长期阻塞其他请求，最好的方式还是让所有写事务都以IMMEDIATE方式开始

要实现重试的话，可以使用sqlite3_busy_timeout()或sqlite3_busy_handler()函数

线程安全小结及实践

小结

由上可知，要想保证线程安全的话，可以有这4种方式：

• SQLite使用单线程模式，用一个专门的线程访问数据库。
• SQLite使用单线程模式，用一个线程队列来访问数据库，队列一次只允许一个线程执行，队列里的线程共用一个数据库连接。
• SQLite使用多线程模式，每个线程创建自己的数据库连接。
• SQLite使用串行模式，所有线程共用全局的数据库连接

编程实践测试

测试这几种方式在iPhone 4（iOS 4.3.3，SQLite 3.7.2）上的性能表现

• 第一种方式太过麻烦，需要线程间通信，这里我就忽略了。
• 第二种方式可以用dispatch_queue_create()来创建一个serial queue，或者用一个maxConcurrentOperationCount为1的NSOperationQueue来实现。
  这种方式的缺点就是事务必须在一个block或operation里完成，否则会乱序；而耗时较长的事务会阻塞队列。另外，没法利用多核CPU的优势

测试结果为只读时平均每秒165次，只写时每秒68次，同时读写时每秒各47次。

换成多线程或串行模式时，效率也差不多。

详细代码见SQLite在多线程环境下的应用

试试WAL模式

if (sqlite3_exec(database, "PRAGMA journal_mode=WAL;", NULL, NULL, &errorMsg) != SQLITE_OK) {
    NSLog(@"Failed to set WAL mode: %s", errorMsg);
}

sqlite3_wal_checkpoint(database, NULL); // 每次测试前先checkpoint，避免WAL文件过大而影响性能

测试结果为只读时平均每秒166次，只写时每秒244次，同时读写时每秒各97次。
并发性增加了1倍有木有！更夸张的是写入比读取还快了

在自编译的3.7.8版中，同时读写为每秒各102次，加上-DSQLITE_THREADSAFE=0参数后为每秒各104次，性能稍有提升

• 第三种方式需要打开和关闭数据库连接，所以会额外消耗一些时间。此外还要维持各个连接间的互斥，事务也比较容易冲突，但能确保事务正确执行。

这里就无需递归调用了，直接在子线程中循环即可。

测试结果为只读时平均每秒164次，只写时每秒68次，同时读写时分别为每秒14和30次（波动很大）。
此外，这种方式因为最初启动的几个线程持续访问数据库，后加入的线程会滞后几秒才启动，
且很难打开数据库连接或创建prepare statement。
调试时发现只会启用2个线程，但是随队列中block数目的增加，读性能增高，写性能降低。
读写各3个block时分别为每秒35和14次。

WAL模式下甚至连初始时启动2个线程都会被lock，因此只能改成不断重试
结果为只读时平均每秒169次，只写时每秒246次，同时读写时每秒分别为90和57次（波动较大）。
并发效率有了显著提升，但仍不及第二种方式

• 第四种方式相当于让SQLite来维护队列，只不过SQL的执行是乱序的，因此无法保证事务性

测试结果为只读时平均每秒164次，只写时每秒68次，同时读写时每秒分别为57和43次。
读线程比写线程的速率更高，而且新线程的加入不需要等待。
WAL模式下，只读时平均每秒176次，只写时每秒254次，同时读写时每秒分别为109和85次。

由此可见，要获得最好的性能的话，WAL模式是必须启用的，为此也有必要自己编译SQLite 3.7.0以上的版本

在测试过的后3种方式中：

• 第3种是效率最低的，不建议使用；
• 第4种读取性能更高，适合无需使用事务的场合；
• 第2种适用范围更广，效率也足够优秀，一般应采用这种方式。

不过要注意的是，第2种方式在测试时的逻辑是完全与数据库相关的。实际中可能要做计算或IO访问等工作，在此期间其他线程都是被阻塞的，这样就会大大降低效率了。因此只建议把访问数据库的逻辑放入队列，其余工作在其他线程里完成。

参考

• SQLite在多线程环境下的应用

• Using SQLite In Multi-Threaded Applications

• FAQ Frequently Asked Questions

• 微信iOS SQLite源码优化实践

• sqlite3学习笔记1

• sqlite3学习笔记2

• sqlite 怎么开启wal机制

• SQLite3 文件锁和并发