MySQL死锁实战复盘：从日志定位到加锁顺序优化方案

凌晨两点十四分手机震动把我从“刚合眼”的状态里硬拽了出来。值班同学在群里喊了一句“订单库一直在报 ERROR 1213工单刷了快二十条了。”我揉着眼睛把电脑打开慢日志里那两条 UPDATE 已经排在最前面。MySQL 死锁这种问题说大不大说小不小但它如果每隔几分钟就来一轮业务侧就是一片超时、重试和失败堆积对账任务也会跟着出错线上体验直接雪崩。当时的场景特别有意思——两条 SQL 明明条件差不多、语义也差不多的更新语句却在 InnoDB 里“互相看不顺眼”各自攥着一把锁不肯放还都想等对方手里的那一把。结果就是典型的死锁InnoDB 检测到循环等待主动回滚了其中一个事务应用层收到 40001 报错。今天就把这起从凌晨查到天亮的死锁事故完整复盘一下包括 InnoDB 的锁机制、死锁日志怎么读、根因怎么定位以及最后落地的几个解决办法。如果你也在写批量更新、定时任务、或者经常和数据库锁打交道这篇内容应该能帮你省几个加班的深夜。我会尽量把底层原理讲明白同时给出一套可以直接操作的排查思路和避坑清单。1. 现场还原先看两条咬起来的 UPDATE 长什么样1.1 表结构与业务语义这是一张很常见的订单表线上环境是 MySQL 8.0.30隔离级别是默认的 REPEATABLE READ。表结构大概长这样CREATE TABLE order_info ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, user_id bigint NOT NULL, order_type tinyint NOT NULL, status tinyint NOT NULL DEFAULT 0, pay_amount decimal(12,2) DEFAULT NULL, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), KEY idx_order_type (order_type) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;业务上有两个入口在批量更新订单状态一个是“按用户维度”把某个用户名下的订单统一置为失败或关闭比如用户在退款投诉流程里被触发的状态批量修正另一个是“按订单类型维度”把某类业务类型的订单统一置为已处理通常是定时任务在扫描积压数据。这两套逻辑分别跑在两个独立的事务里平时各干各的很少撞车。但凌晨那波告警偏偏就撞上了而且撞得结结实实。1.2 两条 UPDATE 和它们的“尴尬相逢”慢日志里抓出来的两条核心语句从业务上看几乎没什么区别-- 事务 T1 中的第一条更新 UPDATE order_info SET status 9 WHERE user_id 10086; -- 事务 T2 中的第一条更新 UPDATE order_info SET status 5 WHERE order_type 2;单看任何一条都挑不出毛病where 条件都走索引更新范围也就是一个用户、一个类型的订单数。可问题在于这两个事务后面还各自带着“第二脚”-- 事务 T1 继续执行第二条更新 UPDATE order_info SET status 4 WHERE order_type 2; -- 事务 T2 继续执行第二条更新 UPDATE order_info SET status 9 WHERE user_id 10086;看到这里你应该已经察觉到问题了。T1 先按 user_id 加锁再想拿 order_type 的锁T2 先按 order_type 加锁再想拿 user_id 的锁。两个事务各自握着一部分订单记录的锁又同时去等对方手里那部分记录的锁。凌晨那一刻两张索引上的锁正好交叉两条 SQL 谁也不肯先松手InnoDB 的死锁检测机制直接让它们“掰手腕掰到死循环”。为什么两条 SQL 条件差不多却能产生完全相反的加锁顺序这背后是 InnoDB 的加锁规则和执行计划选择的问题下一节我们把底层拆开看。2. 底层规则InnoDB 为什么会让两条 SQL 互相卡死2.1 行锁、间隙锁和临键锁一次讲透要理解死锁得先理解 InnoDB 在事务里到底给什么上了锁。很多人以为 UPDATE 就是“锁住被更新的那一行”其实没这么简单。InnoDB 的锁按粒度可以分为三类Record Lock记录锁锁住索引上的某一条记录只锁这一行。Gap Lock间隙锁锁住记录之间的“空隙”防止其他事务在这个范围内插入新记录。间隙锁只存在于 RR可重复读隔离级别下也是 InnoDB 解决幻读问题的核心手段。Next-Key Lock临键锁记录锁和间隙锁的组合锁住“一条记录以及它前面的间隙”实际上是一个左开右闭的区间。这三类锁不是并列关系而是层层叠加。比如你在 RR 隔离级别下执行UPDATE ... WHERE user_id 10086如果走了idx_user_id索引那它不只是把 user_id 等于 10086 的所有记录给锁住还会在索引扫描范围内加上临键锁限制其他事务往这个范围里插入新记录。这样做的代价就是锁范围比你以为的要宽得多。用一个生活类比你在书架的某一层抽了三本书出来改标注但你为了防止别人趁你改的时候往空档里塞新书干脆把这一整层的“空位”也给占住了。别人不只要等那三本书连想往这层塞一本新书都得等你改完。这也是为什么两条看起来只是“更新少量记录”的 SQL在并发场景下锁冲突范围会比想象中大很多。2.2 加锁顺序与执行计划的关系InnoDB 的锁不是“一次性全部加好”的而是按记录一条一条地加。加锁的顺序取决于执行计划——也就是优化器选定的索引扫描顺序。比如UPDATE ... WHERE user_id 10086如果优化器选择走idx_user_id那加锁顺序基本是按idx_user_id上定位到的记录来排的。而UPDATE ... WHERE order_type 2如果走了idx_order_type加锁顺序就变成了按order_type索引上的记录顺序来排。这里有一个关键点两条 SQL 的条件虽然一模一样但优化器完全可能给它们选择不同的索引路径。比如一张表上同时存在idx_user_id和idx_order_type两个单列索引两条 SQL 各自的 where 条件里列的先后顺序不同统计信息不同优化器估算出的扫描行数也不同于是一个选了idx_user_id另一个选了idx_order_type。一旦两个事务走的是不同的索引路径它们加锁顺序就各按各的来。如果更新范围覆盖了有交集的记录非常容易形成交叉等待。这就是“两条 SQL 互相看不顺眼”的底层原因之一不是语句本身有语法问题而是它们在相同数据集上按相反的顺序去抢锁。2.3 死锁四要素与现场演绎死锁的四个必要条件教科书上写过互斥、持有并等待、不可剥夺、循环等待。把这四个词映射到凌晨那次事故里就是互斥同一行记录一个事务加了 X 锁另一个事务不能再加 X 锁。持有并等待T1 已经拿到了某个用户的订单记录锁还在等 order_type 相关记录的锁T2 相反。不可剥夺InnoDB 默认不会主动去抢另一个事务持有的锁只能等对方提交或回滚。循环等待T1 等 T2 放锁T2 等 T1 放锁形成闭环。InnoDB 有死锁检测机制默认开启innodb_deadlock_detectON。它会在事务获取锁失败时构建等待图发现循环等待后主动回滚其中一个“代价较小”的事务也就是 undo log 较少的那一个然后把ERROR 1213 (40001): Deadlock found抛给对应的客户端。业务侧看到的就是一条更新失败。所以死锁的本质不是“MySQL 卡住了”而是两个事务按相反顺序申请同一批资源。找到根因的关键就是去死锁日志里还原它们的加锁路径。3. 证据链用死锁日志还原案发过程3.1 第一手材料怎么拿排查死锁最重要的第一手材料就是 InnoDB 自己写的死锁日志。在 MySQL 里执行这条命令SHOW ENGINE INNODB STATUS\G;这个命令会输出一大段会话状态我们只需要关注其中的LATEST DETECTED DEADLOCK段落。注意它只保存最近一次死锁的信息如果死锁已经发生过很多次日志只覆盖最新的一轮所以看的时候要抓紧。还有一个参数建议线上直接打开SET GLOBAL innodb_print_all_deadlocks 1;这个开关打开后每次发生死锁都会追加打印到 MySQL 错误日志里而不仅仅是覆盖最近一次。对于高频死锁的排查打开这个参数几乎等于装了一个免费的记录仪强烈建议在比较重要的业务实例上开启。另外 MySQL 8.0 用户还可以查两张性能表SELECT * FROM performance_schema.data_locks; SELECT * FROM performance_schema.data_lock_waits;这两张表可以实时看到当前有哪些事务锁、锁在哪个索引的哪条记录上、谁在等谁。不过它们只能反映“当下”的状态死锁发生后事务可能已经回滚现场信息就没了所以最靠谱的还是死锁日志本身。3.2 死锁日志关键片段解读我截取当时日志里比较关键的片段稍微脱敏后给大家看------------------------ LATEST DETECTED DEADLOCK ------------------------ 2025-01-18 02:14:31 0x7f9a8c2a1700 *** (1) TRANSACTION: TRANSACTION 8123456, ACTIVE 3 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 1046, OS thread handle 140312345678900, query id 56789 10.2.3.4 app_user update UPDATE order_info SET status 9 WHERE user_id 10086 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 18 page no 12 n bits 120 index PRIMARY of table test.order_info trx id 8123456 lock_mode X waiting Record lock, heap no 88 PHYSICAL RECORD: ... *** (2) TRANSACTION: TRANSACTION 8123457, ACTIVE 3 sec starting index read mysql tables in use 1, locked 1 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 1051, OS thread handle 140312345678901, query id 56790 10.2.3.5 app_user update UPDATE order_info SET status 5 WHERE order_type 2 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 18 page no 12 n bits 120 index PRIMARY of table test.order_info trx id 8123457 lock_mode X Record lock, heap no 88 PHYSICAL RECORD: ... *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 18 page no 8 n bits 112 index idx_user_id of table test.order_info trx id 8123457 lock_mode X waiting Record lock, heap no 32 PHYSICAL RECORD: ... *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 18 page no 8 n bits 112 index idx_user_id of table test.order_info trx id 8123456 lock_mode X Record lock, heap no 32 PHYSICAL RECORD: ... WE ROLL BACK TRANSACTION (2)这段日志信息量很大逐行拆开看TRANSACTION 8123456是事务 T1它执行的语句是UPDATE ... WHERE user_id 10086状态是LOCK WAIT说明它在等锁。WAITING FOR THIS LOCK TO BE GRANTED里写得很清楚它在等的是index PRIMARY ... lock_mode X waiting。也就是某条主键记录的 X 锁。事务 T2 执行的是UPDATE ... WHERE order_type 2它在HOLDS THE LOCK(S)里握着的正是 T1 等的那条主键记录的 X 锁。同时T2 也在WAITING FOR THIS LOCK TO BE GRANTED里写明它在等index idx_user_id ... lock_mode X waiting而这个idx_user_id上的锁恰恰是 T1 持有的。最后一行WE ROLL BACK TRANSACTION (2)说明 InnoDB 选择回滚了事务 T2因为 T2 在这个死锁场景里的回滚代价相对更小。3.3 从日志推出两条 SQL 的真实加锁顺序结合死锁日志整个案发过程就非常清楚了T1 先执行WHERE user_id 10086的更新通过idx_user_id找到了目标记录先给辅助索引上的记录加了锁再给对应的主键记录加了锁。它手里握着idx_user_id和PRIMARY两把锁。随后 T1 又去执行第二条更新WHERE order_type 2需要对应的主键记录锁而这正是 T2 已经锁住的那部分。T2 这边正好反过来。它先从idx_order_type定位到了同一批记录里的某些行拿到了对应的主键锁然后又去执行第二条更新WHERE user_id 10086想拿idx_user_id上的锁但被 T1 抢先握住了。所以日志里出现了非常对称的等待关系T1 持有idx_user_id的锁等待PRIMARY上某条记录的锁。T2 持有PRIMARY上某条记录的锁等待idx_user_id的锁。这两个事务是同时进入“持有并等待”状态的。InnoDB 死锁检测发现循环等待后立即回滚了 T2并把错误返回给应用层。这也符合日志里WE ROLL BACK TRANSACTION (2)的判断。到这里根因基本定位两条 UPDATE 走了不同的辅助索引导致加锁顺序相反在共享记录集合上形成了环形等待。4. 三条解法和落地细节4.1 第一条统一 SQL 与加锁顺序从源头消除环形等待既然死锁的原因是加锁顺序相反那最直接的办法就是让所有并发事务都按同一个顺序去加锁。具体操作上第一步是把业务逻辑改成“同一个批处理任务里只按一种维度先加锁”。比如把所有订单批量更新的入口都改成先按user_id取数据再统一order_type而不是有的任务先按用户、有的任务先按类型。这样两个事务会先抢同一把锁抢不到的那个直接排队而不是各自占着一半再互相等。它牺牲了一部分并发度但换来了确定性。如果业务上做不到完全串行还有一个很实用的折中方案在某张配置表里锁一行“协调记录”。所有批量更新事务在真正 update 订单前都先执行SELECT ... FROM batch_lock WHERE biz_type ORDER_UPDATE FOR UPDATE;拿到这行的锁之后才继续往下走。这相当于在应用层做了一把全局互斥锁确保同一时刻只有一个大事务在批量更新订单。这把“协调锁”持有的时间很短但能非常有效地避免多个大事务同时撞进同一批数据。4.2 第二条调整索引与执行计划让优化器别乱选路径那两条 SQL 条件一样为什么优化器给了不同的执行计划说白了MySQL 是根据统计信息估算代价的。你可以在客户端跑一下 EXPLAIN 看看两条 SQL 的实际选择EXPLAIN SELECT * FROM order_info WHERE user_id 10086 AND order_type 2; EXPLAIN SELECT * FROM order_info WHERE order_type 2 AND user_id 10086;虽然条件顺序不同但对于优化器来说最终定位的数据集合是一样的。可如果两条 SQL 各自单独执行优化器可能一个选了idx_user_id另一个选了idx_order_type。更高频的情况是同一张表上同时存在多个单列索引优化器根据“估计扫描行数”去选而这个估算并不总是准确尤其当表里数据分布不均匀、或者统计信息过期时就可能选出不同的索引。针对这种情况一个稳的做法是给表建一个复合索引比如(user_id, order_type)让所有更新语句都通过这个复合索引定位记录避免优化器在单列索引之间摇摆。也可以使用FORCE INDEX强制指定UPDATE order_info FORCE INDEX (idx_user_id) SET status 9 WHERE user_id 10086 AND order_type 2;但FORCE INDEX是个双刃剑它确实让加锁顺序可控但如果数据量增长后该索引选择性变差更新会变慢。所以更适合的做法是先统一 SQL 的 where 条件写法让所有批量更新都按同一个复合索引的最左前缀来定位同时对表执行ANALYZE TABLE保持统计信息新鲜。还有一个非常经典的解法把大范围 UPDATE 改成先 SELECT 主键 ID再按 ID 顺序逐条 UPDATE。主键的加锁顺序是全局唯一的按ORDER BY id升序逐条更新两个事务就算同时跑也会在第一条记录上就分出先后不会再形成交叉等待。这也是我后来最常推荐给业务团队的写法SELECT id FROM order_info WHERE user_id 10086 ORDER BY id; -- 拿到 id 列表后按 id 升序逐条执行 UPDATE order_info SET status 9 WHERE id ?;同样的另一条 SQL 也可以走同样的逻辑。这样不仅锁顺序完全一致事务持有锁的时间也大幅缩短死锁基本被结构性消灭。4.3 第三条把大事务拆小给数据库减负死锁还有个隐藏放大器——事务太大。两个事务各自更新了几千行甚至上万行以后持有锁的范围变宽、时间变长互相碰撞的概率指数上升。所以第三步就是拆事务、缩小加锁粒度。实际操作上可以在应用层把批量更新任务拆成每批 200 条或 500 条一批一个事务批次之间sleep一小段时间。对账、补偿、扫描类任务尤其适合这种处理方式。比如原来的定时任务一次把所有满足条件的订单更新完我们改成循环分页拉取主键每 500 个 id 执行一轮批量更新提交事务后再拉下一批。同时应用层必须做好死锁重试。在 Java 项目里可以捕获异常并判断错误码try { jdbcTemplate.update(UPDATE order_info SET status 9 WHERE user_id ?, userId); } catch (DeadlockLoserDataAccessException e) { // ERROR 1213死锁回滚随机退避后重试 Thread.sleep(ThreadLocalRandom.current().nextLong(50, 200)); jdbcTemplate.update(UPDATE order_info SET status 9 WHERE user_id ?, userId); }重试逻辑的关键是加随机退避避免重试的多个事务再次以同样节奏撞在一起。这个代码模式看着简单但真能帮线上扛过不少“凌晨尖峰”。5. 复盘清单死锁排查的正确姿势5.1 排障工具与命令速查表这里的命令和工具我按照排查顺序整理成一张速查表方便你下次遇到死锁直接抄作业排查步骤工具 / 命令关键作用查看最近一次死锁现场SHOW ENGINE INNODB STATUS\G;输出最近的死锁日志包含事务与锁等待详情持续记录每次死锁SET GLOBAL innodb_print_all_deadlocks 1;让所有死锁都写入错误日志便于事后复盘查看当前锁等待查performance_schema.data_lock_waits实时查看谁在等谁的锁查看当前持锁事务查performance_schema.data_locks查看各个事务持有哪些锁、锁在哪个索引看正在执行的事务SELECT * FROM information_schema.innodb_trx;查看事务运行时间、状态、undo 大小历史死锁监控percona-toolkit 里的pt-deadlock-logger周期性抓取死锁日志输出到文件或表里主从环境检查看主库和从库的错误日志确认是否只在主库发生是否与只读实例有关顺带提一句如果你用的是云数据库大部分云厂商的日志系统里也能直接查到死锁记录优先去控制台看一眼往往比连上去敲命令更快。5.2 死锁日志与监控的几个坑有些坑只有排过凌晨的障才会长记性。第一个坑死锁日志只保留最近一次。如果你打开SHOW ENGINE INNODB STATUS的时候已经过去了十几分钟看到的可能已经不是最早的那次死锁了。所以平时就把innodb_print_all_deadlocks1打开别等到凌晨再临时去开。第二个坑死锁回滚的事务不一定是你以为的那个。InnoDB 会回滚 undo log 较少、回滚代价较小的事务而不是“先来后到”里的后到者。所以日志里出现“WE ROLL BACK TRANSACTION (2)”不代表事务 2 一定刚起步有时候它已经执行了挺久。应用层重试逻辑要按事务为单位做而不是简单重放最后一条 SQL。第三个坑别一上来就猜业务先看日志。我见过太多同学遇到死锁第一反应是“是不是那条 SQL 写错了”结果查了半天 SQL 语法毫无问题。死锁的根源是加锁顺序冲突不是语法错误。先看日志先定位锁再去看业务代码这个顺序不能乱。第四个坑innodb_deadlock_detect不要轻易关。在超高并发场景下死锁检测本身有一定性能开销有些人会把它关掉。但关闭之后环状等待会一直卡到innodb_lock_wait_timeout超时才回滚默认 50 秒对业务来说是漫长的不可用时间。除非你非常清楚自己在做什么否则保持默认开启更安全。5.3 日常预防的三条建议复盘完整起事故后我给团队立了三条规矩日常维护非常管用第一批量更新入口尽量统一。所有涉及订单状态批量变更的任务不管业务方是谁都走同一个内部方法。方法内部固定加锁顺序、固定批大小、固定重试策略。把“并发路径多样性”这个最大的变量人为收敛掉。第二SQL 的 where 条件优先走主键或复合索引的最左前缀。主键加锁顺序全局唯一复合索引让优化器有明确的路径可走尽量避免数据库在多个单列索引之间“凭感觉”选路线。加索引不是越多越好单列索引有时候反而是死锁的帮凶。第三事务时间要短锁范围要小。一个事务里不要混太多写操作不要在事务里做远程调用、复杂计算或等待外部响应。锁拿得越久被别人撞上的概率越大。哪怕是业务上需要多步操作也尽量拆成多个独立事务配合补偿机制来保证最终一致性。最后补一句实在话处理完那次事故之后我最大的体会是MySQL 死锁并不可怕可怕的是你连死锁日志都没看就开始猜。把innodb_print_all_deadlocks打开、学会读日志里的HOLDS THE LOCK(S)和WAITING FOR THIS LOCK TO BE GRANTED、统一加锁顺序、拆小事务这四件事做好你基本就能把绝大多数死锁消灭在萌芽里。另外哪怕你已经做好了所有预防应用层还是要把死锁重试当成标配。数据库不是万无一失的它出错时怎么优雅恢复才见真功夫。