1. 从一条报警短信说起两条SQL的“锁”事凌晨两点三十七分监控平台发来告警短信线上订单系统的死锁次数在五分钟内飙升到47次部分支付回调开始积压。打开日志一看罪魁祸首就两条SQL一条update订单状态一条insert操作流水平时各自跑得飞快偏偏在业务高峰期撞在一起互相揪着锁不放谁也等不到谁最后双双回滚。这种戏码在MySQL的日常运维里不算罕见但真落到自己头上尤其还是大半夜被叫起来滋味确实不好受。这篇文章就把这次完整排查过程从头到尾捋一遍从现象识别、原理拆解、工具定位到方案落地附带几张当时记录的锁等待时序图文字版和几条可以直接抄走的巡检SQL希望能帮到正在被死锁问题折磨的同行。先交代一下背景MySQL 5.7.26InnoDB存储引擎默认隔离级别Repeatable Read订单表orders和流水表order_logs两张表都是线上核心业务表数据量分别在800万和3000万级别。触发死锁的业务场景是支付回调处理同一笔订单在极端情况下会被两个不同的服务节点同时拉起回调逻辑导致两条SQL并发操作同一行或相邻行数据。2. 死锁到底是什么一场互相等钥匙的闹剧要说清楚死锁先得理解InnoDB的锁机制在等什么。可以把一行数据想象成一间更衣室事务A进去换衣服把门锁了加了排他锁事务B也想进去只能站在门口等A出来。如果这时候事务A又想去B正在用的另一间更衣室而B也等着A用的这间两边都手握一把钥匙、眼巴巴望着对方手里的另一把谁都不松手这就成了死锁。MySQL的死锁检测机制每秒钟会扫描一次锁等待图一旦发现有循环等待的环就会立刻挑一个牺牲者回滚它的事务释放它持有的锁让另外一个事务能走下去。所以死锁并不等于事务永远卡死而是系统主动打破了僵局代价是牺牲者的操作直接失败应用层如果没做好重试就会把错误抛给用户。关键问题在于为什么两个看起来毫无交集的SQL会产生锁冲突这要从InnoDB的行锁机制说起。InnoDB的行锁是建立在索引之上的也就是说执行update或delete时优化器会通过索引扫描定位目标行然后在扫描过程中对访问到的每一行加锁。这里就藏着一个大坑如果WHERE条件上的列没有索引InnoDB只能走全表扫描等于把整张表的所有行都锁了个遍哪怕最后只更新一个目标行。更隐蔽的情况是二级索引与主键索引的组合。假设我们在user_id字段上建了二级索引执行update order_logs set status 1 where user_id 123时InnoDB会先通过二级索引锁定匹配的索引记录再回表锁定主键对应的聚簇索引记录两把锁都拿到才会真正修改数据。如果另一条SQL以另一种顺序访问同一批行的索引和主键比如先走主键再回查二级索引锁的获取顺序就可能交错给死锁埋下伏笔。回到这次的场景。两条SQL分别是-- SQL A更新订单状态 UPDATE orders SET status PAID, paid_time NOW() WHERE order_id 1001 AND status UNPAID; -- SQL B插入流水记录 INSERT INTO order_logs (log_id, order_id, action, created_at) VALUES (50001, 1001, PAY_CALLBACK, NOW());只看SQL本身一个是update一个是insert一个是更新订单主表一个是插入流水子表业务上虽然是同一笔订单的操作但操作的表不同怎么会在锁上纠缠不清答案藏在事务边界和外键约束里还有InnoDB的间隙锁与插入意向锁的相互作用。下面逐步拆解。3. 排查第一站让现场证据说话遇到死锁第一反应不是去猜而是把现场信息抓全。InnoDB自己就带了一个死锁日志的存储机制只要出现了死锁最近的死锁信息会记录在内存里可以用一条命令拉出来看SHOW ENGINE INNODB STATUS\G重点关注其中的LATEST DETECTED DEADLOCK段落。我们当晚拉到的日志大致长这样脱敏简化------------------------ LATEST DETECTED DEADLOCK ------------------------ 2025-03-12 02:31:47 0x7f4a3c1b1700 *** (1) TRANSACTION: TRANSACTION 824671, ACTIVE 12 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 892034, OS thread handle 140145078128384, query id 847122 10.10.3.8 app_user updating UPDATE orders SET status PAID, paid_time NOW() WHERE order_id 1001 AND status UNPAID *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 58 page no 786 n bits 168 index PRIMARY of table mall.orders trx id 824671 lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 824672, ACTIVE 9 sec starting index read mysql tables in use 2, locked 2, locked 2 LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 892035, OS thread handle 140145076164864, query id 847125 10.10.3.9 app_user insert INSERT INTO order_logs (log_id, order_id, action, created_at) VALUES (50001, 1001, PAY_CALLBACK, NOW()) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 58 page no 786 n bits 168 index PRIMARY of table mall.orders trx id 824672 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 58 page no 786 n bits 168 index PRIMARY of table mall.orders trx id 824672 lock_mode X locks rec but not gap waiting日志信息量大但初次接触的人容易看懵。耐心拆解一下就会发现两个事务都在等同一个资源orders表主键索引上的某一行的排他锁。事务1update在等这行锁因为它要更新这一行事务2insert明明是在插入order_logs为什么也在等orders表这行的锁这就得说到外键约束了。orders和order_logs之间建有外键约束order_logs.order_id关联了orders.order_id。InnoDB对含外键约束的表的处理有一条隐式规则插入子表记录时要先对父表对应的那一行加共享锁S锁用来确认父表记录存在且没有被删除。事务1先拿到了该行orders记录的排他锁X锁准备更新事务2插入order_logs时需要同一行orders记录上的共享锁。共享锁和排他锁互斥事务2只能等。问题是事务1的update本身是一个更大事务的一部分这个事务在某个更早的时间点已经插入过另外一条order_logs记录而那条记录又触发了对orders表另一行的共享锁请求更复杂的情况是两张子表记录之间存在交叉引用需求链条在这里扯成了一个环。简化还原当时的锁等待链条事务A持有orders表order_id1001这行的X锁等待order_logs表某条记录的插入意向锁。事务B持有order_logs表某条记录或间隙的锁等待orders表order_id1001这行的S锁。两边各握一头刚好绕成一个圈。而InnoDB的死锁检测器每秒扫描检测到这个环之后选择了事务A作为牺牲者回滚代价就是那笔订单的支付更新失败应用层收到 Deadlock found when trying to get lock; try restarting transaction 的报错。所以排查死锁第一件事一定不是看业务代码而是看SHOW ENGINE INNODB STATUS里的TRANSACTION段和锁信息段。这是最权威的现场记录比任何日志框架里的业务堆栈都可靠。日志里明确告诉你了谁持有哪把锁、谁在等哪把锁、哪个事务被回滚把这几条对起来死锁链条基本就浮出水面了。4. 原理复盘为什么两条“正常”SQL会踩进死锁陷阱拿到死锁日志只是第一步更关键的是从原理层面想明白为什么这些锁会竞争为什么锁的等待会形成环忽略原理只改SQL往往是治标不治本。4.1 InnoDB锁类型与兼容矩阵一张表理清关系InnoDB的锁从粒度上分有行级锁、间隙锁、表级锁主要在DDL和元数据锁场景从模式上分有共享锁S、排他锁X、插入意向锁、自增锁等等。行级锁里还有记录锁record lock和间隙锁gap lock的细分在Repeatable Read隔离级别下间隙锁会自动启用用来防止幻读。判断两个事务会不会互相等待核心是看S锁和X锁的兼容性锁类型共享锁(S)排他锁(X)插入意向锁共享锁(S)兼容互斥兼容排他锁(X)互斥互斥互斥插入意向锁兼容互斥互斥注意后面两行插入意向锁之间是互斥的。这意味着并行插入到同一个间隙的多个事务如果间隙里的记录锁没释放会互相排队这也是插入死锁的一个温床。4.2 索引选择决定锁范围一个没走对索引的更新可能锁半张表在排查死锁的过程中最容易忽略的变量就是执行计划的锁范围。看这次死锁日志里的事务1update语句的WHERE条件是order_id 1001 AND status UNPAID。order_id是主键但status是普通字段如果优化器选择先通过某个二级索引过滤status再回表锁定主键行锁定的记录就不仅是order_id1001这一行而是所有statusUNPAID且满足其他条件的记录。假设orders表上有这样一个复合索引(idx_status_created)优化器认为status过滤性更好于是走了这个索引锁的边界就从一行扩大到了一批。这时候另一条SQL只要命中了这批锁范围内的任意一行都会发生阻塞。锁范围越大两个事务的锁覆盖区域越容易产生交集死锁概率跟着指数级上升。反过来看事务2insert操作本身只插入一行但因为外键约束要对父表orders的对应行加S锁等于把锁竞争引入到orders表上了。这里有一个很多人忽视的细节外键约束的父表加锁范围不是精确到主键值而是根据外键列在父表上命中的索引来定位。如果order_id在orders表上是主键精确到一行问题不大如果外键关联的是普通索引列且存在重复值锁定的就是一组记录范围又被放大了。死锁本质是锁竞争和资源获取顺序的冲突。要形成死锁必须具备四个条件互斥、持有并等待、不可剥夺、循环等待。InnoDB的设计天然满足前三条所以应用层能不能打破第四条也就是让所有事务以相同的顺序获取资源就是避免死锁的根本思路。4.3 事务边界膨胀一个隐形的死锁放大器除了锁本身事务的边界长度也会显著影响死锁概率。还是这次场景两个服务节点同时回调同一笔订单时每个节点内部都跑着一个长事务事务里除了update和insert还夹杂了调用会员服务、发送消息通知等RPC操作。事务迟迟不提交持有的锁就不释放这条锁被占用的时间越长其他事务撞上来的概率就越大。那个凌晨我们查看从库的information_schema.innodb_trx表时发现有两个事务已经open了超过20秒远高于正常事务几毫秒的水平。加锁时间拉长本质上等于把死锁的窗口期放大就算两条SQL本身设计合理只要执行时间错开足够久也不会形成环。所以排查死锁看完锁本身还要看事务的持续时间、提交时机这两者往往是更深层的原因。5. 定位死锁的实用工具箱从静态日志到动态监控死锁排查没有银弹但有一套组合拳可以快速缩小范围。这次实际用到的工具和命令可以整理出来遇到类似问题可以直接照着做。5.1 SHOW ENGINE INNODB STATUS的前世今生与正确姿势这条命令是排查死锁的第一利器但它有个小坑它只记录最近一次死锁如果死锁频繁发生旧的信息会被覆盖。所以线上每次死锁发生第一时间就要跑这条命令把输出留存下来并且最好把输出重定向到文件里避免在终端里滚屏丢信息。SHOW ENGINE INNODB STATUS 的输出里LATEST DETECTED DEADLOCK 只保留最新一条死锁记录如果线上死锁非常频繁务必多次抓取并结合业务日志交叉分析。通过命令输出的TRANSACTION段可以读到每个事务的id、状态、持有锁数量和等待锁数量这组数据直接告诉你事务的锁体量大小。有些死锁虽然不致命但锁结构数量异常其实是在提醒你索引使用有问题别只盯着死锁本身。5.2 实时事务查询直接看当前谁握着锁死锁日志是事后还原想知道当下这一刻谁在等谁比较直观的方法是查information_schema下的三张表-- 查询当前正在运行的事务 SELECT trx_id, trx_state, trx_started, trx_rows_locked, trx_rows_modified FROM information_schema.innodb_trx ORDER BY trx_started; -- 查询锁等待关系 SELECT * FROM sys.innodb_lock_waits\G -- 查看具体行锁信息 SELECT * FROM performance_schema.data_locks\G这三张表一层套一层innodb_trx告诉你有哪些事务活着、活得多久sys.innodb_lock_waits告诉你锁等待的依赖关系谁卡谁一目了然performance_schema.data_locks能给出更细致的锁对象信息包括锁在哪张表的哪个索引、锁的类型。不过说句实在话线上高并发下性能表的查询本身有开销不能在核心链路随便跑。比较稳妥的做法是把这些查询做成一个只读账号能访问的巡检脚本每分钟执行一次把结果写到监控系统只在异常发生的时候回放分析。这个思路和我们当时处理问题的路径一致先应急看现场再补长期监控。5.3 perf与堆栈分析定位锁是从哪一行代码产生的数据库层面定位到锁还不够还要落到代码上。死锁日志里能看到OS thread id和MySQL thread id可以通过performance_schema.threads表映射到processlist id再对比应用侧打印的错误日志找到出问题的服务节点和调用链路。更精细的做法是开performance_schema的statement history记录每个事务执行过的SQL语句序列还原事务完整的执行路径。这一步往往被很多人跳过但它恰恰是最能挖出事务里还干了别的事这个根因的手段。我们当时就是通过statement_history发现事务B在执行insert之前已经先执行了一次select订单的for update查询锁的获取顺序和事务A正好相反。6. 从根源入手停用外键还是改造事务先分清主次原理和工具都过了一遍真正回答问题的时候到了这个死锁怎么根除方案不止一个不同方案的优先级和代价差别很大。6.1 方案一该不该禁用外键约束外键约束是这次死锁的导火索之一。insert order_logs时对父表orders加S锁这个行为是外键约束带来的固有代价。很多互联网团队在数据库设计阶段就明确不用外键由应用层来保证数据一致性目的之一就是为了避免这类隐式锁带来的死锁隐患。但要不要跟着一刀切得理性看待。如果业务对父子表一致性要求极高外键有它的价值尤其是防止脏数据产生。只是要认识到外键不是免费的它的每一次子表写入都会给父表带一次额外的锁请求在写入密集场景下这个锁竞争就是死锁的温床。如果确实决定停用外键操作上要注意先确认子表和父表的数据完整性再alter table drop foreign key最后在应用层补上约束校验逻辑。主从同步架构下建议在业务低峰期执行避免大表DDL带来主从延迟。6.2 方案二缩小锁范围索引优化才是最优雅的解法核心矛盾在锁的范围那最干净的做法就是让SQL的锁范围精确化。回到事务1那条update它真正想锁的就是订单1001那一行但为了避免更新丢失又加了一个statusUNPAID的条件。这是典型的乐观锁写法问题是这个条件如果没走联合索引锁的范围可能被放大。把order_id status建成联合索引还是让where条件只走主键、把status判断放到更新后的应用层校验可以按业务并发量取舍。联合索引方案下InnoDB通过索引定位到恰好一行锁的范围就是一行干净利落。但如果status字段本身变化频繁索引维护成本也要评估。我们当时的做法是把SQL调整为UPDATE orders SET status PAID, paid_time NOW() WHERE order_id 1001;然后在应用层先查一次订单当前状态确认是UNPAID再执行更新。这个改动把锁精确到一行死锁概率大幅下降。代价是多了额外的查询但对单行主键查询来说成本微乎其微换个稳定性很值。6.3 方案三让事务的执行顺序统一打破循环等待前面说过死锁形成的条件之一就是资源获取顺序不一致。一个事务先insert order_logs再update orders另一个事务先update orders再insert order_logs两边都等对方先释放锁这就是顺序冲突。解决办法很直接让所有涉及多表操作的事务都以固定的顺序获取资源。比如定义凡是涉及订单和流水的操作一律先获取orders表的锁再操作order_logs表。这个规则看起来简单落地起来要梳理所有调用路径尤其是多个微服务之间的事务如果跨越了服务边界还需要在接口层约定调用顺序。多说一句分布式场景下如果事务真正跨越了多个数据库那就不在InnoDB死锁的管辖范围里了。那种情况要么用分布式事务框架协调要么通过消息队列做最终一致性属于另一个层面的技术选型问题。6.4 方案四兜底的超时与重试机制必须有死锁不可能100%根除尤其业务还在快速迭代的时候。因此应用层一定要有兜底策略捕获Deadlock错误做有限次数的重试重试之间加随机延迟避免多个客户端同时重试再次碰撞。// 典型的重试实现 int retryCount 3; while (retryCount 0) { try { orderService.handlePayCallback(orderId); break; } catch (DeadlockLoserDataAccessException e) { retryCount--; Thread.sleep(ThreadLocalRandom.current().nextInt(50, 200)); if (retryCount 0) { // 超过重试次数落到死信队列或人工处理 log.error(order pay callback failed after retries, e); } } }这个重试不是为了掩盖问题而是给系统的自愈能力留一个空间。死锁回滚只会回滚当前事务不会破坏其他事务的数据所以重试是安全的只要保证操作的幂等性即可。幂等性这个点要提前设计好比如更新订单状态时带上预期的状态条件或者用唯一键约束防止insert重复。7. 巡检SQL与监控预警配置把死锁杀在萌芽状态经历过一次凌晨两点半的折腾后我养成了一个习惯不管项目忙不忙先给数据库加上死锁雷达。这个雷达不是多复杂的系统就是把之前排查用到的一些查询组合成一个巡检脚本定时跑发现问题早报警。分享几个直接可用的巡检脚本片段-- 查看当前是否有锁等待 SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, TIMESTAMPDIFF(SECOND, r.trx_started, NOW()) AS wait_age_sec, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, b.trx_query AS blocking_query FROM information_schema.innodb_lock_waits w JOIN information_schema.innodb_trx r ON w.requesting_trx_id r.trx_id JOIN information_schema.innodb_trx b ON w.blocking_trx_id b.trx_id; -- 查看长事务 SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_seconds, trx_rows_locked, trx_rows_modified FROM information_schema.innodb_trx WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) 10;第一个脚本查锁等待关系第二个脚本查长事务。长事务是死锁的放大镜事务开得越久锁持有的时间就越长碰撞概率越高。监控上把这两个指标设上阈值锁等待超过5秒告警事务运行超过30秒告警基本能把绝大多数死锁问题暴露在萌芽阶段。如果用的是性能监控工具比如Prometheus加mysqld_exporter也可以在采集器里加上innodb_row_lock_waits和innodb_row_lock_time这两个指标配合Grafana画一条趋势线死锁次数超过两位数就触发告警。用自动化代替人工盯屏幕比什么都香。8. 复盘与思考除了修好SQL还有三件更重要的事死锁修复不是一个SQL改写就结束的事它牵出的往往是一连串系统性问题。这次排查到凌晨的case除了把SQL修好还暴露了架构和流程上的几个薄弱点顺手一起补上。第一支付回调接口没有设计成幂等。同一笔订单在不同服务节点同时回调时由于缺少全局去重两个请求都进了业务逻辑。正常应该有一把分布式锁或者数据库层唯一的业务键约束保证同一笔订单的处理请求只有一个能走到数据库写操作。这个改动比死锁SQL本身更重要因为从源头把并发请求消掉了死锁自然没有发生的前提。第二事务里混入了RPC调用。那个长了20秒的事务并不是数据库操作本身慢而是事务内部调用了一个响应超时的会员服务接口。RPC网络等待把事务的提交时间拉长锁也跟着被占用很久。规范的做法是把RPC调用移出事务边界事务里只放必需的数据库读写宁可多查一次也别把外部调用的不确定性带进锁的生命周期里。第三值班预警体系的告警泛滥问题。当晚其实死锁连续出现了好几轮但第一轮告警被值班同学当成偶发错误直接忽略直到业务侧开始积压才升级。后来我们把死锁告警做了分级连续5分钟超过阈值才触发P1级电话通知减少噪音的同时确保真问题不被吞掉。这些复盘内容乍看和死锁没有直接关系却是这次事故能得到根治的关键。技术问题的根往往不在技术本身这句话在故障处理中反复被验证。最后再说一个小的实操心得处理凌晨故障头脑最容易犯迷糊动手改任何东西之前先把当时的SHOW ENGINE INNODB STATUS输出、事务列表、binlog位置点都留好备份。等第二天恢复精神再回看很多当时觉得玄学的现象其实只是某个锁等待关系被漏看了。留好现场记录比急着修好更有价值。