17370845950

可快速通过SHOW PROCESSLIST和错误日志识别锁等待与死锁；查INNODB_TRX、INNODB_LOCK_WAITS定位阻塞关系；SELECT ... FOR UPDATE锁范围取决于索引使用，无索引将退化为表锁；死锁日志中需关注WAITING FOR与HOLDS THE LOCK(S)的交叉依赖；避免死锁关键在统一索引访问顺序、缩短事务、拆分批量操作。

如何快速识别 MySQL 中的锁等待和死锁现象

当查询明显变慢、SHOW PROCESSLIST 里出现大量 Waiting for table metadata lock 或 Waiting for table level lock，或者应用报出 Deadlock found when trying to get lock，基本可以确认是锁问题。MySQL 5.7+ 默认开启 innodb_print_all_deadlocks=ON，死锁信息会写入错误日志（error_log），而不是只返回给客户端——这是诊断前提。

• 查当前锁等待：

  SELECT * FROM information_schema.INNODB_TRX\G

  关注 TRX_STATE（LOCK WAIT 表示被阻塞）、TRX_WAITING_LOCK_ID
• 查锁信息：

  SELECT * FROM information_schema.INNODB_LOCKS\G

  （MySQL 8.0 已移除，改用 performance_schema.data_locks）
• 查锁等待关系：

  SELECT * FROM information_schema.INNODB_LOCK_WAITS\G

  可直接看到谁在等谁、哪个事务持有了哪把锁

为什么 SELECT ... FOR UPDATE 会锁住不该锁的行

InnoDB 的行锁基于索引实现，不是“按 WHERE 条件锁数据”，而是“锁住满足条件的索引记录 + 间隙（gap）”。如果 WHERE 字段没走索引，会退化为表锁或全索引扫描锁——这是最常被忽略的根源。

• SELECT * FROM orders WHERE status = 'pending' FOR UPDATE：若 status 无索引，InnoDB 会扫描并锁定聚簇索引所有记录（相当于整表锁）
• 即使有索引，范围查询如 WHERE id > 100 会锁住 (100, +∞) 的间隙，可能阻塞后续插入
• 唯一索引等值查询（WHERE id = 123）只锁单行；普通索引等值查询会锁该索引记录 + 对应的聚簇索引记录（可能跨页）

死锁日志里 TRANSACTION 和 WAITING FOR 到底怎么看

MySQL 错误日志中的死锁片段看似混乱，其实结构固定：每个 TRANSACTION 块描述一个事务的持有锁与等待锁，WAITING FOR 后面是它卡住的位置，HOLDS THE LOCK(S) 是它已占有的资源。

*** (1) TRANSACTION:
TRANSACTION 123456789, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 456 n bits 72 index PRIMARY of table `db`.`t` trx id 123456789 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 123456790, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 456 n bits 72 index PRIMARY of table `db`.`t` trx id 123456790 lock_mode X locks rec but not gap
WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 457 n bits 80 index idx_name of table `db`.`t` trx id 123456790 lock_mode X locks rec but not gap waiting

关键看两处：(1) 在等主键锁，(2) 持有主键锁却在等另一个索引锁——说明两个事务以不同顺序访问了同一组索引，形成环路。

避免死锁的实操策略比加重试更关键

应用层 try-catch 重试死锁异常（ER_DEADLOCK）只是兜底，真正要减少发生，得从 SQL 执行路径和事务边界入手。

• 所有写操作尽量走相同索引顺序：比如统一先按 user_id 查询再按 order_id 更新，不要一个事务先查 order_id 再更新 user_id，另一个反过来
• 缩短事务时间：把非数据库操作（如 HTTP 调用、计算）移出事务块；避免在事务中 SLEEP() 或用户交互等待
• 批量操作拆分：UPDATE t SET status='done' WHERE id IN (1,2,3,...,10000) 会一次性锁几千行，改成每次 100 行循环执行
• 必要时显式加锁顺序：用 SELECT ... ORDER BY id FOR UPDATE 强制按主键顺序获取锁，避免随机顺序导致竞争

死锁不是配置调参能根治的问题，它暴露的是业务逻辑与数据访问模式之间的隐含耦合——日志里那几行锁信息，本质是两个事务在用不同节奏敲同一扇门。