MySQL数据库锁等待如何排查?事务分析与死锁处理方法

应用日志里突然出现大量 Lock wait timeout exceeded; try restarting transaction,接口超时率从 0.1% 飙到 30%。你登上数据库,SHOW PROCESSLIST 里几十条 UPDATE 全部卡住,但每一条看起来都很正常——它们只是恰好在等同一批行。锁等待是 MySQL 运维里最需要"查因果链"的问题:你看到的几十个被阻塞的事务都只是受害者,真正的元凶往往是某一个跑了很久却没提交的事务,它可能藏在连接里已经睡了十几分钟。本文讲清楚怎么快速找到那个源头,以及死锁日志该怎么读。

一、先建立正确的心智模型:InnoDB 有哪些锁

排查锁问题之前,必须先知道你在查的是什么锁。InnoDB 的锁大致分三层。

1.1 按粒度:表级意向锁与行级锁

锁类型缩写粒度说明
共享锁S行SELECT ... LOCK IN SHARE MODE(8.0 为 FOR SHARE)加的读锁
排他锁X行UPDATE/DELETE/SELECT ... FOR UPDATE 加的写锁
意向共享锁IS表事务打算给某些行加 S 锁前,先在表上加 IS
意向排他锁IX表同上,对应 X 锁

意向锁的意义是快速判断表级冲突:如果事务 A 要给整表加表锁,不需要逐行检查有没有行锁,只要看表上有没有 IS/IX 就知道。行级 S 与 X 的兼容矩阵是:S 与 S 兼容,其余组合(S-X、X-S、X-X)互斥。

1.2 按范围:记录锁、间隙锁与 Next-Key Lock

这是最容易出问题的部分。在默认的可重复读(REPEATABLE READ)隔离级别下,InnoDB 用的是 Next-Key Lock——它等于"记录锁(Record Lock)+ 间隙锁(Gap Lock)",锁住的是索引记录本身以及它前面的那段间隙。

假设表 t 有索引列 id,现有值:10, 20, 30

-- 事务 A 执行(RR 隔离级别)
SELECT * FROM t WHERE id = 20 FOR UPDATE;

如果 id 是唯一索引:只锁住 id=20 这一条记录(Record Lock)
如果 id 是非唯一索引:锁住 (10, 20] 和 (20, 30] 两个 next-key 区间
                      即连 id 在 10~30 之间的插入也会被阻塞
如果 id 没有索引:全表所有记录加锁 + 所有间隙加锁,等于锁表

三条关键结论:

  • 没有索引的 UPDATE/DELETE 会锁全表。这是最恐怖的锁问题来源,一条 UPDATE t SET status=1 WHERE name='x' 如果 name 没索引,会把整张表的所有行和间隙都锁住,所有并发写入全部阻塞。
  • 非唯一索引的锁范围比想象中大。这也是很多人"明明更新的是不同行却互相阻塞"的原因。
  • 间隙锁之间不互斥。Gap S 锁和 Gap X 锁是兼容的(因为间隙锁只阻止插入,不阻止读),真正冲突的是"间隙锁 + 插入意向锁"(Insert Intention Lock)。这构成了死锁的一个经典模式。
-- 降低锁范围的两个手段
-- 1. 把隔离级别改成 READ COMMITTED:间隙锁基本消失(除外键检查和唯一性冲突场景)
SET SESSION transaction_isolation = 'READ-COMMITTED';
-- 全局:transaction_isolation = READ-COMMITTED(注意 binlog 必须用 ROW 格式)

-- 2. 8.0.18+ 可以用 SKIP LOCKED / NOWAIT 快速失败而非等待
SELECT * FROM t WHERE status=0 LIMIT 10 FOR UPDATE SKIP LOCKED;  -- 跳过被锁的行
SELECT * FROM t WHERE id=5 FOR UPDATE NOWAIT;                    -- 立即报错不等待

1.3 还有一种常被忽略的锁:MDL

元数据锁(Metadata Lock,MDL)不是 InnoDB 层的锁,而是 Server 层的。它的经典故障场景是:一个事务里执行了 SELECT 但一直没提交,此时另一个会话执行 ALTER TABLE 会被阻塞;而 ALTER 被阻塞后,后续所有对该表的查询(哪怕是 SELECT)也全部排队。表现就是整张表突然不可访问。

-- 查看 MDL 等待(8.0)
SELECT * FROM performance_schema.metadata_locks WHERE OWNER_THREAD_ID IS NOT NULL\G

-- 用 sys schema 直接看谁阻塞了谁
SELECT * FROM sys.schema_table_lock_waits\G

-- 关键参数:MDL 等待超时,默认 31536000 秒(一年!基本等于永久等待)
SHOW VARIABLES LIKE 'lock_wait_timeout';   -- 注意这不是 innodb_lock_wait_timeout

lock_wait_timeout 控制 MDL 等待,默认是一年,这几乎等于"永远等下去"。生产环境建议把它调小(比如 30 秒),避免一个 DDL 卡住导致全表雪崩。另外做 DDL 前务必先用 SHOW PROCESSLIST 确认没有长事务,或使用 pt-online-schema-change / gh-ost 这类在线改表工具。

二、锁等待排查:从现象到源头

2.1 第一现场:SHOW ENGINE INNODB STATUS

这是最权威的锁信息来源,输出分成很多段,锁相关的在 TRANSACTIONS 段:

mysql> SHOW ENGINE INNODB STATUS\G
...
------------
TRANSACTIONS
------------
Trx id counter 4215647
Purge done for trx's n:o < 4215640 undo n:o < 0
History list length 8
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 4215645, ACTIVE 1823 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 108, OS thread handle 140234, query id 882341 10.0.0.5 appuser updating
UPDATE orders SET status = 2 WHERE id = 998877
------- TRX HAS BEEN WAITING 45 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 4 n bits 80 index PRIMARY of table `orderdb`.`orders`
trx id 4215645 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 4; hex 000f3d1d; asc  = ;;
...
---TRANSACTION 4215600, ACTIVE 2400 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 3
MySQL thread id 95, OS thread handle 140198, query id 881900 10.0.0.5 appuser

逐项解读:

  • ACTIVE 1823 sec:事务已经运行了 1823 秒还没提交,这是找长事务的第一指标。
  • LOCK WAIT + TRX HAS BEEN WAITING 45 SEC:这个事务正在等锁,等了 45 秒。
  • lock_mode X locks rec but not gap waiting:等待的是 X 记录锁(不含间隙),状态为 waiting。如果是 locks gap before rec 则是间隙锁,insert intention 是插入意向锁。
  • index PRIMARY of table orderdb.orders:锁在主键索引上。
  • 最后那个 ---TRANSACTION 4215600, ACTIVE 2400 sec 且没有 LOCK WAIT 标记、有 undo log entries 3 的事务,就是阻塞源头——它自己不等锁,但它持有别人需要的锁,并且已经开了 2400 秒还没提交。

注意 SHOW ENGINE INNODB STATUS 输出有长度限制,事务特别多时会被截断。8.0 可以用 innodb_status_output_locks = ON 让它打印更详细的锁列表;也可以直接查 performance_schema。

2.2 结构化查询:information_schema 与 performance_schema

-- 【通用】查看当前所有 InnoDB 事务
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_sec,
       trx_wait_started, trx_rows_locked, trx_rows_modified,
       trx_isolation_level, trx_mysql_thread_id AS pid,
       LEFT(trx_query, 100) AS sql_text
FROM information_schema.INNODB_TRX
ORDER BY trx_started LIMIT 20;

字段怎么读:trx_state 为 LOCK WAIT 的是被阻塞者,RUNNING 的是正在执行或空闲等待的(可能是源头);trx_rows_locked 是该事务已锁定的行数,值特别大的往往就是阻塞源头;trx_rows_modified 是已修改的行数,值大说明这是个大事务,回滚会很慢;trx_mysql_thread_id 就是要 KILL 的那个 ID。

-- 【MySQL 8.0】performance_schema 方式(推荐,INNODB_LOCKS 在 8.0 已移除)
-- 查看所有持有的锁
SELECT lock_id, lock_type, lock_mode, lock_status, lock_data,
       object_schema, object_name, index_name
FROM performance_schema.data_locks;

-- 查看锁等待关系:谁在等谁
SELECT * FROM performance_schema.data_lock_waits;
-- REQUESTING_ENGINE_TRANSACTION_ID  等待方事务 ID
-- BLOCKING_ENGINE_TRANSACTION_ID    阻塞方事务 ID

-- 【强烈推荐】sys schema 封装视图,直接给出 kill 语句
SELECT waiting_pid, waiting_query, waiting_lock_mode,
       blocking_pid, blocking_query, blocking_lock_mode,
       wait_age, sql_kill_blocking_query
FROM sys.innodb_lock_waits\G

-- 【MySQL 5.7 及更早】
SELECT * FROM information_schema.INNODB_LOCKS\G      -- 所有锁
SELECT * FROM information_schema.INNODB_LOCK_WAITS\G  -- 等待关系

sys.innodb_lock_waits 是生产环境最好用的一张表,它把事务 ID 转换成了 MySQL 的 process id,还直接生成了 sql_kill_blocking_query 字段(形如 KILL QUERY 95)。但它只显示"直接阻塞关系",如果有 A→B→C 这样的链式阻塞,需要逐层追溯。

2.3 处理:怎么正确地杀

  1. 用 sys.innodb_lock_waits 找到 blocking_pid。
  2. 确认这个事务在做什么:SELECT * FROM information_schema.PROCESSLIST WHERE ID = ,看它的 Info 和 Time。如果 Info 是 NULL 说明事务开着但连接空闲(典型的"忘了提交")。
  3. 优先用 KILL QUERY (只终止当前语句,保留连接和事务上下文),不行再用 KILL (断开连接,事务回滚)。
  4. 不要批量杀等待者。等待者是无辜的,杀掉他们后新请求还会继续被同一个源头阻塞。
  5. KILL 之后确认 INNODB_TRX 里该事务消失,且 History list length 不再增长。
-- 处理前留存证据(重要)
SHOW ENGINE INNODB STATUS\G > /tmp/innodb_status_$(date +%s).txt
SELECT * FROM sys.innodb_lock_waits\G > /tmp/lock_waits_$(date +%s).txt

-- 按运行时长找出最可疑的长事务
SELECT trx_mysql_thread_id AS pid,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
       trx_rows_locked, trx_rows_modified, LEFT(trx_query,80)
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY age_sec DESC;

-- 杀掉阻塞源头
KILL 95;

三、死锁:日志怎么读

死锁和锁等待是两回事:锁等待是单向的(A 等 B),会一直等到超时(默认 50 秒)或 B 提交;死锁是环形的(A 等 B,B 等 A),InnoDB 能主动检测到并立即回滚其中一个事务,代价是其中一个业务操作失败。

3.1 让死锁日志留存下来

默认情况下 SHOW ENGINE INNODB STATUS 只保留最近一次死锁信息,重启或新的死锁会覆盖它。生产环境必须开启全量记录:

SET GLOBAL innodb_print_all_deadlocks = ON;
-- 写进 my.cnf:[mysqld] innodb_print_all_deadlocks = 1

# 之后所有死锁都会写入错误日志
grep -A 30 'LATEST DETECTED DEADLOCK' /var/log/mysql/error.log | tail -100

3.2 解读一份真实的死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-10-14 14:23:11 0x7f8a4c0e3700
*** (1) TRANSACTION:
TRANSACTION 4216100, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 210, OS thread handle 140552, query id 910234 10.0.0.5 appuser updating
UPDATE orders SET status = 2 WHERE id = 1001

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 58 page no 4 n bits 80 index PRIMARY of table `orderdb`.`orders`
trx id 4216100 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: ... (id=1002)

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 4 n bits 80 index PRIMARY of table `orderdb`.`orders`
trx id 4216100 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: ... (id=1001)

*** (2) TRANSACTION:
TRANSACTION 4216101, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 211, OS thread handle 140553, query id 910235 10.0.0.5 appuser updating
UPDATE orders SET status = 3 WHERE id = 1002

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY ...
trx id 4216101 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: ... (id=1001)

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY ...
trx id 4216101 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: ... (id=1002)

*** WE ROLL BACK TRANSACTION (2)

读法固定为三步:

  1. 看两个事务各自的 SQL:事务 1 在更新 id=1001,事务 2 在更新 id=1002。
  2. 看各自持有什么、等什么:事务 1 持有了 id=1002 的锁却要去拿 id=1001;事务 2 持有了 id=1001 的锁却要去拿 id=1002。交叉了,所以成环。
  3. 看谁被牺牲:最后一行 WE ROLL BACK TRANSACTION (2) 表示 InnoDB 回滚了事务 2(它会选择 undo log 量更小、影响行数更少的事务来回滚,代价最小)。业务上会看到事务 2 那条 UPDATE 报 Deadlock found when trying to get lock; try restarting transaction(错误码 1213)。

3.3 四种高频死锁模式与解法

模式形成原因解法
交叉更新(如上例)两个事务以不同顺序更新同一批行统一加锁顺序:批量更新前先按主键排序。这是最有效的一招
间隙锁 + 插入意向锁RR 级别下 DELETE WHERE id=100(不存在)加间隙锁,另一个事务要插入同一位置改用 READ COMMITTED 隔离级别;或改成"先 SELECT 判断存在再操作"
唯一键冲突两个事务并发插入相同的唯一键,一个成功另一个等待,回滚时形成死锁用 INSERT ... ON DUPLICATE KEY UPDATE 或先查后插 + 捕获重复键异常
无索引导致锁全表WHERE 条件没索引,两个事务各自锁全表但加锁顺序不同加索引,把锁范围从全表降到单行

四、从根上减少锁问题

4.1 事务设计的五条原则

  1. 事务要短。只在真正需要原子性的时候开事务,事务里不要有 RPC 调用、文件 IO、用户输入等待。一个事务从 5 毫秒变成 500 毫秒,锁竞争概率增加百倍。
  2. 批量操作按固定顺序。UPDATE ... WHERE id IN (...) 前先在应用层排序,或者用 ORDER BY id 让加锁顺序一致。
  3. 避免在事务里做 SELECT ... FOR UPDATE 后再慢慢处理。如果一定要这么做,用 NOWAIT 或 SKIP LOCKED 快速失败,而不是无限等待。
  4. 大批量操作要分批。一次更新 10 万行会锁住 10 万行并产生巨大 undo,改成每批 1000 行提交一次。
  5. 关闭 autocommit 之后一定要记得提交。大量生产事故源于"显式 BEGIN 后异常分支没 COMMIT/ROLLBACK,连接归还连接池后事务一直挂着"。

4.2 参数层面的调整

-- 1. 锁等待超时(默认 50 秒),短事务业务可以调小到 10-20 秒快速失败
SET GLOBAL innodb_lock_wait_timeout = 20;

-- 2. 死锁检测开关(Percona / MariaDB 提供,官方 MySQL 无此参数)
--    innodb_deadlock_detect = OFF 可关闭检测,配合小超时用,
--    适用于高并发且能接受死锁变等待的场景(官方版不要动)

-- 3. 隔离级别:RR 是默认,RC 能显著减少间隙锁
--    注意:改为 RC 时 binlog_format 必须是 ROW
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

-- 4. 打印全部死锁到错误日志
SET GLOBAL innodb_print_all_deadlocks = ON;

-- 5. 输出更详细的锁信息(8.0)
SET GLOBAL innodb_status_output_locks = ON;

-- 6. MDL 等待超时(默认一年,务必改小)
SET GLOBAL lock_wait_timeout = 30;

4.3 监控与告警

-- 长事务监控:定时执行,超过阈值告警
SELECT COUNT(*) AS long_trx_cnt,
       MAX(TIMESTAMPDIFF(SECOND, trx_started, NOW())) AS max_age_sec
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30;

-- 锁等待数量监控
SELECT COUNT(*) FROM performance_schema.data_lock_waits;

-- 死锁累计次数(8.0)
SELECT COUNT FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME = 'wait/synch/mutex/innodb/deadlock_check';
-- 或用状态变量看累计死锁数(Percona 分支有 innodb_deadlocks)

-- undo history 长度:过大说明有长事务阻塞 purge
SHOW ENGINE INNODB STATUS\G   -- 看 History list length,持续 > 10000 要警惕

History list length 是一个很好的间接指标。它表示尚未被 purge 清理的 undo 日志数量。只要有一个长事务不提交,它之后产生的所有 undo 都不能被清理,这个值就会持续增长,导致 ibdata / undo 表空间膨胀、purge 线程压力大、甚至让所有查询变慢(因为要读更长的 undo 链做一致性读)。

五、标准化排查清单

  1. 确认是等待还是死锁:应用报错是 1205(Lock wait timeout)还是 1213(Deadlock found)。前者是超时,后者是死锁回滚。
  2. 先看全貌:SELECT * FROM sys.innodb_lock_waits\G(8.0)或 information_schema.INNODB_TRX,数一下有多少事务在 LOCK WAIT。
  3. 找到源头:在 INNODB_TRX 里找 trx_state = RUNNING 且 trx_started 最早、trx_rows_locked 最大的那个事务。
  4. 看它在做什么:用 trx_mysql_thread_id 去 PROCESSLIST 查,Info 为 NULL 说明连接空闲但事务未提交。
  5. 留存证据后处理:先保存 SHOW ENGINE INNODB STATUS 和 lock_waits 输出,再 KILL 源头。
  6. 分析语句:对涉事 SQL 做 EXPLAIN,确认是否走了索引。没走索引就是全表锁,加索引是最有效的解法。
  7. 死锁场景:开启 innodb_print_all_deadlocks,从错误日志里取完整死锁信息,比对两个事务的加锁顺序。
  8. 代码层面修:统一加锁顺序、缩短事务、批量操作排序、避免事务内 RPC。
  9. 加监控:长事务(> 30 秒)、锁等待数、死锁次数纳入告警。
  10. 复盘:把这次的死锁日志归档,形成团队的"死锁模式库",新人培训时直接用。

六、避坑清单

  • 不要用 KILL 批量清理等待者。只杀源头,等待者会自然恢复。
  • innodb_lock_wait_timeout 和 lock_wait_timeout 是两个不同参数,前者管 InnoDB 行锁(默认 50 秒),后者管 MDL 等 Server 层锁(默认一年)。改错参数等于没改。
  • 不要忽略 History list length。它持续增长往往是"有一个长事务没提交"的隐蔽信号。
  • 改隔离级别到 RC 时,binlog 必须是 ROW 格式,否则主从复制会出问题(STATEMENT 格式在 RC 下不安全)。
  • 不要在没有索引的列上做 UPDATE/DELETE,这是锁全表最直接的成因。上线前用 EXPLAIN 逐条检查。
  • 大事务回滚可能比执行还慢,别以为 KILL 完就结束了,要观察 undo 回滚进度。
  • 注意连接池里的"僵尸事务":应用显式 BEGIN 后异常未提交,连接归还池中但事务仍在,下一个复用该连接的请求会莫名其妙地持有大量锁。确保框架层面有事务超时和 finally 回滚。
  • 死锁无法彻底消除,只能降低频率。正确做法是让应用层捕获 1213 错误并重试(一般重试 2-3 次),这是标准实践而非偷懒。
  • 8.0 里 INNODB_LOCKS 表已被移除,不要照抄 5.7 的排查脚本,改用 performance_schema.data_locks / data_lock_waits。
  • 批量更新要用 ORDER BY 固定顺序,否则 MySQL 的加锁顺序取决于执行计划,不同条件下可能不同,死锁随机发生极难复现。

小结

MySQL 锁等待排查的核心能力是"找到因果链上的源头",而不是对着几十个受害者束手无策。工具链要熟记三条:SHOW ENGINE INNODB STATUS 看 TRANSACTIONS 段(找没有 LOCK WAIT 标记但 ACTIVE 时间最长的那个事务)、information_schema.INNODB_TRX 做结构化查询(按 trx_started 排序,看 trx_rows_locked)、以及 sys.innodb_lock_waits(8.0 首选,直接给出阻塞方 pid 和 kill 语句)。处理时只杀源头,用 KILL QUERY 优先于 KILL。死锁方面,开启 innodb_print_all_deadlocks 留存全部日志,按"看两条 SQL — 看各自持有与等待 — 看谁被回滚"三步读,绝大多数死锁都能归到"加锁顺序交叉"这一类,用统一排序这一招就能解决大半。最后,锁问题七分靠设计(短事务、固定顺序、必走索引)、三分靠排查,把长事务和锁等待纳入日常监控,才能在业务大面积超时之前就发现苗头。