开两个会话去撞一撞

目标

亲手创建并修复单独运行正确、但两个任务同时运行就出错的代码。并发问题之所以困难，是因为很难复现。因此，本实验的所有步骤都会真正启动两个会话，让它们发生冲突。

环境

PostgreSQL 16 已经运行（5432，数据库 labdb，用户 lab）。

export PATH=/usr/lib/postgresql/16/bin:$PATH
psql -h 127.0.0.1 -U lab -d labdb

创建两个会话的方法

让一侧在后台运行，同时操作另一侧。

psql -h 127.0.0.1 -U lab -d labdb   -c "begin; select * from stock where id=1 for update; select pg_sleep(15);" &
sleep 1
# 여기서 두 번째 세션 작업
wait

准备

create table stock(id int primary key, qty int not null);
insert into stock values (1,10),(2,10);

步骤

制造锁等待 → 01-block.txt
lock_timeout → 02-timeout.txt
故意制造死锁 → 03-deadlock.txt
统一顺序以消除死锁 → 04-order.txt
并发扣减 20 次仍不出现负数 → 05-stock.txt
使用 skip locked 构建工作队列 → 06-queue.txt
advisory lock → 07-advisory.txt
总结 → 08-notes.md

参考

第 3 步中，失败才是正确答案。deadlock detected 的两行 DETAIL 会准确指出谁在等待什么——学会阅读这两行，是本实验中最持久实用的技能。

制造锁等待

用两个会话对同一行执行 for update。在第二个会话等待期间，找出谁正在阻塞谁，并写入 01-block.txt。

先执行 create table stock(id int primary key, qty int not null); insert into stock values (1,10),(2,10);。让一侧在后台运行：psql -h 127.0.0.1 -U lab -d labdb -c "begin; select * from stock where id=1 for update; select pg_sleep(15);" &。然后在另一个会话中保存 select pid, pg_blocking_pids(pid) from pg_stat_activity where cardinality(pg_blocking_pids(pid))>0; 的结果。

避免无限等待

设置 lock_timeout 后请求已锁定的行，确认操作被取消。将完整错误信息写入 02-timeout.txt。

set lock_timeout='1s'; begin; select * from stock where id=1 for update;——会出现 canceling statement due to lock timeout。如果不设置，等待会占用连接；当这些连接耗尽连接池时，停止工作的不是数据库，而是应用程序。

故意制造死锁

让两个事务以相反顺序更新 id 1、2，从而制造死锁。将包含 deadlock detected 的完整错误信息写入 03-deadlock.txt。

A 按 1 →（短暂停顿）→ 2，B 按 2 →（短暂停顿）→ 1。中间加入 select pg_sleep(2) 使它们重叠。把两者同时放到后台运行并执行 wait。两行 DETAIL 会准确说明谁在等待什么——学会阅读这两行就是本步骤的目标。

统一顺序以消除死锁

修改相同的两个操作，让它们都按 id 升序加锁，从而在没有死锁的情况下完成。运行至少两次也不得失败。

采用哪种排序标准并不重要，重要的是所有任务使用相同标准。将结果写入 04-order.txt。死锁无法完全杜绝，因此应用仍应对 40P01 进行重试，但统一顺序可以消除绝大多数死锁。

防止库存变为负数

把 stock 中 id=1 的值恢复为 10，然后尝试并发扣减 20 次。库存为 10 且扣减 20 次时，最终数量必须准确等于 0。将结果写入 05-stock.txt。

两种方式任选其一——用 for update 读取并判断，或者用单条语句完成（update stock set qty=qty-1 where id=1 and qty>0）。如果可以，后一种成本更低。并发执行可使用 for i in $(seq 20); do (psql ... &) ; done; wait。

**仅仅没有负数并不足以通过。**如果先用普通 select 读取再扣减，虽然不会出现负数，但结果可能停在 9 左右——更新丢失了。这正是本步骤要发现的缺陷。

避免工作进程排队等待

创建 jobs 表，让两个工作进程分别取得不同的任务。在 06-queue.txt 中用 worker1=... / worker2=... 两行记录各自取得的 id。

只使用 for update 时，第二个工作进程会等待。for update skip locked 会跳过已锁定的行。如果第一个工作进程占用 1、2，第二个就不会等待，而会取得 3、4。文件按如下格式书写——各占一行，并同时保留所用 SQL：

worker1=1,2
worker2=3,4

为不存在对应行的对象加锁

使用 pg_try_advisory_lock 证明两个会话不能同时取得同一个键。在 07-advisory.txt 中用 session1= / session2= 两行记录结果。

一个会话通过 select pg_try_advisory_lock(42) 取得锁后，在另一个会话尝试同一个键会得到 f（在同一会话再次执行会因可重入而得到 t——这不能作为证明）。文件格式如下：

session1=t
session2=f

使用连接池时，归还连接前务必执行 pg_advisory_unlock。

总结三项要点

在 08-notes.md 中至少写三行：一条消除死锁的规则、缺少 lock_timeout 时哪里会先停止，以及 skip locked 改变了什么。

正文必须包含 순서、커넥션、skip locked。第二点是生产环境中最常被误诊的部分——症状不是“数据库很慢”，而是“服务器不再响应”。