SQLite的并发性和隔离级别

SQLite是知名的嵌入式数据库，一个dll只有1MB。开源，纯C，单文件且提供许多功能开关，编译非常方便。

一般认为SQLite的并发性不高，其实按官网的说法，它已经是嵌入式数据库中并发性高的了。

本文为阅读官方文档加上自己实验后的总结。

读写锁

SQLite锁的级别依次是：NoLock Shared Reserved Pending Exclusive。

• Shared是一般意义上的读锁，允许同时有多个并发读
• Exclusive是一般意义上的写锁，只允许有一个，作用于整个数据库文件，不允许其它连接读，只有自己能读写
• Reserved是写日志阶段时的锁，也只允许有一个。关键是此时仍能加读锁，只是不能再加Reserved或更别的锁
• Pending是Commit时发生的，会阻止加新的读锁，等所有已有读锁都释放了就进入Exclusive

对于一个事务，刚开始Select的时候只有Shared锁，等开始DML了就会加Reserved锁，Commit时进入Pending阶段，如果一段时间内没等到所有的读锁都释放，锁就升级失败。

隔离级别

隔离级别默认情况下是高的Serializable。

脏读：

事务T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是的。

T1修改了没提交，T2读不到T1的内容，因为T1只往日志里写了东西，T2是直接读的文件。因此不会出现脏读。

不可重复读：

事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果。

T1和T2都能读取，此时T1存在读锁，T2可以修改但无法提交，此情形同脏读。因此不会出现不可重复读。

幻读：

个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入“一行新数据”。那么，以后就会发生操作个事务的用户发现表中还存在没有修改的数据行。

T1修改了没提交，存在写锁，T2无法修改；或者T1一开始读取了，存在读锁，T2能修改但无法提交。总之此时无法修改数据库，因此不会出现幻读。

WAL下的隔离级别

启用WAL模式后，SQLite的隔离性变为Snapshot。

锁的行为变为即使是Exclusive也允许读取，某一事务也能写入，即读写之间可以并发，这大大增加了性能。只不过可能读到的是旧数据，即读不到确实已提交的内容，只有本连接所有读锁都释放或者新连接才能读到已提交的内容；上一段都是直接无法提交的。当然，多个写之间仍无法并发。

• 脏读：无变化
• 不可重复读：T2可以提交，且T1读到的仍是提交前的内容
• 幻读：T1修改了没提交，T2无法修改；或者T1读取了，T2可以修改也能提交，此情形同上一点

因此三种问题还是都不会出现。

不过引入了一个新问题。假如T1读取了，T2修改并提交，T1再想提交时，数据库已经不是新的了，它修改的是老版本的快照。此时该语句会简单的失败，不会去分析两者修改的范围是否有冲突，或者说冲突的检测范围是整个数据库文件。
解决办法是对于T1使用BEGIN IMMEDIATE语句，这样T1在一开始就获得了写锁，T2无法修改。

多线程

SQLite有三种线程模式：单线程、多线程、Serialized。注意此处的Serialized与隔离级别中的Serializable没有关系。

根据官网的说法：

• 当编译期参数SQLITE_THREADSAFE设为1时（也是一般情况下的默认值），“SQLite can be safely used by multiple threads with no restriction”
• 当它设为2时，“SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads”
• 当它设为0时，“SQLite is unsafe to use in more than a single thread at once”

按照此说法，默认情况下多线程怎么使用都没问题；设为2时，对于某一连接，只要同一时间只有一个线程使用就没问题；设为0时，一次只能有一个线程。

但是许多网友的建议比较保守，大家认为即使设为1，也不能重用连接。这当然是安全的，但却比官网设为2时还严格，官网设为2时也没说不能重用，只是说不能同时使用。另外官方CLI设为了0。

我对官网这些话的理解：设为1时一个连接可以有多个游标，也可以重用。设为2时仍然可以有多个游标也能重用，但是不要多个线程使用同一个连接，各个线程要用的时候自己新建连接即可。设为0时仍然支持多进程连接，但对于单一的进程，一次就只能使用一个连接。

测试代码，能正常读取数据：

import sqlite3
import threading
import time

def f(con):
    cur2 = con.execute('select * from category')
    print(cur2.fetchone())
    time.sleep(1)
    print(cur2.fetchone())

con = sqlite3.connect('Northwind_small.sqlite', check_same_thread=False)

cur1 = con.execute('select * from region')
print(cur1.fetchone())
print(cur1.fetchone())

t = threading.Thread(target=f,args=(con,))
t.start()
t.join()

print(cur1.fetchall())