- Authors
- Name
- Youngju Kim
- @fjvbn20031
- 引言 — 第一次遇到 deadlock detected 日志
- 死锁不是故障,而是正常行为
- 在 PostgreSQL 日志里读死锁报告
- 读 MySQL 的 SHOW ENGINE INNODB STATUS
- 实务中反复出现的三种模式
- 预防原则 — 顺序、长度、索引
- deadlock_timeout 与 lock_timeout,以及和锁等待的区分
- 结语 — 死锁不是要消灭,而是要让它可以重试
引言 — 第一次遇到 deadlock detected 日志
生产日志里出现了这样一行。
ERROR: deadlock detected
DETAIL: Process 24188 waits for ShareLock on transaction 98211; blocked by process 24193.
Process 24193 waits for ShareLock on transaction 98209; blocked by process 24188.
HINT: See server log for query details.
CONTEXT: while updating tuple (128,17) in relation "orders"
初看像是严重故障,但这行日志被打出来,恰恰说明数据库已经检测到问题并且解决掉了。一边的事务被回滚,另一边正常执行完毕。数据是一致的。
真正的问题有两个。第一,应用可能没有重试这个错误,而是原样抛给了用户。第二,如果死锁反复发生,那就是事务设计存在结构性缺陷的信号。
本文先从如何在日志中锁定纠缠在一起的那两条查询开始,再讲反复出现的三种模式与预防原则,最后说明如何不把死锁和普通锁等待混为一谈。
死锁不是故障,而是正常行为
死锁发生在两个事务互相等待对方持有的锁的时候。
时间 ──────────────────────────────────────────▶
事务 A: 获取 row 1 的锁 ─────── 请求 row 2 的锁 ─── 等待
事务 B: 获取 row 2 的锁 ────── 请求 row 1 的锁 ─── 等待
双方永远等待下去
在这种状态下如果数据库什么都不做,两个事务就会永远停住。所以 PostgreSQL 和 MySQL 都会在锁等待图中检测到环时挑一边中断掉。这是正确的设计,也没有别的选项。
关键就在这里。把死锁彻底消除不可能成为目标。无论怎么设计,只要存在并发,就无法把发生概率降到零。目标有两个:把频率压到实用的水平,以及在真的发生时让应用安静地重试。
# PostgreSQL: 40P01 (deadlock_detected)
# MySQL: 1213 (ER_LOCK_DEADLOCK)
RETRYABLE = {"40001", "40P01"}
重试时要守住两点:把整个事务从头重新执行,以及在延迟中掺入抖动。用固定延迟重试,两个事务会以同样的间隔再次相撞。
在 PostgreSQL 日志里读死锁报告
默认配置下,只能得到前面那种「去看服务器日志」的提示。想看到真正的查询,就必须先配好日志。
-- 把死锁和耗时较长的锁等待记入日志
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '1s';
ALTER SYSTEM SET log_min_error_statement = 'error';
ALTER SYSTEM SET log_line_prefix = '%m [%p] %u@%d app=%a ';
SELECT pg_reload_conf();
把进程 ID 和应用名放进 log_line_prefix 是决定性的一步。死锁报告是用进程号来指认对方的,所以必须靠这个号去找其他日志行才能还原出查询。如果应用侧在连接串里设置了 application_name,是哪个服务参与其中就能立刻看清。
配置之后的日志长这样。
2026-07-26 14:02:11.442 KST [24188] api@shop app=order-service ERROR: deadlock detected
2026-07-26 14:02:11.442 KST [24188] api@shop app=order-service DETAIL: Process 24188 waits for ShareLock on transaction 98211; blocked by process 24193.
Process 24193 waits for ShareLock on transaction 98209; blocked by process 24188.
Process 24188: UPDATE inventory SET stock = stock - 1 WHERE sku = 'B-200';
Process 24193: UPDATE inventory SET stock = stock - 1 WHERE sku = 'A-100';
2026-07-26 14:02:11.442 KST [24188] api@shop app=order-service HINT: See server log for query details.
2026-07-26 14:02:11.442 KST [24188] api@shop app=order-service CONTEXT: while updating tuple (128,17) in relation "inventory"
2026-07-26 14:02:11.442 KST [24188] api@shop app=order-service STATEMENT: UPDATE inventory SET stock = stock - 1 WHERE sku = 'B-200';
阅读顺序是这样的。
Process 24188 waits ... blocked by process 24193— 先弄清环的方向。两行说明是两方成环,三行以上则说明有三个以上的事务缠在了一起。Process 24188:和Process 24193:这两行 — 是各进程最后正在等待的语句。CONTEXT— 告诉你卡在了哪张表的哪个元组上。
这里要点明一个最重要的误解。报告里打出的这两条语句只是死锁的最后一块碎片,而不是原因。24188 在等 B-200,可它已经握着的锁是 A-100。而制造那个 A-100 锁的语句并不会出现在报告里。也就是说,要查明原因,需要两个事务在此之前执行过的全部语句。
所以实务中会用这样的组合。
-- 从日志里回溯相关进程在那个时刻执行过的其他语句
-- (log_line_prefix 里必须有 %p 才行)
grep -E '\[24188\]|\[24193\]' /var/log/postgresql/postgresql.log \
| awk '$1 " " $2 >= "2026-07-26 14:01:50"' \
| head -60
如果死锁已经过去,日志就是唯一的证据。但如果想看当下正在进行的锁等待,查目录就可以了。
SELECT a.pid,
a.application_name,
a.state,
now() - a.xact_start AS tx_age,
now() - a.state_change AS state_age,
pg_blocking_pids(a.pid) AS blocked_by,
left(a.query, 80) AS query
FROM pg_stat_activity a
WHERE a.backend_type = 'client backend'
AND (cardinality(pg_blocking_pids(a.pid)) > 0
OR a.state = 'idle in transaction')
ORDER BY tx_age DESC;
pid | application_name | state | tx_age | state_age | blocked_by | query
-------+------------------+---------------------+--------------+--------------+------------+--------------------------------
24193 | order-service | idle in transaction | 00:04:12.881 | 00:04:10.220 | {} | SELECT ... FOR UPDATE
24188 | order-service | active | 00:00:08.114 | 00:00:08.101 | {24193} | UPDATE inventory SET stock ...
如果有某个进程的 pg_blocking_pids 是空的,状态却是 idle in transaction,那它就是根源。这说明应用开着事务却跑去干别的事了。
读 MySQL 的 SHOW ENGINE INNODB STATUS
MySQL 的状态输出里只保留最近一次死锁。
SHOW ENGINE INNODB STATUS\G
------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-07-26 14:02:11 0x7f2a1c0d5700
*** (1) TRANSACTION:
TRANSACTION 4821094, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 8812, query id 2210934 10.0.3.21 api updating
UPDATE inventory SET stock = stock - 1 WHERE sku = 'B-200'
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 421 page no 5 n bits 80 index PRIMARY of table `shop`.`inventory`
trx id 4821094 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; ... 0: len 5; hex 412d313030; asc A-100;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 421 page no 5 n bits 80 index PRIMARY of table `shop`.`inventory`
trx id 4821094 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; ... 0: len 5; hex 422d323030; asc B-200;
*** (2) TRANSACTION:
TRANSACTION 4821096, ACTIVE 2 sec starting index read
MySQL thread id 8815, query id 2210941 10.0.3.22 api updating
UPDATE inventory SET stock = stock - 1 WHERE sku = 'A-100'
*** (2) HOLDS THE LOCK(S):
... asc B-200;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
... asc A-100;
*** WE ROLL BACK TRANSACTION (2)
信息量比 PostgreSQL 的报告更大。尤其是有 HOLDS THE LOCK(S) 这一节,连每个事务已经握着的锁都展示了出来。仅凭上面这段输出,整个环就能被完整还原:事务 1 握着 A-100 等 B-200,事务 2 握着 B-200 等 A-100。
阅读时值得留意的条目有这些。
asc后面的字符串 — 被锁记录的索引键值。能立刻看出是哪一行。index PRIMARY of table— 说明锁加在了哪个索引上。如果看到的是二级索引的名字,就说明访问走的是那条索引路径。lock_mode X locks rec but not gap— 只锁住了记录。如果出现locks gap before rec或者只有lock_mode X,那就是间隙锁或临键锁。看到间隙锁,就是锁范围很宽的信号。WE ROLL BACK TRANSACTION (2)— InnoDB 会牺牲需要回滚的工作量较少的一方,也就是改动行数较少的那一方。
问题在于这段输出只保留最后一次。生产环境必须把它们全部记入日志。
SET GLOBAL innodb_print_all_deadlocks = ON;
打开这个设置后,所有死锁都会被记录到错误日志里。如果死锁多到让人担心日志负载,那本身就已经是必须修复的问题了。
实务中反复出现的三种模式
把大量死锁案例拆解开看,基本上会收敛为三种。
| 模式 | 在日志里呈现的样子 | 解法 |
|---|---|---|
| 多行更新顺序不一致 | 两个事务交叉等待同一张表的不同键 | 始终按同一标准排序后的顺序加锁 |
| 缺少索引导致锁范围扩大 | 被锁的行数远多于实际目标,并能看到间隙锁 | 给条件列加索引 |
| 外键引发的父行加锁 | 明明是子表 INSERT,却在父表记录上等待 | 缩小更新父表的事务,必要时延迟约束校验 |
模式 1 — 更新顺序不一致
最常见。两个请求以相反的顺序去碰同样的两行。
-- 请求 A
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE sku = 'A-100';
UPDATE inventory SET stock = stock - 1 WHERE sku = 'B-200';
COMMIT;
-- 请求 B (同时)
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE sku = 'B-200';
UPDATE inventory SET stock = stock - 1 WHERE sku = 'A-100';
COMMIT;
在应用代码里,这个顺序通常就是加入购物车的顺序,也就是因人而异的顺序。解决办法是事先把要加锁的对象排好序。
-- 用一条语句处理并显式指定顺序
UPDATE inventory
SET stock = stock - v.qty
FROM (VALUES ('B-200', 1), ('A-100', 2)) AS v(sku, qty)
WHERE inventory.sku = v.sku;
有一点需要注意。单条 UPDATE 语句的加锁顺序取决于执行计划,所以光靠 SQL 并不能完全保证。想要确保,就先按排好的顺序把锁拿到手。
BEGIN;
SELECT sku FROM inventory
WHERE sku IN ('B-200', 'A-100')
ORDER BY sku
FOR UPDATE;
-- 之后的更新无论什么顺序都是安全的
UPDATE inventory SET stock = stock - 1 WHERE sku = 'B-200';
UPDATE inventory SET stock = stock - 1 WHERE sku = 'A-100';
COMMIT;
如果是批处理任务,原则是在应用侧把键排好序再传下去。排序标准是什么都无所谓,唯一重要的是所有代码路径都使用同一个标准。
模式 2 — 缺少索引导致锁范围变宽
这在 MySQL 里尤其致命。条件列上没有索引时,InnoDB 会给扫过的每一条记录加锁。
-- 如果 order_no 上没有索引
UPDATE orders SET status = 'cancelled' WHERE order_no = 'ORD-20260726-001';
-- 实际上是全表扫描 + 给扫过的所有行加锁
目标只有一行,却锁住了一百万行。在这种状态下,毫不相干的两个请求也会互相冲突。如果死锁日志里出现的两条查询在逻辑上看起来毫无关系,就该怀疑这个模式。
CREATE INDEX idx_orders_order_no ON orders (order_no);
PostgreSQL 不会无条件地给没有索引时扫过的每一行都加锁,但会给要更新的行加锁。即便如此,没有索引就意味着寻找更新目标的时间变长、事务随之拉长,最终冲突概率还是会上升。
模式 3 — 外键引发的父行加锁
往子表 INSERT 时,为了保证参照完整性会锁住父行。PostgreSQL 会在父行上加 FOR KEY SHARE 级别的锁,InnoDB 则加共享锁。
CREATE TABLE users (id bigserial PRIMARY KEY, name text);
CREATE TABLE orders (
id bigserial PRIMARY KEY,
user_id bigint REFERENCES users(id),
total numeric
);
-- 会话 A
BEGIN;
INSERT INTO orders (user_id, total) VALUES (7, 1000); -- 在 users(7) 上加 KEY SHARE
UPDATE users SET name = 'Kim' WHERE id = 7; -- 是自己的锁,所以能通过
-- 会话 B (同时)
BEGIN;
INSERT INTO orders (user_id, total) VALUES (7, 2000); -- 获取 KEY SHARE (共享锁,可以拿到)
UPDATE users SET name = 'Lee' WHERE id = 7; -- 等待会话 A 的锁
当两个会话引用同一个父行、又都想更新那个父行时,就变成了死锁。共享锁可以被多个事务同时持有,所以在那个状态下试图升级为排他锁的瞬间,它们就会互相阻塞。
在实务中,这个模式经常出现在「下单的同时更新用户的最后下单时间」这类代码里。解法是去掉对父表的更新、拆到单独的表里,或者强制顺序、先把父行排他地锁住。
-- 先排他地锁住父行,消除升级竞争
BEGIN;
SELECT id FROM users WHERE id = 7 FOR UPDATE;
INSERT INTO orders (user_id, total) VALUES (7, 1000);
UPDATE users SET last_ordered_at = now() WHERE id = 7;
COMMIT;
在 PostgreSQL 中,只要 UPDATE 不修改被引用的列就不会与 FOR KEY SHARE 冲突,所以这个问题要轻一些。即便如此,只要结构上仍是围绕同一个父行争夺排他锁,情况就是一样的。
预防原则 — 顺序、长度、索引
从这三种模式中可以直接导出预防原则。
始终按同一顺序访问。如果要锁多行,就按主键升序这类确定性的标准排序。如果要锁多张表,表的顺序也要在编码规范里固定下来。
保持事务简短。死锁概率与持锁时间成正比。事务里不能出现外部 API 调用、文件读写、等待用户输入。光是守住这一条规则,大部分死锁就消失了。
-- 强制切断那些开着事务却放着不管的连接
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
SELECT pg_reload_conf();
用索引收窄锁范围。更新条件和加锁读取的条件必须由索引来处理。这不是查询性能问题,而是并发问题。
批处理要排序并切小。在一个事务里更新 10 万条,这段时间里 10 万行就一直被锁着。
-- 按排好的顺序,以小块处理
WITH batch AS (
SELECT id FROM jobs
WHERE status = 'queued'
ORDER BY id
LIMIT 1000
FOR UPDATE SKIP LOCKED
)
UPDATE jobs SET status = 'processing'
WHERE id IN (SELECT id FROM batch);
当多个工作进程消费同一个队列时,SKIP LOCKED 能从结构上消除死锁。因为它跳过被锁的行而不是去等待,所以根本形成不了环。
deadlock_timeout 与 lock_timeout,以及和锁等待的区分
有三个超时,作用完全不同。
-- 开始死锁检测之前等待的时间。检测成本高,所以不会立刻执行。
SHOW deadlock_timeout; -- 默认 1s
-- 放弃获取锁之前的等待时间。默认是无限。
SHOW lock_timeout; -- 默认 0 (无限)
-- 整条语句执行时间的上限。
SHOW statement_timeout; -- 默认 0 (无限)
调小 deadlock_timeout 能让死锁更快被解开,但对那些没有成环的普通锁等待,检测算法也会每次都跑一遍,白白消耗 CPU。默认值 1 秒在大多数环境下都是合理的。经常能看到有人建议把这个值降到 100ms 之类,但除非死锁频繁到响应延迟真的成了问题,否则代价大于收益。
真正该设置的是 lock_timeout。
-- 在用户响应路径上不要长时间等锁
SET lock_timeout = '3s';
-- DDL 尤其要短,因为它等待期间后面排队的所有查询都会一起被堵住。
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN memo text;
MySQL 中对应的设置是 innodb_lock_wait_timeout,默认值是 50 秒。对 Web 请求路径来说实在太长了。
SET SESSION innodb_lock_wait_timeout = 5;
最后,梳理一个最常见的误诊。锁等待和死锁是两个不同的问题,应对方式也不同。
- 死锁是环。数据库会在 1 秒内检测到并杀掉一边。错误码在 PostgreSQL 是 40P01,在 MySQL 是 1213。症状是间歇性报错,而不是延迟。
- 锁等待不是环。有人长时间握着锁,其余的排起了队。没有人被杀掉,取而代之的是响应时间越拖越长,直到连接池被耗尽、整个服务停摆。错误码是 55P03 或者超时。
「服务变慢是因为死锁」这种判断多半是错的。死锁会被迅速解开,所以并不制造延迟。如果变慢了,该看的是锁等待,以及它背后的长事务或被放任的 idle in transaction 连接。
-- 找出锁等待链条的根
WITH RECURSIVE chain AS (
SELECT pid, unnest(pg_blocking_pids(pid)) AS blocker, 1 AS depth
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0
UNION ALL
SELECT c.blocker, unnest(pg_blocking_pids(c.blocker)), c.depth + 1
FROM chain c
WHERE cardinality(pg_blocking_pids(c.blocker)) > 0 AND c.depth < 10
)
SELECT DISTINCT a.pid, a.state, now() - a.xact_start AS tx_age, left(a.query, 60)
FROM chain c
JOIN pg_stat_activity a ON a.pid = c.blocker
WHERE cardinality(pg_blocking_pids(c.blocker)) = 0;
结语 — 死锁不是要消灭,而是要让它可以重试
整理一下就是这样。
看日志时,首先要记住报告里打出的那两条语句不是原因。它们只是各事务最后等待的语句,真正的原因是在此之前就已经拿到手的锁。如果是 MySQL,HOLDS THE LOCK(S) 这一节会给出这个信息;如果是 PostgreSQL,就得去找同一进程号更早的日志。
预防可以浓缩成顺序、长度、索引三个词。按同样的顺序加锁,把事务保持简短,给加锁条件配上索引。再加上 lock_timeout 和 idle_in_transaction_session_timeout 的设置,事故就不会蔓延成全面故障。
而即便这一切都做到了,死锁仍然会发生。必须有捕获 40P01 和 1213、并掺入退避与抖动去重试的代码。没有那段代码,预防工作的成果就传不到用户手里。