MySQL出现死锁怎么排查

约 10 分钟读完 4 阅
文章导读
能不能快速解决死锁，关键在于先拿到死锁现场，还是先去改代码。MySQL 报 1213（Deadlock found when trying to get lock）说明 InnoDB 已经检测到环路，并回滚了其中一个事务；报 1205（Lock wait timeout exceeded）则是锁等待超时，两者的处理方式不同。比较稳妥的顺序是：先取出最近一次死锁涉及的两个事务、各自的 SQL、持锁和
📋 目录
  1. 一 MySQL出现死锁先定位在哪一层
  2. 二 MySQL出现死锁：先看这四个位置
  3. 三 MySQL出现死锁：按可能性从高到低排除
  4. 四 MySQL出现死锁：改完怎么确认真的解决了
A A

能不能快速解决死锁，关键在于先拿到死锁现场，还是先去改代码。MySQL 报 1213（Deadlock found when trying to get lock）说明 InnoDB 已经检测到环路，并回滚了其中一个事务；报 1205（Lock wait timeout exceeded）则是锁等待超时，两者的处理方式不同。比较稳妥的顺序是：先取出最近一次死锁涉及的两个事务、各自的 SQL、持锁和等锁记录，再回到应用核对加锁顺序与索引，最后用计数增量确认死锁是否真的不再出现。

死锁排查的顺序通常是：先从 InnoDB 状态输出和错误日志拿到最近一次死锁现场，看清是哪两个事务、各持有什么锁、在等什么锁，再回到应用核对加锁顺序和 SQL 索引；最后用 Innodb_deadlocks 的增量和应用侧的 1213 错误确认死锁是否真的消失。只在数据库端调大锁等待超时或关掉死锁检测，通常只是把报错换个形式出现。

MySQL出现死锁先定位在哪一层

先判断问题落在哪一层，可以避免在数据库参数上反复调整、代码却没动的情况。下面四层各自有对应的检查位置和判断依据，能排除的那层就不用再往下看。

  • InnoDB 引擎层：检查位置是 SHOW ENGINE INNODB STATUS\G 输出里的 LATEST DETECTED DEADLOCK 段。判断依据：这段会列出两个事务的完整 SQL、持有的锁和等待的锁；如果这段是空的，说明最近没有死锁，或者实例重启过，需要开启 innodb_print_all_deadlocks 后从错误日志里找。
  • 应用事务层：检查位置是从 START TRANSACTION 到 COMMIT 之间的所有写操作，以及 ORM 的事务边界。判断依据：死锁段里两个事务执行的语句集合相同但顺序相反，基本可以判定是加锁顺序问题。
  • 索引与执行计划层：检查位置是死锁段里的锁模式（例如 lock_mode X locks gap before rec）和对应 SQL 的 EXPLAIN。判断依据：UPDATE/DELETE 走了全表扫描或大范围扫描，锁住的行远多于业务预期。
  • 连接池与访问层：检查位置是连接池最大连接数、事务超时配置和应用日志里的事务耗时。判断依据：长事务、批量任务与在线请求混跑，把事务持锁时间拉长。

MySQL出现死锁：先看这四个位置

顺序建议是先看命令输出拿到现场，再看日志拿到历史，然后核对配置项，最后对面板字段。前两个位置给出事实，后两个位置解释为什么会出现这种锁。

  1. 命令输出：登录到实例执行 SHOW ENGINE INNODB STATUS\G，只看 LATEST DETECTED DEADLOCK 这一段，其余部分对排查死锁帮助不大。
  2. 错误日志：先执行 SHOW VARIABLES LIKE 'log_error'; 取得日志绝对路径，再在其中搜索 Deadlock found 或 TRANSACTION；前提是 innodb_print_all_deadlocks = ON，否则只有最近一次死锁能在状态输出里看到。
  3. 配置项：核对 transaction_isolation、innodb_lock_wait_timeout、innodb_deadlock_detect 三个值。隔离级别为 REPEATABLE READ 时存在间隙锁，锁范围会比 READ COMMITTED 大。
  4. 面板字段：如果使用云数据库或带监控的实例，在控制台的监控页搜索“死锁”“InnoDB 死锁次数”“活跃事务数”这类指标名；不同厂商命名不一致，以控制台实际字段为准。
-- 1) 最近一次死锁现场
SHOW ENGINE INNODB STATUS\G

-- 2) 当前活动事务，找长时间未提交的那个
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id,
       trx_rows_locked, LEFT(trx_query,120) AS q
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

-- 3) MySQL 8.0 的锁与等待关系
SELECT * FROM performance_schema.data_locks\G
SELECT * FROM performance_schema.data_lock_waits\G

-- 4) 关键配置项
SHOW VARIABLES LIKE 'transaction_isolation';
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
SHOW VARIABLES LIKE 'innodb_deadlock_detect';
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';
SHOW VARIABLES LIKE 'log_error';

如果需要一个能把每次死锁都记下来的开关，可以在排查期间临时打开，写进 my.cnf 的 [mysqld] 段是 innodb_print_all_deadlocks = ON，运行时也可以执行 SET GLOBAL innodb_print_all_deadlocks = ON;（需要相应权限）。打开后每次死锁都会写进错误日志，日志量会上升，定位完成后建议评估是否保留。另外，performance_schema.data_locks 依赖 performance_schema = ON，关闭时这两张表查不到内容。

MySQL出现死锁：按可能性从高到低排除

  1. 加锁顺序不一致：两个事务以相反的顺序更新同两张表或同一批行。确认动作是把死锁段里两个事务的语句按执行顺序抄出来，对比先后顺序；如果只是顺序相反，处理方式是统一加锁顺序，比如都按主键升序更新，或者先更新同一张“入口表”。
  2. 缺索引导致锁范围扩大：WHERE 条件没走索引，UPDATE 会扫到并锁住大量行。确认动作是对死锁段里的 SQL 执行 EXPLAIN，看 key 是否为空、rows 是否远大于预期；补上合适索引后，锁的范围通常会收窄。
  3. 长事务与间隙锁：事务里夹了远程调用、循环写或人工确认，持锁时间被拉长；REPEATABLE READ 下的范围条件还会加间隙锁。确认动作是用上面的 INNODB_TRX 查询按 trx_started 找运行最久的事务，看它的 trx_query 停在哪一步。
  4. 唯一键冲突与批量写入顺序：并发 INSERT ... ON DUPLICATE KEY UPDATE 或分批插入时顺序随机，容易在唯一索引上形成环路。确认动作是看死锁段里是否出现 INSERT 与唯一索引的锁记录，并确认批量任务是否被多线程乱序拆分。
  5. 重试逻辑缺失或过粗：捕获 1213 后整体重跑，可能再次撞上同样的顺序。确认动作是在代码里搜索捕获 1213 的位置，看重试前是否重新读取数据、是否有退避。

这里有一个容易踩的坑：看到 Deadlock found 就去调大 innodb_lock_wait_timeout。死锁是 InnoDB 主动检测到环路后立刻回滚代价较小的事务，改这个超时参数不会影响它的行为，该参数只对 1205 锁等待超时有效。同理，直接关掉 innodb_deadlock_detect 只是把死锁变成锁等待超时，报错时间被推迟，环路本身仍然存在。

MySQL出现死锁怎么排查

MySQL出现死锁：改完怎么确认真的解决了

死锁是概率事件，改完当场不报错说明不了问题。可行的做法是看两个增量：数据库侧的死锁计数，和应用侧 1213 错误的出现频率。两次采样之间求差值，比看累计值更有意义。

-- 数据库侧：间隔一段时间各执行一次，比较 Innodb_deadlocks 的增量
SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';

-- 应用侧错误（MySQL 8.0，需 performance_schema 开启）
SELECT ERROR_NUMBER, ERROR_NAME, SUM_ERROR_RAISED
FROM performance_schema.events_errors_summary_global_by_error
WHERE ERROR_NAME = 'ER_LOCK_DEADLOCK';

-- 错误日志计数，路径以 SELECT @@log_error 的结果为准
grep -c "Deadlock found" /var/log/mysql/error.log

观察多久算稳定，需要结合环境确认：至少覆盖一个完整业务周期，把日终批量、定时任务、活动高峰都包含进去；如果业务有明显的周期性（比如月末跑批），建议连续观察数天，确认 Innodb_deadlocks 的增量不再随业务高峰上涨，应用日志里也不再出现新的 1213 堆栈，再把临时打开的 innodb_print_all_deadlocks 关掉。

回滚步骤要提前写好，不能等出问题再想：

  1. 配置回滚：临时打开的开关执行 SET GLOBAL innodb_print_all_deadlocks = OFF;；如果当初用的是 SET PERSIST，用 RESET PERSIST innodb_print_all_deadlocks; 撤掉持久化值，再确认 SHOW VARIABLES 的输出。
  2. 索引回滚：新增的索引先确认是否有其他查询依赖，再用 DROP INDEX 索引名 ON 表名; 删除；大表上的加索引、删索引建议走在线 DDL 并避开业务高峰。
  3. 代码回滚：把改动前的事务方法保留一个可切换的版本，出现新的死锁模式时先回滚代码，再用 SHOW ENGINE INNODB STATUS\G 重新取现场，避免一边回滚一边继续改。