SQLite在多线程环境下的应用

本文章来自网络------禚

文一

SQLite的FAQ里面已经专门说明，先贴出来。供以后像我目前的入门者学习。



(7) 多个应用程序或者同一个应用程序的多个例程能同时存取同一个数据库文件吗？

多进程可以同时打开同一个数据库，也可以同时 SELECT 。但只有一个进程可以立即改数据库。

SQLite使用读/写锁定来控制数据库访问。(Win95/98/ME 操作系统缺乏读/写锁定支持，在低于 2.7.0 的版本中，这意味着在 windows 下在同一时间内只能有一个进程读数据库。在版本 2.7.0 中 这个问题通过在 windows 接口代码中执行一个用户间隔几率读写锁定策略解决了。) 但如果数据库文件在一个 NFS 文件系统中，控制并发读书的锁定机制可以会出错。因为 NFS 的fcntl() 文件锁定有时会出问题。如果有多进程可能并发读数据库则因当避免把数据库文件放在 NFS 文件系统中。 根据微软的文档，如果不运行 Share.exe 后台程序则 FAT 文件系统中的锁定可能不工作。对 Windows 非常有经验的人告诉我网络文件的锁定有许多问题并且不可靠。如果是这样，在2个或以上 Windows 系统中共享一个 SQLite 数据库文件会导致不可预知的问题。

我们知道没有其他的嵌入式 SQL数据库引擎比SQLite支持更多的并发性。 SQLite允许多进程 同时打开和读取数据库。任何一个进程需要写入时，整个数据库将在这一过程中被锁定。但这一般仅耗时 几毫秒。其他进程只需等待然后继续其他事务。其他嵌入式SQL数据库引擎往往只允许单进程访问数据库。

但是，client/server型的数据库引擎 (如 PostgreSQL, MySQL, 以及 Oracle) 通常支持更高的并发度， 并支持多进程同时写入同一个数据库。由于总有一个控制良好的服务器协调数据库的访问，这才保证了以上 特性的实现。如果你的应用需要很高的并发度，你应该考虑使用client/server数据库。事实上，经验告诉 我们大多数应用所需要的并发度比他们的设计者们想象的要少得多。

当 SQLite 尝试操作一个被另一个进程锁定的文件时，缺省的行为是返回 SQLITE_BUSY。你可以用 C代码更改这一行为。 使用 sqlite3_busy_handler() 或sqlite3_busy_timeout() API函数。

如果两个或更多进程同时打开同一个数据库，其中一个进程创建了新的表或索引，则其它进程可能不能立即看见新的表。其它进程可能需要关闭并重新连结数据库。


--------------------------------------------------------------------------------

(8) SQLite是线程安全的吗?

有时候是的。为了线程安全，SQLite 必须在编译时把 THREADSAFE 预处理宏设为1。在缺省的发行的已编译版本中 Windows 版的是线程安全的，而 Linux 版的不是。如果要求线程安全，Linux 版的要重新编译。

“线程安全”是指二个或三个线程可以同时调用独立的不同的sqlite3_open() 返回的"sqlite3"结构。而不是在多线程中同时使用同一个 sqlite3 结构指针。

一个sqlite3结构只能在调用 sqlite3_open创建它的那个进程中使用。你不能在一个线程中打开一个数据库然后把指针传递给另一个线程使用。这是因为大多数多线程系统的限制（或 Bugs？）例如RedHat9上。在这些有问题的系统上，一个 线程创建的fcntl()锁不能由另一个线程删除或修改。由于SQLite依赖fcntl()锁来进行并发控制，当在线程间传递数据库连接时会出现严重的问题。

也许在Linux下有办法解决fcntl()锁的问题，但那十分复杂并且对于正确性的测试将是极度困难的。因此，SQLite目前不允许在线程间共享句柄。

在UNIX下，你不能通过一个 fork() 系统调用把一个打开的 SQLite 数据库放入子过程中，否则会出错。

文二

这几天研究了一下SQLite这个嵌入式数据库在多线程环境下的应用，感觉里面的学问还挺多，于是就在此分享一下。

先说下初衷吧，实际上我经常看到有人抱怨SQLite不支持多线程。而在iOS开发时，为了不阻塞主线程，数据库访问必须移到子线程中。为了解决这个矛盾，很有必要对此一探究竟。

关于这个问题，最权威的解答当然是SQLite官网上的 “Is SQLite threadsafe?” 这个问答。
简单来说，从3.3.1版本开始，它就是线程安全的了。而 iOS的SQLite版本 没有低于这个版本的：

3.4.0 - iPhone OS 2.2.1
3.6.12 - iPhone OS 3.0 / 3.1
3.6.22 - iPhone OS 4.0
3.6.23.2 - iPhone OS 4.1 / 4.2
3.7.2 - iPhone OS 4.3
3.7.7 - iPhone OS 5.0

当然，你也可以自己编译最新版本。 只是我发现自己编译出来的3.7.8居然比iOS 4.3.3内置的3.7.2慢了一半，不知道苹果做了什么优化。 发现是我编译成了debug版本，改成release后性能比内置版本高5%左右，不过构建出来的app会大420k左右。

不过这个线程安全仍然是有限制的，在这篇 《Is SQLite thread-safe?》 里有详细的解释。
另一篇重要的文档就是 《SQLite And Multiple Threads》 。它指出SQLite支持3种线程模式：

1. 单线程：禁用所有的mutex锁，并发使用时会出错。当SQLite编译时加了SQLITE_THREADSAFE=0参数，或者在初始化SQLite前调用sqlite3_config(SQLITE_CONFIG_SINGLETHREAD)时启用。
   
2. 多线程：只要一个数据库连接不被多个线程同时使用就是安全的。源码中是启用bCoreMutex，禁用bFullMutex。实际上就是禁用数据库连接和prepared statement（准备好的语句）上的锁，因此不能在多个线程中并发使用同一个数据库连接或prepared statement。当SQLite编译时加了SQLITE_THREADSAFE=2参数时默认启用。若SQLITE_THREADSAFE不为0，可以在初始化SQLite前，调用sqlite3_config(SQLITE_CONFIG_MULTITHREAD)启用；或者在创建数据库连接时，设置SQLITE_OPEN_NOMUTEX flag。
   
3. 串行：启用所有的锁，包括bCoreMutex和bFullMutex。因为数据库连接和prepared statement都已加锁，所以多线程使用这些对象时没法并发，也就变成串行了。当SQLite编译时加了SQLITE_THREADSAFE=1参数时默认启用。若SQLITE_THREADSAFE不为0，可以在初始化SQLite前，调用sqlite3_config(SQLITE_CONFIG_SERIALIZED)启用；或者在创建数据库连接时，设置SQLITE_OPEN_FULLMUTEX flag。
   

而这里所说的 初始化 是指调用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 。
顺带一提，调用sqlite3_threadsafe()可以获得编译期的SQLITE_THREADSAFE参数。标准发行版是1，也就是串行模式；而iOS上是2，也就是多线程模式；Python的sqlite3模块也默认使用串行模式，可以用sqlite3.threadsafety来配置。但是默认情况下，一个线程只能使用当前线程打开的数据库连接，除非在连接时设置了check_same_thread=False参数。

现在3种模式都有所了解了，清楚SQLite并不是对多线程无能为力后，接下来就了解下 事务 吧。
数据库只有在事务中才能被更改。所有更改数据库的命令（除SELECT以外的所有SQL命令）都会自动开启一个新事务，并且当最后一个查询完成时自动提交。
而BEGIN命令可以手动开始事务，并关闭自动提交。当下一条COMMIT命令执行时，自动提交再次打开，事务中所做的更改也被写入数据库。当COMMIT失败时，自动提交仍然关闭，以便让用户尝试再次提交。若执行的是ROLLBACK命令，则也打开自动提交，但不保存事务中的更改。关闭数据库或遇到错误时，也会自动回滚事务。
经常有人抱怨 SQLite的插入太慢 ，实际上它可以做到每秒插入几万次，但是每秒只能提交几十次事务。因此在插入大批数据时，可以通过禁用自动提交来提速。

事务在改写数据库文件时，会先生成一个rollback journal（回滚日志），记录初始状态（其实就是备份），所有改动都是在数据库文件上进行的。当事务需要回滚时，可以将备份文件的内容还原到数据库文件；提交成功时，默认的delete模式下会直接删除这个日志。这个日志也可以帮助解决事务执行过程中断电，导致数据库文件损坏的问题。但如果操作系统或文件系统有bug，或是磁盘损坏，则仍有可能无法恢复。
而从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 来实现内嵌事务）。
也就是说，事务与线程无关，一个线程里可以同时用多个数据库连接来完成多个事务，而多个线程也可以同时（非并发）使用一个数据库连接来共同完成一个事务。
下面用Python来演示一下：

# -*- coding: utf-8 -*-

import sqlite3
import threading


def f():
    con.rollback()

con = sqlite3.connect('test.db', check_same_thread=False) # 允许在其他线程中使用这个连接
cu = con.cursor()

cu.execute('CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY)')

print cu.execute('SELECT count(*) FROM test').fetchone()[0] # 0
cu.execute('INSERT INTO test VALUES (NULL)')
print cu.execute('SELECT count(*) FROM test').fetchone()[0] # 1

thread = threading.Thread(target=f)
thread.start()
thread.join()

print cu.execute('SELECT count(*) FROM test').fetchone()[