SQLite 有写锁，为什么还会丢失更新？用 Python 实测版本号乐观锁

一个计数器初始为 100。请求 A 想增加 10，请求 B 想增加 20，最后应该是 130。如果两个请求先各自读到 100，再把计算结果写回，数据库完全可能得到 120。两次更新都成功了，A 的增量却消失了。

这不是 SQLite 把写入的字节弄丢了，而是应用把过期的业务判断写了回去。下面只用 Python 标准库做一次本机实验，把错误、冲突检测和恢复过程放在同一份可运行代码里。

一、先约定：模拟交错，不做并发压测

实验使用同一个临时 SQLite 文件和两个独立连接，分别代表请求 A、B。程序刻意安排顺序：A 读，B 读，A 写并提交，B 写并提交。两个读取事务在首次写入前都已结束，旧值保留在 Python 变量中。

这叫确定性交错：用顺序操作复现并发业务中可能发生的一种时序。没有线程，没有随机等待，也没有请求吞吐量数据。它能稳定验证陈旧读后覆盖的逻辑问题，不能证明真实高并发下的等待时间、异常比例或性能。

初始状态是 value=100, version=0。A 的意图是“在当前值上增加 10”，B 的意图是“在当前值上增加 20”。如果应用把意图提前变成固定结果，A 会写 110，B 会写 120；B 成功落库时，覆盖的是 A 已经提交的 110。

可以把两个 Python 元组理解为两张已经加载完成的编辑页面。页面加载时的读取已经结束，用户随后提交表单，是一次新的数据库交互。即使浏览器没有真的同时发送两个写请求，只要页面持有旧数据，这个问题就可能出现。因此实验关注的是读取依据与写入时刻之间的状态变化，而不是让两个写事务在同一瞬间执行。

二、为什么写锁没有替应用判断过期

SQLite 会协调数据库写事务，避免多个写者任意交叉修改文件。但它不知道 Python 变量中的 100 是刚刚读取的，还是用户十分钟前打开页面时看到的。

本例每次写入都在独立的 BEGIN IMMEDIATE 事务里完成：取得写事务，执行 UPDATE，提交，再轮到另一个连接。写锁按顺序正常工作，B 提交的 120 也完全符合它那条 SQL。缺失的是“只能在我读取的状态仍有效时更新”这一业务前提。

不要把这个例子改成两个连接一直持有读取事务，再假定一定还能重现相同输出。活跃读事务向写事务升级时，可能遇到锁竞争；WAL 模式下还可能遇到快照升级相关的忙错误。那涉及另一组事务与锁规则，不能拿来代替这里的陈旧变量问题。

乐观锁把业务前提放进 UPDATE：主键相同，并且版本号仍等于读取时的版本，才允许写入。检查和修改必须由同一条语句完成，先 SELECT 检查版本、再无条件 UPDATE 仍会留下竞态窗口。

三、完整代码：普通覆盖、条件更新与恢复

把下面代码保存为 sqlite-lost-update-demo.py。需要 Python 3，所用模块都来自标准库；程序自动建立并清理临时数据库，不需要安装数据库服务，也不读写业务库。

连接使用 isolation_level=None 关闭隐式开启事务的行为，由代码显式 BEGIN。with db 在正常退出时提交，异常退出时回滚；它不会关闭连接，因此外层另外使用 closing。读取函数退出后，数据库事务结束，返回的元组仍然留在内存中。

"""Deterministic stale-snapshot demo; Python standard library only."""
import platform
import sqlite3
from contextlib import closing
from pathlib import Path
from tempfile import TemporaryDirectory


def snapshot(db):
    with db:
        db.execute("BEGIN")
        row = db.execute(
            "SELECT value, version FROM counter WHERE id = 1"
        ).fetchone()
    assert not db.in_transaction
    return row


def write(db, sql, params):
    with db:
        db.execute("BEGIN IMMEDIATE")
        changed = db.execute(sql, params).rowcount
    assert not db.in_transaction
    return changed


def reset(db):
    rows = write(db, "UPDATE counter SET value = 100, version = 0", ())
    assert rows == 1


def blind_add(db, old, delta):
    return write(
        db,
        "UPDATE counter SET value = ?, version = version + 1 WHERE id = 1",
        (old[0] + delta,),
    )


def cas_add(db, old, delta):
    return write(
        db,
        "UPDATE counter SET value = ?, version = version + 1 "
        "WHERE id = 1 AND version = ?",
        (old[0] + delta, old[1]),
    )


def main():
    print(f"Python {platform.python_version()} | SQLite {sqlite3.sqlite_version}")
    with TemporaryDirectory(prefix="sqlite-occ-") as folder:
        path = str(Path(folder) / "demo.sqlite3")
        with closing(sqlite3.connect(path, isolation_level=None)) as a, \
                closing(sqlite3.connect(path, isolation_level=None)) as b:
            a.execute(
                "CREATE TABLE counter (id INTEGER PRIMARY KEY, "
                "value INTEGER NOT NULL, version INTEGER NOT NULL)"
            )
            a.execute("INSERT INTO counter VALUES (1, 100, 0)")

            # Both read transactions finish before either write begins.
            old_a, old_b = snapshot(a), snapshot(b)
            assert old_a == old_b == (100, 0)
            print(f"blind snapshots: A={old_a}, B={old_b}")
            rows_a = blind_add(a, old_a, 10)
            rows_b = blind_add(b, old_b, 20)
            final = snapshot(a)
            assert (rows_a, rows_b, final) == (1, 1, (120, 2))
            print(f"blind: rows=({rows_a}, {rows_b}), final={final}, expected_value=130")

            reset(a)
            old_a, old_b = snapshot(a), snapshot(b)
            assert old_a == old_b == (100, 0)
            print(f"CAS snapshots: A={old_a}, B={old_b}")
            rows_a = cas_add(a, old_a, 10)
            rows_b = cas_add(b, old_b, 20)
            after_conflict = snapshot(a)
            assert (rows_a, rows_b, after_conflict) == (1, 0, (110, 1))
            print(f"CAS: rows=({rows_a}, {rows_b}), after_conflict={after_conflict}")

            # Retry this pure increment once, using a fresh business snapshot.
            fresh_b = snapshot(b)
            rows_retry = cas_add(b, fresh_b, 20)
            final = snapshot(a)
            assert (fresh_b, rows_retry, final) == ((110, 1), 1, (130, 2))
            print(f"retry: fresh={fresh_b}, rows={rows_retry}, final={final}")
    print("PASS: lost update, version conflict, and recomputation verified")


if __name__ == "__main__":
    main()

在源码所在目录执行，保留断言，不要添加 -O：

python sqlite-lost-update-demo.py

每一阶段都有断言，检查的不只是最终数值，还包括成功更新的行数、版本号和读取事务已经结束。任何一个条件不符合预期，程序都会中止并报错，而不会继续打印成功结论。重复运行会创建新的临时数据库，前一次结果不会污染下一次实验。

本机实际运行环境为 Python 3.12.14、SQLite 3.53.1。完整标准输出如下，换环境后首行版本号可能不同，其余结果应一致：

Python 3.12.14 | SQLite 3.53.1
blind snapshots: A=(100, 0), B=(100, 0)
blind: rows=(1, 1), final=(120, 2), expected_value=130
CAS snapshots: A=(100, 0), B=(100, 0)
CAS: rows=(1, 0), after_conflict=(110, 1)
retry: fresh=(110, 1), rows=1, final=(130, 2)
PASS: lost update, version conflict, and recomputation verified

四、读懂 rowcount：写成功不等于业务正确

第一组中，A、B 都读到 (100, 0)。普通覆盖虽然也递增版本号，却没有在 WHERE 中检查旧版本。A 写 110 后，B 仍用自己的 100 加 20 写入 120。最终版本是 2，只说明执行了两次版本递增，不能证明两份业务意图都得到保留。

rowcount 来自刚执行 UPDATE 的游标。普通覆盖的两个值都是 1，表示各有一行被这条语句更新。它不会替你比较“本应是 130”与“实际是 120”，因此不能单凭受影响行数判断有没有丢失更新。

第二组重新初始化数据。A 带着旧版本 0 更新成功，行变成 (110, 1)；B 仍要求版本等于 0，于是匹配不到，rowcount=0。这次 B 没有覆盖 A，版本也没有变成 2。版本条件让原本悄悄发生的错误变成应用能够处理的失败。

这里主键为 1 的行始终存在，所以可以将 0 判为版本冲突。一般业务中，记录被删除、租户条件不符等情况也可能导致 0，必须结合接口约定区分。rowcount=1 还不代表整个业务事务最终提交成功；本例把提交放在返回之前，提交失败会抛异常，不会正常返回行数。

代码把版本的比较与递增放在同一条语句中，并且用占位符传递读取时的值。这种比较后更新常被称为 CAS。版本最好作为应用管理的独立整数，而不是借用精度不足、可能相同的更新时间。请求提交旧版本，服务端负责条件更新和递增；不能让客户端随意指定新的版本值，也不能只在界面上提示版本冲突而仍然无条件写库。

五、恢复要重读并重算，不能只换版本号

检测到冲突并不会自动合并结果。B 必须重新读取 (110, 1)，保留原始意图“增加 20”，重新计算 130，再带版本 1 更新。成功后得到 (130, 2)，两次增量都保留下来。

如果只是把旧结果 120 配上新版本 1 再提交，A 的增量仍会丢失。版本号提供的是状态前提，不是让旧计算结果重新变正确的许可证。

即使重读完成，其他请求也可能在下一次写入前抢先提交。因此重读后的 UPDATE 仍然必须检查版本，应用也必须检查新的行数。本例按固定顺序只重试一次并断言成功，没有演示无限重试或自动调度。

六、重试边界：业务意图不一定能自动重放

纯内存加法没有外部副作用，所以这里可以重新计算。真实操作如果已经发邮件、扣款、调用远端接口，重复整个业务函数可能把副作用执行第二次。一次数据库版本冲突，不能推导出外部动作也失败了。

需要重试时，应先明确哪些步骤可以重放，对外部请求使用适当的幂等机制；需要可靠传递事件时，再设计事务消息或 outbox 等方案。不要把这些机制简化成“数据库失败就再执行一次”。如果数据库提交后的响应丢失，调用方甚至无法仅凭网络异常判断是否已提交。

还要区分失败类型：条件 UPDATE 正常执行但行数为 0，是业务前提不成立；database is locked 一类异常涉及锁等待或事务竞争。忙等待超时只影响取得锁的机会，不会让旧业务值变新。两类问题需要各自的处理策略。

对于人工编辑，冲突后直接自动重算可能违背用户意图。更合适的行为可能是展示新旧内容、请用户合并；对于允许自动重算的操作，也要限制次数和总时长，持续冲突时明确返回失败。

七、带到生产之前，还要守住哪些条件

如果需求只是对单行做独立加法，直接使用 UPDATE counter SET value = value + ?, version = version + 1 WHERE id = ? 往往更直接：让数据库基于执行时的值计算，免去一次应用读后写。乐观锁适合必须依赖已读状态完成业务判断、又允许冲突返回的流程。

所有相关写入入口都要遵守版本协议。后台脚本或批处理改了业务字段却不递增版本，会让陈旧请求错误地通过检查；删除后以相同主键重新创建并重置版本，也需要额外的身份或版本策略。实验里的 reset 只是隔离两组案例，不能照搬成生产重置机制。

多行约束需要在明确的事务范围内一起维护，单行版本不能自动保护跨行余额、库存总量等不变量。是否采用 WAL、如何设置超时、是否需要提前取得写锁，都应结合实际读写模式验证；本例没有测这些配置的优劣，也没有运行云端实验。

另一种设计是从读取之前就开启写事务，让读取、判断和写入处于同一个受保护的短事务内。这可能适合完全由服务端快速完成的操作，但会延长锁占用，不能把用户思考时间或慢速远端调用一起包进去。选择事务内串行还是事务外计算后校验，需要同时考虑冲突率、锁等待与业务交互时长。乐观锁检测状态变化，本身并不减少写锁需求。

这份实验验证的结论很具体：写事务可以完全串行，应用仍然可能用陈旧结果覆盖新结果；同一条 UPDATE 中的版本条件能检测这里的过期状态；冲突后保留原始意图，重新读取、重新计算并再次校验，才能在这个场景中得到 130。