锁与 MVCC

环境：Windows 11 x64，SQLite 3.53.1（Python 3.13.1），探针 %TEMP%/db3l_*/probe_mvcc.py，WAL 模式。语义结论可复现，耗时是中位数。本机没有 MySQL：InnoDB 的行锁、间隙锁、next-key locking、SHOW ENGINE INNODB STATUS 死锁日志、innodb_lock_wait_timeout全部未实测——本文用 SQLite 侧的真实测量讲清”快照读/当前读”和”写者排队”两个通用概念，MySQL 特有的锁类型只在第七节做文档级对照。

一、两种并发控制的路线，先分清楚

数据库解决”读写并发”有两条路线，选哪条决定了你会看到什么现象：

路线	做法	代表	写-写行为	读-写行为
锁（2PL）	读写都加锁，靠锁互斥	MySQL InnoDB（行锁+间隙锁）	等待或死锁	读写互斥（MVCC 出现前）
MVCC	写者复制新版本，读者读旧版本	Postgres、MySQL InnoDB、SQLite WAL	仍互斥（单写者/行锁）	不互斥

SQLite WAL 是 MVCC 路线：写者往 -wal 追加新页，读者拿”读到第几帧”的快照标记。所以本文第一节先讲在 SQLite 里能测到什么，第七节再讲 InnoDB 比它多出来的锁类型。

二、快照读：长事务的世界是冻结的（实测）

WAL 模式下，读者事务一旦开始，它看到的数据就定型了。探针：读者先读到 v=0，不结束事务；写者连续提交三版（10、20、30）：

读者事务开始读到 v=0；写者连提 3 版(10/20/30)后，同一读者事务再读 v=0 → 快照冻结
读者事务结束后新连接读到 v=30

这就是 MVCC 的核心承诺：读者永远不被写者阻塞，也永远看不到中间状态。三个工程含义：

长读事务 = 长快照。报表跑了 5 分钟，这 5 分钟里别人提交的新数据它一概看不到。这是特性（数据一致）也是陷阱（“我明明改了怎么不生效”）。
WAL 文件不能随便 checkpoint 掉读者还在用的帧。所以长事务会间接导致 WAL 膨胀——第五节量了这个。
“读己之写”在跨事务时失效：连接 A 提交后，连接 B 若还挂着旧快照事务，读不到 A 的提交。debug 这类 bug 的抓手就是”两边是不是不在同一个事务里”。

三、当前读：自动提交的每条语句都看最新

和快照读相对的是当前读——不开事务（自动提交）的每条语句，看到的是”执行那一刻”的最新已提交数据：

写未提交时自动提交读: 126        ← 旧值，没看到未提交的 555
写提交后自动提交读: 555          ← 立刻看到

126 这个数字还顺带证明了无脏读：写者的 555 在 COMMIT 前任何 reader 都看不到。SQLite 没有”READ UNCOMMITTED”档，这是模型保证的。

选型规则：要一致用快照（显式 BEGIN），要新鲜用当前读（自动提交单语句）。中间状态最危险——循环里一会儿自动提交一会儿开事务，同一份代码前后读到不同的世界，且难以复现。

四、写-写：SQLite 的答案是没有锁竞争，只有排队

MySQL InnoDB 两个写者抢同一行：一个等、一个占，等超时的报 Lock wait timeout，互相绕成环的报死锁回滚。SQLite 的答案更简单——任何时刻只有一个写者存在：

a.execute("BEGIN IMMEDIATE"); a.execute("UPDATE kv SET v=100")
b 在 109 ms 后失败: database is locked

b 连”竞争行锁”的机会都没有，它在拿库级写锁时就出局了。这个模型的后果全部是正向的：

没有死锁。死锁需要”各自持有对方要的锁并互相等待”，单写者下这个循环不可能形成。SQLite 应用层永远不需要写死锁检测、不需要按固定顺序加锁的约定。
没有锁升级/锁降级的复杂度。没有行锁、页锁、表锁的转换，没有 innodb_lock_wait_timeout 这类旋钮。
写吞吐的天花板是单连接的速度。20 个写者排队打同一行，实测：

 1 个写者串行: 单次中位  3.21 ms, 合计    3.2 ms
 5 个写者串行: 单次中位  2.43 ms, 合计   13.0 ms
20 个写者串行: 单次中位  2.54 ms, 合计   53.4 ms

单次 2.4–3.2 ms 不随排队深度恶化（这是 WAL + fsync 一次的代价），总时间线性涨。换成 InnoDB，20 个写者抢同一行会看到锁等待、上下文切换和可能的死锁——代价结构完全不同。所以”SQLite 写慢”的准确说法是”SQLite 写是串行的”，串行有串行的优化法（见第六节），不是把行锁调优搬过来。

五、WAL 膨胀：长事务的间接代价（实测）

MVCC 的账最终记在 WAL 文件上。探针连续测量：

当前 -wal 大小: 148352 B
批量插 2 万行后 -wal: 778712 B（主库 4096 B）
TRUNCATE checkpoint 后 -wal: 0 B（主库 626688 B）
wal_autocheckpoint 默认 1000 页

三个事实：

WAL 只增不减，直到 checkpoint 把帧合回主库。默认 1000 页自动触发，但有长读事务时自动 checkpoint 会被跳过（合不动的帧还有人要读）——这时 WAL 会一直涨，磁盘莫名其妙少了几百 MB，元凶往往是一个忘了结束的报表事务。
PRAGMA wal_checkpoint(TRUNCATE) 手动收口，实测把 778 KB 直接归零，主库长到 626,688 B。批量导入后主动调一次，是控制磁盘的正规动作。
** checkpoint 期间读者仍可读、写者仍可写**（被动式 checkpoint 不阻塞），这条是 WAL 相对 journal 模式的又一个结构性优势。

六、热点行的正确拆法（单写者模型下）

“高并发更新热点”在 MySQL 里的解法是拆分（把一行计数器拆成 N 行，各加各的，读时求和）或换存储（Redis INCR）。在 SQLite 单写者模型下，拆分一行的收益结构不同——串行化已经发生了，拆分行省的是”每次提交的 fsync 次数”，不是锁竞争。三条按收益排序的做法：

合并提交：把 N 次 UPDATE 计数+1 攒成一次 UPDATE 计数+N。fsync 从 N 次变 1 次，这是数量级收益（db-optimize 第五节实测 457 倍）。计数器场景用一个累加器批量落库即可。
写路径与读路径分离：读多写少的计数（浏览量、点赞数），写进内存/队列、异步批量落库，读直接给内存值。SQLite 只当持久层，不当实时计数层。
按 key 分库文件：确有多写者且都是热 key，按用户/租户拆成多个 .db，每个库仍是单写者，但库间并行。这是 SQLite 的”分片”，代价是跨库查询和备份要在应用层拼。

反过来，不要把这些做法搬过来：等锁超时重试、锁排序防死锁、SELECT FOR UPDATE——单写者模型下它们要么无效要么有害。

七、MySQL InnoDB 侧对照（未实测）

以下为文档语义，本机无实例，用到时回官方文档核对：

概念	InnoDB 行为	SQLite WAL 对照
行锁	`UPDATE` 给索引记录加 X 锁	无（库级写锁）
间隙锁 gap lock	范围条件锁住记录之间的空隙，防幻读	无（快照本身防幻读）
next-key lock	行锁 + 间隙锁，RR 级别默认	无
锁等待	`innodb_lock_wait_timeout`（默认 50s）后报 1205	`busy_timeout`（粒度 100ms 级，db-sqlite 第四节实测）
死锁	可能发生，`SHOW ENGINE INNODB STATUS` 看环，自动回滚代价小的事务	不可能
快照读	普通 `SELECT`，RR 下读事务首条语句定型	`BEGIN` 后首条读定型（第二节实测）
当前读	`SELECT ... FOR UPDATE` / `LOCK IN SHARE MODE`	无对应语法（写即当前读）
MVCC 版本	undo log 里的历史版本	`-wal` 里的新帧

迁移时最常踩的坑：把 SELECT ... FOR UPDATE 搬到 SQLite——它不是报错，是被静默忽略（SQLite 不认识这个后缀的语义但语法上 FOR UPDATE 会直接语法错误，实际上多数驱动会报错；真正的坑是反过来：依赖 RR 快照的代码在 SQLite 里行为一致，依赖”当前读看到最新”的代码必须改成自动提交单语句）。以及：SQLite 没有死锁，所以从 MySQL 搬过来的”统一加锁顺序”规范可以退役，但”事务要短”的规范要加码——长事务在 SQLite 下挡的是 WAL 回收（第五节）。