离线数据持久化SQLite对比解析 - 第4节
4.1 事务隔离级别：READ UNCOMMITTED 与 SERIALIZABLE
事务是数据库系统保证数据一致性的基本单位，具备 ACID 特性。隔离性（Isolation）控制并发事务之间的可见程度。SQLite 只支持 READ UNCOMMITTED 和 SERIALIZABLE 两种隔离级别，不存在 READ COMMITTED 或 REPEATABLE READ。
4.1.1 核心概念对比
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | SQLite 默认？ |
|---|---|---|---|---|
| READ UNCOMMITTED | 允许 | 可能 | 可能 | 否（需 PRAGMA 显式设置） |
| SERIALIZABLE | 禁止 | 禁止 | 禁止 | 是 |
- READ UNCOMMITTED：读操作不加共享锁，可能读到其他事务尚未提交的数据（脏读）。仅在极少读多写少且允许脏读的场景下使用。
- SERIALIZABLE：SQLite 的默认级别，所有事务按顺序串行化执行，完全隔离但并发度低。
关键事实：SQLite 的 SERIALIZABLE 并非通过两阶段锁或 MVCC 实现，而是依靠数据库级锁——写事务获得 EXCLUSIVE 锁后，阻止所有其他读/写。
4.1.2 设置隔离级别
通过 PRAGMA 命令更改当前连接的事务行为：
-- 设为 READ UNCOMMITTED（允许脏读）
PRAGMA read_uncommitted = 1;
-- 恢复为 SERIALIZABLE
PRAGMA read_uncommitted = 0;注意：READ UNCOMMITTED 使得读操作不获取共享锁（SHARED），因此在写事务持有 PENDING 或 EXCLUSIVE 锁时仍可读取数据库快照，但可能读到未提交的中间数据。
4.2 锁状态：UNLOCKED → SHARED → RESERVED → PENDING → EXCLUSIVE
SQLite 使用五种锁状态管理并发访问，每种状态代表数据库文件当前被持有的锁类型。锁状态只能沿特定方向升级，不能跳级或降级。
4.2.1 五状态转换规则
- UNLOCKED：初始状态，没有锁。任何操作发生时必须升级到 SHARED（读）或 RESERVED/EXCLUSIVE（写）。
- SHARED：读锁，允许多个连接同时持有 SHARED 锁。此时可以读数据库，但不能写。
- RESERVED：写预留锁，表示当前连接计划写入但尚未开始。允许其他连接继续读（继续持有 SHARED），但阻止其他连接获取 RESERVED 或更高锁。
- PENDING：升级锁，表示连接即将开始写入。此时不允许新 SHARED 锁获得，但已有 SHARED 锁的连接可以继续读。等待已有 SHARED 锁释放后，升级为 EXCLUSIVE。
- EXCLUSIVE：独占锁，只允许当前连接访问。写入操作必须在 EXCLUSIVE 状态下进行。其他连接不能读也不能写。
4.3 死锁场景：为什么 SQLite 极少死锁？
SQLite 使用单写者策略和严格锁升级顺序，使得死锁理论上的可能性极低，但并非不存在。
4.3.1 典型死锁条件
死锁四要素：互斥、持有并等待、非抢占、循环等待。在 SQLite 中，RESERVED 到 PENDING 的等待阶段可能造成两个事务互相阻塞。
-- 模拟死锁（极低概率，但概念上存在）
-- 连接 A：写事务，持有 RESERVED，等待升级到 PENDING
-- 连接 B：写事务，持有 RESERVED，等待升级到 PENDING
-- 两者均需要其他 SHARED 锁释放，但互相等待对方先释放 RESERVED实际生产中，SQLite 的忙等待超时（busy_timeout）和单线程写提交大幅降低了死锁发生概率。大多数“死锁”其实是活锁——写事务因无法获得 EXCLUSIVE 锁而反复重试。
4.3.2 死锁检测与处理
- SQLite 没有内置死锁检测器，依赖超时机制：
PRAGMA busy_timeout设置等待毫秒数。 - 超时后返回
SQLITE_BUSY错误码，应用程序应回滚当前事务并重试。 - 避免长事务，保持写事务简短，减少锁持有时间。
4.4 常见误区
- 误区：SQLite 支持所有标准隔离级别。
现实：只支持 READ UNCOMMITTED 和 SERIALIZABLE，无 READ COMMITTED。 - 误区：SHARED 锁可以升级为 RESERVED，RESERVED 可以直接升级为 EXCLUSIVE。
现实：RESERVED → PENDING → EXCLUSIVE 两步，PENDING 阶段等待已有 SHARED 释放。 - 误区：死锁在 SQLite 中不可能发生。
现实：概率极低但概念上存在，尤其在多个写事务并发且长时间持有 RESERVED 锁时可能发生循环等待。 - 误区：EXCLUSIVE 只能写，不能读。
现实：EXCLUSIVE 是读写独占，持有 EXCLUSIVE 的连接可以正常读取和写入。
4.5 应用边界
| 场景 | 推荐锁控制方法 | 原因 |
|---|---|---|
| 只读查询（报表、分析） | SERIALIZABLE + SHARED 锁 | 避免脏读，简单可靠 |
| 高并发写入（日志写入） | WAL 模式 + SERIALIZABLE | WAL 允许读操作不被写阻塞，减少锁竞争 |
| 临时中间数据（脏读可接受） | PRAGMA read_uncommitted=1 | 减少锁开销，提升读取性能 |
| 多线程频繁短事务 | PRAGMA busy_timeout=5000 | 避免 SQLITE_BUSY 立即失败，给写操作缓冲时间 |
4.6 练习题与实用清单
📘 概念检测
- SQLite 默认的隔离级别是什么？它与 READ UNCOMMITTED 的核心区别是什么？
- 请描述从 UNLOCKED 到 EXCLUSIVE 的完整锁升级过程，写出每步的锁状态名称。
- 为什么 SQLite 中两个写事务同时持有 RESERVED 锁且互相等待时可能形成死锁？写出死锁发生的条件。
- 在命令行中设置 3 秒的忙等待超时，应使用什么 PRAGMA？
📋 实用清单
| 检查项 | 说明 |
|---|---|
| 确认当前连接隔离级别 | PRAGMA read_uncommitted; |
| 配置忙等待超时 | PRAGMA busy_timeout = 5000; |
| 检测锁冲突时重试逻辑 | 捕获 SQLITE_BUSY（5）或 SQLITE_LOCKED（6），回滚后重试 |
| 启用 WAL 模式减少锁竞争 | PRAGMA journal_mode=WAL; |
| 死锁恢复策略 | 设置重试次数上限，避免无限等待 |
📌 代码示例：死锁处理
def safe_execute(conn, sql, retries=3):
for i in range(retries):
try:
conn.execute(sql)
conn.commit()
return
except sqlite3.OperationalError as e:
if "database is locked" in str(e):
time.sleep(0.1)
continue
else:
raise
raise Exception("操作超时，无法完成写入")