PostgreSQL / 系统课时

识别并消除死锁

识别并消除死锁

死锁不是“某条 SQL 很慢”,而是多个事务形成了无法自行解除的循环等待。本课用两条记录复现死锁,观察 PostgreSQL 的检测与中止行为,再把修复落到统一加锁顺序、缩短持锁时间和等待关系诊断上。

学习目标

  • 能用两个并发事务复现“各持一把锁、再等待对方”的死锁
  • 能区分普通锁等待与死锁,并用 pg_blocking_pids 找出阻塞链
  • 能通过统一锁顺序和缩短事务边界降低死锁概率

先把循环等待复现出来

准备两条独立记录。下面的表只用于实验,避免影响真实业务数据。

sql
DROP TABLE IF EXISTS deadlock_demo;
CREATE TABLE deadlock_demo (
    id integer PRIMARY KEY,
    balance integer NOT NULL
);
INSERT INTO deadlock_demo (id, balance)
VALUES (1, 100), (2, 100);

预期输出:

text
DROP TABLE
CREATE TABLE
INSERT 0 2

打开两个 psql 会话。会话 A 先修改 id=1,会话 B 再修改 id=2,此时双方各自持有一条记录上的行锁。

sql
-- 会话 A
BEGIN;
UPDATE deadlock_demo SET balance = balance - 10 WHERE id = 1;

预期输出:

text
BEGIN
UPDATE 1
sql
-- 会话 B
BEGIN;
UPDATE deadlock_demo SET balance = balance - 20 WHERE id = 2;

预期输出:

text
BEGIN
UPDATE 1

接着让 A 请求 B 正持有的记录。这个语句会等待,不会立即返回。

sql
-- 会话 A
UPDATE deadlock_demo SET balance = balance + 10 WHERE id = 2;

预期结果:

text
<语句保持阻塞,暂时无终端输出>

最后让 B 请求 A 正持有的记录,等待边就闭合成环。

sql
-- 会话 B
UPDATE deadlock_demo SET balance = balance + 20 WHERE id = 1;

预期结果(二者之一会成为受害者):

text
ERROR:  deadlock detected

PostgreSQL 不会让这个环无限等待。某个后端进程等待锁超过 deadlock_timeout 后会进行死锁检查;一旦确认存在环,会中止其中一个事务,让另一个事务在受害者回滚释放锁后继续。

两个事务形成循环等待并由 PostgreSQL 检测死锁

从等待链确认是不是死锁

普通阻塞只有“谁等谁”,最终仍存在能先完成并释放锁的事务;死锁则存在闭环。发生阻塞时,可以从第三个连接查询当前等待者及其阻塞者。

sql
SELECT
    pid,
    wait_event_type,
    wait_event,
    pg_blocking_pids(pid) AS blocking_pids,
    query
FROM pg_stat_activity
WHERE datname = current_database()
  AND wait_event_type = 'Lock';

预期输出会包含动态 PID,实际值可能不同:

text
 pid   | wait_event_type | wait_event    | blocking_pids | query
-------+-----------------+---------------+---------------+-----------------------------------------------
 <PID> | Lock            | transactionid | {<PID>}       | UPDATE deadlock_demo SET balance = balance...

pg_blocking_pids 给的是直接阻塞当前会话的后端 PID。生产排查时应先确认阻塞链,再结合应用日志里的事务入口、SQL 参数和请求时间判断为什么多个事务会以不同顺序访问同一批资源。

用统一加锁顺序消除等待环

最有效的治理不是“捕获 deadlock 错误后一直重试”,而是让所有事务按同一顺序取得可能冲突的锁。例如同时修改账户 1 和 2 时,统一先锁较小的主键,再做业务更新。

sql
BEGIN;

SELECT id
FROM deadlock_demo
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

UPDATE deadlock_demo SET balance = balance - 10 WHERE id = 1;
UPDATE deadlock_demo SET balance = balance + 10 WHERE id = 2;

COMMIT;

预期输出:

text
BEGIN
 id
----
  1
  2
(2 rows)

UPDATE 1
UPDATE 1
COMMIT

并发事务即使都需要同一批记录,也会在第一个冲突点排队,而不会出现 A 等 B、B 又等 A 的闭环。批量处理多行时同样应先对主键排序,再按固定顺序锁定。

缩短事务并验证治理结果

统一顺序解决“环”,缩短事务则减少“重叠窗口”。不要在已经持有数据库锁的事务里等待用户输入、长时间 HTTP/RPC、慢文件 I/O 或不必要的计算;能在事务外完成的准备工作尽量提前完成,并确保过滤列有合适索引,避免无谓延长扫描和持锁时间。

修改后用与复现实验相同的两个会话反复并发执行统一顺序版本:允许出现短暂锁等待,但不应再出现 ERROR: deadlock detected。如果业务仍可能因多张表、外键检查或触发器形成更复杂的锁顺序,就继续沿实际阻塞链梳理所有锁获取路径,而不是只盯着最后报错的那条 SQL。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

识别并消除死锁

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通