SYSTEMS NOTES阅读地图
8个主要章节
1段代码示例
5组图片与图表

从一个具体请求或状态变化出发,追踪数据流与方法成立的条件。

章节速览8 节 · 展开选择

数据库索引解释怎样定位记录。找到同一行以后,两个连接是否会读到同一个值,仍然取决于事务与可见性:另一个连接已经提交,不代表当前读事务立刻更换视图。

本篇只研究同一台机器上的一个新建SQLite数据库、WAL模式、两个独立连接、未启用共享缓存与脏读。我们用一个整数计数器,把读快照、读后写入升级和写者名额分开验证。程序实际调用标准库sqlite3,不用数组模拟数据库;操作按明确顺序交错,不测并行吞吐或等待时长。

先问看见哪一版连接身份之外,还要记录事务边界

同一连接结束旧读事务并重新读取后,结果可能改变。不能只在日志里记连接名与SQL。

再问能否继续写旧快照可读,不保证能升级为写者

WAL允许读者继续看旧视图,但不会允许它依据过期快照分叉写入历史;写入失败需要重新判断事务状态。

固定数据库、连接与快照这三个层次

数据库文件是两连接共享的存储对象,连接是各自的会话入口,事务则界定一段操作的状态与可见性。把连接A重新用于下一次事务,不会让下一次读取天然延续上一份快照。

SQLite官方隔离说明指出,在本文限定的独立连接条件下,一个连接不会读到另一个连接尚未提交的修改;WAL下,活动读事务还会继续看到自己的历史快照,见文献1。这两点分别排除脏读与解释提交后的旧视图,不能缩成一句“所有查询始终返回最新值”。

WAL也不意味着可以有任意多个同时活动的写事务。SQLite仍然只允许一个写者;读写能够重叠,与写写可以并行,是两件事。本文所有后续推演都维持这一限制。

数据库状态v
示例表中唯一一行的整数值,初始为0,没有物理单位,也不代表真实库存或余额。
连接A的读事务
首次实际读取时建立本次读视图;本文随后让它保持活动,直到明确ROLLBACK。
连接B的写事务
获得写者位置后将v改为1并提交。提交完成是操作顺序中的事件,不是某个固定毫秒。

BEGIN之后,何时真正取得读视图

默认的BEGIN DEFERRED并不等于已经开始读取数据库。SQLite事务文档说明,DEFERRED先建立相应控制状态,第一次数据库访问才决定开始读还是写;如果先执行SELECT,就建立读事务,见文献2。

因此“先写了一条BEGIN”与“先完成了一次SELECT”不能互换。若A执行BEGIN以后尚未读取,B就提交了新值,A之后的第一次读取可以看到这个新提交。下面主案例则明确让A先读到0,再让B提交,以消除这个歧义。

可见性约定S1|保持同一份活动读快照
A首次读到v=0 → B提交v=1 → A仍读到v=0
A结束旧读事务 → 新读事务读到v=1

只描述本文WAL、独立连接和这条确定执行顺序;后半行假定没有其他提交插入。箭头不是时长或性能关系。同一活动事务的视图与数据库最新已提交状态要分别记录。

重新执行SELECT不会自动开启另一份快照。如果应用需要看见新的提交,应先明确结束旧事务,再建立新的读取;不能只在同一事务里反复查询,然后把仍返回0解释成索引或缓存故障。

把两个连接的顺序逐行算出来

本例表counter只有id=1这一行。A与B都使用显式SQL控制事务;B先取得写事务、更新并提交,随后A继续读,再尝试写入。

顺序连接A连接B应核对的结果
1BEGIN;SELECT空闲A读到0
2保持读事务BEGIN IMMEDIATE;更新为1;COMMITB的修改已提交
3再次SELECT空闲A仍读到0
4尝试UPDATE空闲升级失败:SQLITE_BUSY_SNAPSHOT
5ROLLBACK;BEGIN;SELECT空闲新读事务看到1
A先读0,B提交1,A旧事务仍读0;A结束旧事务后,新事务读取1
图1|原创确定调度图,列宽不代表耗时。0与1是教学行的状态,图不表示数据库内部页布局,也不是吞吐测量。查看原图
自检1:只看到A先执行BEGIN,能否断言A的快照早于B的提交?

不能。DEFERRED下还要看第一次实际数据库访问何时发生。本例明确让A先完成SELECT并读到0,才执行B的提交;缺少这一顺序就不能照搬时间线。

在第3步,A返回旧值是这个活动读事务的预期行为。若想观察“数据库现在最新的已提交值”,可以在未持有旧快照的连接或新的事务中读取;日志中的问题也必须说清究竟在询问哪一种视图。

为什么旧快照的写入升级会失败

第4步发生时,B已经提交并释放写事务,失败仍可能出现。原因并不是一定还有写者占着位置,而是A试图把一份已落后于数据库历史的读快照升级为写入基础。官方隔离文档与扩展错误码说明对应到SQLITE_BUSY_SNAPSHOT,见文献3。

状态约定S2|读后写入不是无条件升级
旧读快照 + 其他连接已提交新版本
→ 本例UPDATE返回SQLITE_BUSY_SNAPSHOT
→ 结束旧事务后,重新读取并判断业务条件

这是指定调度下的状态关系,不是所有写入失败的统一解释。应用应读取错误身份并检查事务状态;本篇不提供对任意错误自动回滚、重试或恢复的通用算法。

仅仅把等待时间拉长,不会把A正在持有的旧快照变成新快照。正确的后续动作需要重新建立业务前提:结束旧事务、重新读取,在新状态下决定是否还应执行原操作。若此前已经向外部系统产生副作用,还要另外处理重试的重复执行问题,可对照总预算与安全重放。

不要由这个案例推断任意SQL错误后事务都自动结束。SQLite对不同错误有不同处理规则;示例在预期错误后检查in_transaction并明确结束当前教学事务,避免后面的实验借用一个不清楚的状态。

BEGIN IMMEDIATE把写者竞争提前到哪里

当A执行BEGIN IMMEDIATE并取得写事务时,B再尝试同样操作可能得到SQLITE_BUSY。本文把连接的等待时间设为0,目的是立即观察这个拒绝,不比较谁等待得更久。

这与旧快照升级失败的情境不同:一个是在已经落后的读视图上尝试升级,一个是在写者位置已被占用时尝试开始写事务。它们都可能落在busy类别,却需要保留具体错误与操作阶段,不能只统计一个笼统的数据库忙次数。

一种失败来自旧快照升级,另一种来自已有活动写者;两者应分别记录错误身份与事务阶段
图2|原创边界对照,不表示锁的内部实现或重试成功率。WAL保留单写者限制,提前获得写者位置也不等于业务条件已经正确。查看原图
自检2:B已经提交,A的UPDATE还报忙,是否就能认定数据库没有释放锁?

不能。本文旧快照情境可以在B提交后触发SQLITE_BUSY_SNAPSHOT。应核对扩展错误和A仍持有的读事务,而不是把所有失败都解释成另一个写者尚未退出。

提前取得写事务可以让某些竞争更早显现,但也可能让写者位置被更久占用。选择事务模式需要结合实际读写步骤与持有时间,本文没有据此推荐把所有查询统一改为IMMEDIATE。

完整Python:两个真实连接,逐阶段核对

程序在当前目录里创建新的随机命名教学目录,数据库与产物保留。请使用Python 3.11;程序使用该版本sqlite3提供的错误码属性与显式事务方式,参考文献4。isolation_level=None在这里用于关闭Python包装层的隐式开事务,由代码里的BEGIN、COMMIT、ROLLBACK明确控制边界。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
from pathlib import Path
from tempfile import mkdtemp
import sqlite3


def scalar(conn, sql):
cursor = conn.execute(sql)
try:
return cursor.fetchone()[0]
finally:
cursor.close()


def main():
folder = Path(mkdtemp(prefix="sqlite-snapshot-", dir=".")).resolve()
database = folder / "demo.db"
a = sqlite3.connect(database, isolation_level=None, timeout=0)
b = None
try:
if scalar(a, "PRAGMA journal_mode=WAL").lower() != "wal":
raise RuntimeError("WAL required")
a.execute("CREATE TABLE counter(id INTEGER PRIMARY KEY, v INTEGER)").close()
a.execute("INSERT INTO counter VALUES (1, 0)").close()
b = sqlite3.connect(database, isolation_level=None, timeout=0)
a.execute("BEGIN").close()
print("first=" + str(scalar(a, "SELECT v FROM counter WHERE id=1")))
b.execute("BEGIN IMMEDIATE").close()
b.execute("UPDATE counter SET v=1 WHERE id=1").close()
b.execute("COMMIT").close()
print("writer_committed=" + str(scalar(b, "SELECT v FROM counter WHERE id=1")))
print("same_transaction=" + str(scalar(a, "SELECT v FROM counter WHERE id=1")))
try:
a.execute("UPDATE counter SET v=v+10 WHERE id=1").close()
except sqlite3.OperationalError as error:
if error.sqlite_errorcode != sqlite3.SQLITE_BUSY_SNAPSHOT:
raise
print("upgrade=" + error.sqlite_errorname)
else:
raise AssertionError("stale snapshot unexpectedly upgraded")
finally:
if a.in_transaction:
a.execute("ROLLBACK").close()
a.execute("BEGIN").close()
print("new_transaction=" + str(scalar(a, "SELECT v FROM counter WHERE id=1")))
a.execute("ROLLBACK").close()
a.execute("BEGIN IMMEDIATE").close()
try:
b.execute("BEGIN IMMEDIATE").close()
except sqlite3.OperationalError as error:
if error.sqlite_errorcode != sqlite3.SQLITE_BUSY:
raise
print("second_writer=" + error.sqlite_errorname)
else:
raise AssertionError("two active writers unexpectedly admitted")
finally:
if b.in_transaction:
b.execute("ROLLBACK").close()
if a.in_transaction:
a.execute("ROLLBACK").close()
print("final=" + str(scalar(b, "SELECT v FROM counter WHERE id=1")))
print("database=" + str(database))
finally:
if b is not None:
b.close()
a.close()


if __name__ == "__main__":
main()

这个程序刻意关闭等待,按确定顺序安排两个连接。它验证SQL可见性、错误身份与事务边界,不证明真实应用在高负载下的延迟、吞吐或重试成本。运行时SQLite版本应从sqlite3.sqlite_version另外记录,不能把Python版本当成数据库库版本。

SQL执行成功与业务条件满足还要分开

若业务要求一个非负计数减1,可以把条件写进同一条更新,例如UPDATE stock SET qty=qty-1 WHERE id=? AND qty>0。随后检查实际受影响行数:0行可能表示记录不存在或条件不成立,不能仅凭没有抛SQL异常,就报告业务成功。

这条语句只示范单行条件更新,不是完整库存服务。多表不变量、外部消息、请求去重与失败后的业务决策,仍需明确事务边界。读到旧状态以后在应用中先算一个新值,再无条件覆盖数据库,和由数据库在更新时判断当前条件,是不同的并发契约。

SQLite的WAL还需要相应文件与共享内存协调条件。不要把本机教学数据库的行为直接推广到任意网络文件系统或跨机器共享,运行环境边界见文献5。本文不提供修改生产数据库模式的操作步骤。

自检3:WAL允许读写重叠,是否也允许两个活动写事务同时修改?

不允许。SQLite维持单写者限制。本文A持有IMMEDIATE写事务时,等待时间为0的B会在开始IMMEDIATE时得到SQLITE_BUSY;这与读快照能继续存在并不矛盾。

自检4:条件UPDATE返回0行,没有异常,能否说扣减成功?

不能。SQL执行成功与WHERE条件选中了目标行是不同结果。应按业务契约检查受影响行数,并区分记录不存在、条件不成立及其他失败,而不是把没有异常当作成功确认。

参考文献与验证范围

  1. SQLite:Isolation In SQLite。 连接隔离、WAL快照与升级案例。用于本文独立连接、活动读事务及提交可见性边界。返回S1。
  2. SQLite:Transaction。 DEFERRED、IMMEDIATE与事务控制。用于首次访问、读写升级和单写者限制;错误后状态应按具体情况检查。
  3. SQLite:Result and Error Codes。 SQLITE_BUSY_SNAPSHOT。用于区分快照升级失败与一般busy类别。返回S2。
  4. Python 3.11:sqlite3。 事务控制与错误属性。本文使用显式SQL事务,不依赖后来版本新增的连接参数。
  5. SQLite:Write-Ahead Logging。 WAL运行环境与限制。核对同机协调等环境条件;本文程序只验证新教学数据库的确定调度,没有硬件、吞吐或生产系统评测。