DAY 35 W5 · 公司立规矩：建模、约束与事务高频

秒杀预演：锁与死锁 + 周复盘

大促前的秒杀预演：多人同时抢最后一件库存。你需要 SELECT FOR UPDATE、SKIP LOCKED 这些真家伙。

学 40 min

练 50 min

盘 30 min

共 120 分钟

完成这一天

学 · 40 min

01行锁与表锁的模式与兼容矩阵
场景锁不是一把锁：行锁表锁、几种模式、谁跟谁犯冲，得有张地图。
PG 的行锁只在 UPDATE / DELETE / SELECT FOR UPDATE 时出现，不阻塞读（读走 MVCC，根本不碰锁）。表锁大多是 DDL（alter / drop）自动加的，跟一切犯冲。日常关心的其实就一个矩阵：两个写操作争同一行 -> 后到的等；写操作和 DDL 争同一张表 -> 全队等。
```
-- pg_locks 是锁的总账本（含表锁、事务锁、咨询锁等）
select locktype, relation::regclass, mode, granted
from pg_locks
where relation is not null;
```
易错PG 的行锁信息在内存里、不落盘，崩溃恢复不需要修补行锁--跟「锁存在数据页里」的数据库不同，冷知识但面试官爱听。
02FOR UPDATE / FOR SHARE / NOWAIT / SKIP LOCKED
场景秒杀预演的核心武器，四个修饰词各有分工。
select ... for update：选中的行加行锁，别的事务想改会等，锁持续到事务结束；for share：共享锁（只挡写不挡读）；nowait：拿不到锁立即报错而不是排队；skip locked：跳过被锁的行只处理没锁的--多消费者任务队列的完美原语。
```
-- 任务队列：多 worker 并发取任务不撞车
begin;
select id from tasks
where status = 'pending'
order by id
limit 10
for update skip locked;     -- 被别的 worker 锁着的直接跳过
update tasks set status = 'running' where id in (...);
commit;
```
易错for update 必须在事务里才有意义--没有事务，语句结束锁就释放了，等于没锁。
03死锁的成因与 PG 的自动检测
场景两个事务互相等待：A 锁了 1 号行等 2 号，B 锁了 2 号行等 1 号。
PG 的等待图里出现环，死锁检测器（默认每 1s 醒一次）发现后杀掉其中一个事务、报 deadlock detected。被杀的收到错误，另一个正常提交。预防三板斧：① 多行操作按相同顺序（比如都按 id 升序）；② 缩短事务（锁持有时间 = 事务长度）；③ 一次锁齐所需行，别分批加锁。
```
-- 复现模板（两个窗口交错执行）：
-- 窗口A                    -- 窗口B
begin;                      begin;
select * from p where id=1 for update;
                            select * from p where id=2 for update;
select * from p where id=2 for update;  -- 等待 B
                            select * from p where id=1 for update;
                            -- B 先拿到锁，A 被杀：deadlock detected
```
易错死锁没有「报错给双方」：一个失败一个成功。应用必须把被杀的事务重试，否则等于丢了一次更新。
04pg_locks 与 pg_blocking_pids() 排查阻塞
场景生产上一条 UPDATE 卡了 10 分钟--它在等谁？
一行 SQL 找出阻塞链：pg_blocking_pids(pid) 返回「正在阻塞我」的连接。配上 pg_stat_activity 的 query 字段，谁堵谁、堵在哪条语句，一目了然。这是线上「全站突然卡住」的第一反应动作。
```
select pid,
       pg_blocking_pids(pid) as 被谁阻塞,
       wait_event_type, wait_event,
       state,
       now() - query_start as 已运行,
       left(query, 60) as query
from pg_stat_activity
where state <> 'idle'
order by query_start;
```
易错先处理「被谁阻塞」非空且最老的那个连接（看是不是忘提交的事务），再考虑杀谁。

练 · 50 min

两个会话对同一行 FOR UPDATE，观察阻塞

参考答案

B 等的是行锁；配合第 4 题的 pg_blocking_pids 能直接看到「B 被 A 阻塞」。同时注意普通读不受阻--PG 读走 MVCC 根本不碰锁。

-- 基线
update products set stock = 100 where name = '商品1';

-- 窗口A                          -- 窗口B
begin;
select stock from products
where name = '商品1'
for update;   -- 100，行锁到手
                                  begin;
                                  select stock from products
                                  where name = '商品1' for update;
                                  -- 卡住：等 A 释放行锁
update products set stock = stock - 1
where name = '商品1';
commit;                           -- A 提交的瞬间 B 恢复，读到 99
                                  commit;

-- B 卡住期间开第三个窗口验证：普通 select 完全不受影响
select stock from products where name = '商品1';

用 SKIP LOCKED 实现一个多消费者任务队列

参考答案

对比实验：把 skip locked 去掉重跑，B 会整个卡住等 A 提交，队列并行度归零。skip locked 是多消费者任务队列的原语：锁着的让给别人，只拿没人碰的。

-- 准备（任一窗口执行一次）
create table if not exists tasks (
  id      bigint generated always as identity primary key,
  status  text not null default 'pending',
  payload text
);
truncate tasks;
insert into tasks(payload)
select '任务' || g from generate_series(1, 20) g;

-- 窗口A 和 窗口B 同时执行同一段：
begin;
select id from tasks
where status = 'pending'
order by id
limit 10
for update skip locked;
-- A 拿到 id 1~10；B 同一时刻执行，跳过被 A 锁住的行，直接拿到 11~20
update tasks set status = 'running'
where id in (/* 本窗口刚查到的 id */);
commit;

-- 应用里更常用的单语句版：取任务和改状态一条完成
begin;
with picked as (
  select id from tasks
  where status = 'pending'
  order by id
  limit 10
  for update skip locked
)
update tasks t
set status = 'running'
from picked
where t.id = picked.id
returning t.id;
commit;

-- 练习完还原
drop table tasks;

故意制造死锁，读 PG 报出的死锁日志

参考答案

死锁检测器每 deadlock_timeout（默认 1s）醒一次找等待环，报错只出现在被杀的一方。避免三板斧：多行操作按固定顺序（两边都先商品1后商品2就死不了）、缩短事务、一次锁齐别分批。

-- 基线
update products set stock = 100 where name = '商品1';
update products set stock = 200 where name = '商品2';

-- 窗口A                          -- 窗口B
begin;
update products set stock = stock - 1
where name = '商品1';   -- A 锁住商品1
                                  begin;
                                  update products set stock = stock - 1
                                  where name = '商品2';   -- B 锁住商品2
update products set stock = stock - 1
where name = '商品2';   -- A 等 B 放商品2
                                  update products set stock = stock - 1
                                  where name = '商品1';   -- B 等 A：等待环出现
                                  -- 约 1 秒后其中一方被杀：
                                  -- ERROR:  deadlock detected
                                  -- DETAIL: Process 123 waits for ShareLock on
                                  --         transaction 456, blocked by ...

-- 幸存的一方正常 commit；被杀的一方 rollback 后重试
rollback;

查 pg_locks 找出阻塞链条

参考答案

B 那一行的「被谁阻塞」就是 A 的 pid--阻塞链一眼看清。线上「全站突然卡住」的第一反应动作就是这条查询：先看最老的、被阻塞非空的连接是不是忘提交的事务，再决定杀谁。

-- 先复现第 1 题的「B 卡在 for update」，然后在第三个窗口执行：
select pid,
       pg_blocking_pids(pid) as 被谁阻塞,
       wait_event_type, wait_event,
       state,
       now() - query_start as 已运行,
       left(query, 60) as query
from pg_stat_activity
where state <> 'idle'
order by query_start;

-- 想看锁本身的明细
select locktype, relation::regclass as 表, mode, granted, pid
from pg_locks
where relation is not null
order by granted;   -- granted 为 false 的就是正在排队的锁

用 NOWAIT 实现快速失败而不是排队

参考答案

nowait 把干等变成快速失败：秒杀场景宁可放弃这一次也不挂住连接，配合应用层重试 / 降级使用。和 skip locked 的区别：nowait 是一行都拿不到就报错，skip locked 是拿不到的跳过、拿剩下的。

-- 窗口A                          -- 窗口B
begin;
select stock from products
where name = '商品1'
for update;   -- 锁到手
                                  begin;
                                  select stock from products
                                  where name = '商品1'
                                  for update nowait;
                                  -- 不排队，立刻失败：
                                  -- ERROR: could not obtain lock on row
                                  --        in relation "products"
                                  commit;   -- 转身干别的（或返回「稍后重试」）
commit;

过关标准 能画出死锁那两个事务的时序图，并说出三种避免死锁的做法。

学 · 40 min

01行锁与表锁的模式与兼容矩阵

02FOR UPDATE / FOR SHARE / NOWAIT / SKIP LOCKED

03死锁的成因与 PG 的自动检测

04pg_locks 与 pg_blocking_pids() 排查阻塞

练 · 50 min

出题端点设置