隔离级别挡住什么，挡不住什么

目标

在真正的 PostgreSQL 中亲手制造阅读材料表格第四列所说的写偏差，也就是任何隔离级别都无法自动防住的问题。然后依次使用三种防范工具：行锁、可串行化隔离级别，以及为任务队列跳过已锁记录。

为什么重要

遇到并发错误时，最先出现的建议往往是‘提高隔离级别’。本实验会用数字展示这种方法何时有效、何时无效。第 2 步中丢失的 100 元会在第 4 步变成错误，而到了第 5 步，规则甚至会在没有任何错误的情况下被破坏。

尤其值得注意的是，同样的 900 会出现两次。一次是因为更新丢失，另一次是因为更新被拒绝。仅看数值无法区分，只有是否收到错误才能说明是哪一种。在生产环境中，这一区别决定了事故是会被报告，还是永远无人知晓。

环境

这个 Pod 内运行着 PostgreSQL 16。连接方式如下。

export PGPASSWORD=lab
psql -X -q -v ON_ERROR_STOP=1 -h 127.0.0.1 -U lab -d labdb

**本实验需要两个会话重叠执行。**因为只有一个终端，所以将其中一个放到后台运行。在事务中加入 SELECT pg_sleep(3);，就能让该会话保持事务打开并等待。

psql ... -f /root/txn/lost.sql > /root/txn/lost_a.out 2>&1 &
sleep 1
psql ... -f /root/txn/lost.sql > /root/txn/lost_b.out 2>&1
wait

所有产物都放在 /root/txn/ 下。不要修改种子表（customers 等）。

步骤

使用 /root/txn/schema.sql 创建并填充 purse、oncall、tasks 三张表。
使用 /root/txn/lost.sql 在账户 1 上重现更新丢失，并保存到 /root/txn/02-lost.txt。
使用 /root/txn/forupdate.sql 通过行锁保护账户 2，并保存到 /root/txn/03-forupdate.txt。
使用 /root/txn/repeatable.sql 在账户 3 上收到串行化错误，并保存到 /root/txn/04-repeatable.txt。
使用 /root/txn/skew.sql 破坏 rr 团队的值班规则，并保存到 /root/txn/05-skew.txt。
使用 /root/txn/serializable.sql 保护 ssi 团队的规则，并保存到 /root/txn/06-serializable.txt。
使用 /root/txn/claim.sql 让两个 worker 分配队列任务，并保存到 /root/txn/07-queue.txt。
总结所学内容并保存到 /root/txn/08-notes.md。

提示

可以用 \dt 查看表列表，用 \d purse 查看列结构。
SELECT balance AS b FROM purse WHERE id = 1 \gset 会把查询结果存入 psql 变量 b，之后用 :b 引用。
若要让错误同时显示 SQLSTATE，请在文件开头加入 \set VERBOSITY verbose。
每个步骤开始时，都要把目标账户或团队恢复为初始值。这样无论重做多少次，结果都相同。
常见错误 1：写成 UPDATE purse SET balance = balance - 100。这样数据库会读取最新值进行计算，无法重现丢失。必须使用 :b - 100，直接写入之前读到的值。
常见错误 2：两个会话的启动间隔长于等待时间。如果第二个会话读取时第一个会话已经提交，就什么也不会发生。

创建三张实验用表

创建并执行 /root/txn/schema.sql。向 purse(id, owner, balance) 插入三个余额为 1000 的账户（id 1、2、3）；向 oncall(id, team, name, on_duty) 插入 rr 团队两人（id 1、2）和 ssi 团队两人（id 3、4），全部设为值班；向 tasks(id, state, worker) 插入六条 queued 状态的任务（id 1～6）。

连接方式是 psql -h 127.0.0.1 -U lab -d labdb，密码为 lab。先执行 export PGPASSWORD=lab，就无需每次输入密码。

请以 DROP TABLE IF EXISTS 开头，确保多次执行后仍得到相同状态。后续步骤会不断重置并复用这些表，因此幂等性很重要。

六条任务可以用 INSERT ... SELECT g, 'queued', null FROM generate_series(1, 6) g 一次插入。

重现更新丢失

创建 /root/txn/lost.sql，让两个重叠的会话分别运行从账户 1 扣除 100 的事务。先读取余额，等待 3 秒，然后写入从读取值减去 100 后得到的绝对值。在 /root/txn/02-lost.txt 中用 before、after、expected 三行记录结果。

psql 可以用 SELECT balance AS b FROM purse WHERE id = 1 \gset 将查询结果存入变量，之后通过 :b 使用。SELECT pg_sleep(3); 会让事务保持打开并等待 3 秒。

将一个会话放到后台运行，1 秒后再运行第二个会话，就能让两者重叠。二者都在提交前读取 1000，因此都会写入 900。虽然取款两次，余额却只减少了一次。

运行前请把账户 1 恢复为 1000。这样无论重做多少次，结果都相同。

用 SELECT ... FOR UPDATE 防止丢失

创建 /root/txn/forupdate.sql。流程与第 2 步相同，但读取余额时使用 FOR UPDATE 锁定该行。让两个会话在账户 2 上重叠运行，并在 /root/txn/03-forupdate.txt 中用 before、after、expected 三行记录结果。

通过 SELECT balance AS b FROM purse WHERE id = 2 FOR UPDATE \gset 读取时，该行会被锁定。第二个会话会在这条查询处等待第一个会话提交，醒来后读取到的是更新后的值。

这就是它被称为悲观锁的原因：预先假定会发生冲突，让操作排队。代价是产生等待，而且如果以不同顺序锁定多个行，还会发生死锁。

REPEATABLE READ 通过报错来阻止冲突

创建 /root/txn/repeatable.sql。用 BEGIN ISOLATION LEVEL REPEATABLE READ; 开始与第 2 步相同的流程，让两个会话在账户 3 上重叠运行。在 /root/txn/04-repeatable.txt 中用 sqlstate、after、expected 三行记录后写入会话收到的 SQLSTATE 与最终余额。

psql 默认不会在错误消息中显示 SQLSTATE。在文件开头加入 \set VERBOSITY verbose 后，就会像 ERROR: 40001: ... 一样同时显示代码。

这里的余额是 900。虽然数字与第 2 步相同，含义却完全相反：第 2 步的 900 是一次取款悄无声息地消失了，而第 4 步的 900 是一次取款被拒绝了。被拒绝的一方可以重试，丢失的一方却连重试的机会都没有。

REPEATABLE READ 也无法阻止的问题

创建 /root/txn/skew.sql。这个事务先检查 rr 团队的值班人数是否大于一，再把自己移出值班，隔离级别为 REPEATABLE READ。让两个会话分别以 id 1 和 id 2 重叠运行，在 /root/txn/05-skew.txt 中用 isolation、on_duty 两行记录剩余值班人数。

要在 psql 内衔接条件判断与更新，可以用 SELECT (count(*) > 1)::text AS ok ... \gset 将布尔值存入变量，再用 \if :ok 和 \endif 包住操作。自己的编号可以像 psql -v me=1 一样传入，并在 SQL 中通过 :me 使用。

两个会话修改的是不同的行。写入没有重叠，因此快照隔离没有依据来检测冲突。两者都会成功，值班人数变为 0。这就是依据读取结果去修改另一行时发生的写偏差。

用 SERIALIZABLE 捕获写偏差

创建 /root/txn/serializable.sql。用 BEGIN ISOLATION LEVEL SERIALIZABLE; 开始与第 5 步相同的流程，目标是 ssi 团队（id 3 和 4）。在 /root/txn/06-serializable.txt 中用 sqlstate、on_duty 两行记录其中一方收到的 SQLSTATE 与剩余值班人数。

SERIALIZABLE 不仅检查写冲突，还会追踪读取与写入之间的依赖关系。它能发现两个事务彼此修改了对方读取的集合，并回滚其中一个。

复制第 5 步的文件，只需修改隔离级别、团队名和自己的编号。这里也需要 \set VERBOSITY verbose 才能看到 SQLSTATE。

代价是产生错误。一旦决定使用这个隔离级别，应用程序就必须具备重试机制。

用 SKIP LOCKED 分配任务

创建 /root/txn/claim.sql。这个事务按 id 顺序选择三条 queued 状态任务，用 FOR UPDATE SKIP LOCKED 锁定，将其改为 running，并在 worker 中写入自己的名称。让 worker-a 和 worker-b 两个会话重叠运行，领取全部六条任务，并在 /root/txn/07-queue.txt 中用 worker_a、worker_b、unclaimed 三行记录结果。

要在一条语句中包含待选择行与待修改行，使用 CTE 很方便。

WITH picked AS (
  SELECT id FROM tasks WHERE state = 'queued' ORDER BY id
  FOR UPDATE SKIP LOCKED LIMIT 3
)
UPDATE tasks t SET state = 'running', worker = :'me'
FROM picked WHERE t.id = picked.id;

:'me' 会把 psql 变量作为字符串字面量插入。没有 SKIP LOCKED 时，第二个 worker 会在第一个 worker 锁定的行前排队，醒来后又看到已经处理过的行。

总结什么阻止了什么

在 /root/txn/08-notes.md 中至少写四行。说明为什么第 2 步和第 4 步的余额同为 900、含义却不同，为什么第 5 步中的 REPEATABLE READ 无法阻止问题，以及分别应在什么场景选择锁和隔离级别。

正文中必须包含 쓰기 편향、FOR UPDATE、SKIP LOCKED。

尤其要认真总结第 5 步。实际工作中的并发事故，大多发生在表格中没有列出的第四列。