锁等待与死锁排查

MySQL 锁等待与死锁：谁在等谁，怎么查

锁问题比慢 SQL 更难缠：慢 SQL 只是浪费资源，锁会把不相关的业务一起拖住，一个未提交的事务能让整张表的更新全部排队。排查锁的第一步不是看锁，而是分清三种情况——锁等待、死锁、长事务，因为它们的处置动作完全不同。

三类问题的区分

类型	表现	典型根因	处置方向
锁等待	语句一直卡在 `Waiting for ... lock`，最终超时报 `Lock wait timeout exceeded`	另一个事务长时间持有锁未提交	找到并结束持锁事务
死锁	立即报 `Deadlock found when trying to get lock; try restarting transaction`	两个事务加锁顺序相反、间隙锁交叉	统一加锁顺序，业务侧重试
长事务	无报错，但 `innodb_trx` 里事务存活几十分钟	代码忘记提交、批量任务未分批、连接被连接池借走却空闲	结束事务、拆小批次、改代码

死锁不会导致业务无限阻塞，MySQL 会自动回滚代价小的一方；真正让线上雪崩的是“锁等待 + 长事务”的组合，它会让连接池在几分钟内被耗尽。

第一步：先看有没有阻塞

-- 5.7 与 8.0 通用，快速看正在等待锁的会话
SELECT * FROM information_schema.processlist
WHERE state LIKE '%lock%' OR info LIKE '%for update%';

-- 看是否有锁等待计数在涨（多次采样对比）
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';

Innodb_row_lock_current_waits 大于 0 说明此刻就有会话在等锁；Innodb_row_lock_waits 的增量速度说明冲突频率。

第二步：8.0 用 data_locks 直接定位

8.0 起 information_schema.innodb_locks 与 innodb_lock_waits 已被移除，必须改用 performance_schema。下面这条可执行 SQL 直接给出“谁阻塞了谁、卡在哪条 SQL”。

-- MySQL 8.0
SELECT
  r.trx_id            AS waiting_trx,
  r.trx_mysql_thread_id AS waiting_thread,
  LEFT(r.trx_query, 80) AS waiting_query,
  b.trx_id            AS blocking_trx,
  b.trx_mysql_thread_id AS blocking_thread,
  LEFT(b.trx_query, 80) AS blocking_query,
  l.lock_table, l.lock_index, l.lock_mode, l.lock_type, l.lock_data
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id
JOIN performance_schema.data_locks l ON l.engine_lock_id = w.requesting_engine_lock_id;

只想知道“谁在等哪把锁”，把 data_locks 换成 WHERE LOCK_STATUS = 'WAITING' 单表查询即可，字段 LOCK_DATA 直接给出被锁的主键值。

LOCK_MODE 判读：X 是排他锁，S 是共享锁，X,REC_NOT_GAP 是精确行锁，X,GAP 与 X 配合用于间隙锁，X,REC_NOT_GAP 之外的 X 常出现在范围更新里。LOCK_DATA 给出具体主键值，能直接落到业务那一行。

5.7 的等价查询

-- MySQL 5.7：innodb_locks + innodb_lock_waits
SELECT
  r.trx_id AS waiting_trx, r.trx_mysql_thread_id AS waiting_thread,
  LEFT(r.trx_query, 80) AS waiting_query,
  b.trx_id AS blocking_trx, b.trx_mysql_thread_id AS blocking_thread,
  LEFT(b.trx_query, 80) AS blocking_query,
  l.lock_table, l.lock_index, l.lock_mode, l.lock_type
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.innodb_locks l ON l.lock_id = w.requested_lock_id;

5.7 里 innodb_locks 只保留最近一批锁信息，数据量大时可能取不全；要完整覆盖，仍然要依赖 SHOW ENGINE INNODB STATUS。

第三步：找长事务与未提交事务

-- 5.7 与 8.0 通用
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS alive_sec,
       trx_mysql_thread_id, trx_rows_locked, trx_rows_modified,
       LEFT(trx_query, 100) AS trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started ASC;

列	判读标准	处置建议
trx_state	`RUNNING` 正常，`LOCK WAIT` 表示在等锁	`LOCK WAIT` 时优先看阻塞者
alive_sec	超过 60 秒需说明原因，超过 600 秒基本是异常	确认业务后 kill 会话
trx_rows_locked	上千说明锁范围过大	检查是否未走索引导致锁全表
trx_rows_modified	与锁行数量级差异大说明批量更新未分批	改为分批提交
trx_mysql_thread_id	用于 `KILL` 的会话号	必须确认后再执行

trx_query 可能是 NULL：这通常意味着事务已开启但当前没有在执行语句（典型的长事务源头，代码忘了 commit）。

第四步：读 SHOW ENGINE INNODB STATUS 的死锁段

mysql -uroot -p -e "SHOW ENGINE INNODB STATUS\G" | sed -n '/LATEST DETECTED DEADLOCK/,/^---/p'

*** (1) TRANSACTION:
TRANSACTION 4213, ACTIVE 12 sec starting index read
UPDATE t_order SET status = 2 WHERE order_no = 'A001'
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS ... index `PRIMARY` of table `shop`.`t_order` ... X
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index `idx_order_no` ... X
*** (2) TRANSACTION: ACTIVE 10 sec starting index read
UPDATE t_user SET points = points + 1 WHERE order_no = 'A001'
*** WE ROLL BACK TRANSACTION (2)

判读要点：两个事务的“持有锁”与“等待锁”正好互补，就是加锁顺序不一致；index PRIMARY 表示锁的是主键行，index idx_order_no 表示走了二级索引后还锁住了对应主键。WE ROLL BACK TRANSACTION (n) 告诉你 MySQL 回滚了哪一个，业务侧只重试被回滚的那个事务才安全。

常见死锁模式

模式	成因	修复
无索引更新导致锁全表	`UPDATE t SET ... WHERE no_index_col = ?` 走全表扫描，锁住扫描过的所有行	补索引，把锁范围收缩到目标行
加锁顺序相反	事务 A 先更 order 再更 user，事务 B 反过来	全局约定表的更新顺序，或在应用层串行化
唯一键冲突加间隙锁	`INSERT ... ON DUPLICATE KEY UPDATE` 或先 `SELECT` 后 `INSERT`	用唯一索引 + 重试，避免先查后插
批量 IN 更新顺序不一致	两个任务对同一批 ID 用不同顺序 `UPDATE ... WHERE id IN (...)`	ID 排序后再执行
大事务跨越多个热点行	一次事务更新上千行，持锁时间长	拆分成小批次，每批单独提交

处置动作

kill 阻塞者而不是等待者：等待者被 kill 只是把问题推给下一个会话，阻塞者终止后才能整体释放。
kill 前必须核对 trx_query 与业务归属，避免误杀正在执行的关键写入；不确定时先 kill 阻塞者。
临时缓解可调大 innodb_lock_wait_timeout（默认 50 秒），但这是拖延而非修复，长事务不除迟早复发。

-- 确认目标后结束阻塞会话
KILL 10231;

预防与巡检项

所有高频更新语句必须走索引，用 trx_rows_locked 验证锁范围是否收敛。
事务尽可能短：不在事务里做 RPC、发消息、写文件，先提交再处理副作用。
统一加锁顺序，写进开发规范；批量更新前先对 ID 排序。
监控三项：Innodb_row_lock_current_waits、锁等待平均时长、innodb_trx 中存活超过 60 秒的事务数。
业务侧实现幂等与重试：捕获 1213（死锁）与 1205（锁超时）退避重试 2~3 次，避免把死锁直接暴露给用户。

小结：锁问题先分成锁等待、死锁、长事务三类再动手，8.0 用 performance_schema.data_locks/data_lock_waits、5.7 用 innodb_locks/innodb_lock_waits 找到阻塞关系，用 innodb_trx 抓长事务，用 SHOW ENGINE INNODB STATUS 的死锁段确认加锁顺序；处置时 kill 阻塞者而非等待者，根治手段是补索引缩小锁范围、统一加锁顺序、拆短事务，并在业务层做幂等重试。

笔记加载中…

标签：#故障排查 #运维

返回知识库