theme: default themeName: "默认主题" title: "MySQL死锁排查全流程：从一条慢查询到根因定位"

MySQL死锁排查全流程：从一条慢查询到根因定位

凌晨两点，手机振动。监控系统报了一连串MySQL告警——死锁频率异常升高，最近10分钟内出现了47次。

这种级别的死锁频率不是偶发的业务冲突，大概率是某个最近上线的代码变更引入的系统性问题。我打开电脑，SSH连上数据库从库开始排查。

告警现场：先看现象

生产环境跑的是MySQL 5.7.38，主从架构，业务是电商订单系统。告警页面显示死锁集中在`order_stock`这张表上，涉及的SQL有两条：

-- 事务A
UPDATE order_stock SET locked_qty = locked_qty + 1  WHERE order_id = 10086 AND sku_id = 2001 AND warehouse_id = 5;

-- 事务B   UPDATE order_stock SET stock_qty = stock_qty - 1 WHERE sku_id = 2001 AND warehouse_id = 5 AND order_id = 10086;

看SQL本身，操作的是同一行数据，只是字段不同。按理说InnoDB的行锁应该让一个事务等另一个事务提交就行了，为什么会死锁？

第一步：拉死锁日志

直接上`SHOW ENGINE INNODB STATUS`，拉最近一次死锁的完整记录：

SHOW ENGINE INNODB STATUS\G

在输出中找到`LATEST DETECTED DEADLOCK`这一段，关键信息如下：

* (1) TRANSACTION:
TRANSACTION 2847391, ACTIVE 2 sec updating mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 4521, OS thread handle 140234567891200, query id 98234 UPDATE order_stock SET locked_qty = locked_qty + 1  WHERE order_id = 10086 AND sku_id = 2001 AND warehouse_id = 5
* (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 89 page no 456 n bits 72 index idx_sku_warehouse of table `ecom`.`order_stock` trx id 2847391 lock_mode X waiting

* (2) TRANSACTION:
TRANSACTION 2847392, ACTIVE 1 sec updating mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 4523, OS thread handle 140234567899200, query id 98235 UPDATE order_stock SET stock_qty = stock_qty - 1 WHERE sku_id = 2001 AND warehouse_id = 5 AND order_id = 10086
* (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 89 page no 456 n bits 72 index idx_sku_warehouse of table `ecom`.`order_stock` trx id 2847392 lock_mode X locks rec but not gap

* (WE ROLL BACK BACK TRANSACTION (1))

这段信息非常关键。事务1在等`idx_sku_warehouse`索引上的X锁，而事务2持有这个锁。事务2在等什么呢？再往下看，事务2在等主键索引（或另一个索引）上的锁，而这个锁恰好被事务1持有着。

这就是典型的多索引交叉死锁。

第二步：分析锁的持有逻辑

问题出在索引使用路径不同。两条SQL的WHERE条件字段顺序不同，导致MySQL走了不同的索引：

事务A的WHERE是`order_id, sku_id, warehouse_id`，命中了主键（假设主键就是order_id）或者`idx_order`索引，先锁了这个索引的记录，再去锁`idx_sku_warehouse`索引。

事务B的WHERE是`sku_id, warehouse_id, order_id`，命中了`idx_sku_warehouse`索引，先锁了这个索引的记录，再去锁`idx_order`索引。

两个事务各持有一个锁，都在等对方手里的锁。经典的"你等我、我等你"，InnoDB的死锁检测器发现后直接rollback了事务1。

这种问题在代码review阶段很难发现，因为单条SQL本身完全没问题。只有并发执行、锁的获取顺序相反时才会暴露。

第三步：用pt-deadlock-logger抓模式

单次死锁可能是偶发，但47次/10分钟说明这是个高频场景。我用Percona Toolkit的`pt-deadlock-logger`持续抓取死锁记录：

pt-deadlock-logger h=127.0.0.1,u=monitor,p=xxx \
  --dest h=127.0.0.1,D=deadlock_log,t=deadlocks \   --run-time 30m \   --interval 5s \   --create-dest-table

跑30分钟后查`deadlock_log.deadlocks`表，按`LEFT(locked_sql, 100)`分组统计：

SELECT 
  LEFT(locked_sql, 80) AS sql_pattern,   COUNT(*) AS cnt,   MIN(first_seen) AS first_seen,   MAX(last_seen) AS last_seen FROM deadlock_log.deadlocks GROUP BY LEFT(locked_sql, 80) ORDER BY cnt DESC;

结果验证了我的判断：死锁全部发生在`locked_qty +1`和`stock_qty -1`这两条UPDATE之间，没有任何其他SQL卷入。问题范围已经收窄到订单锁定库存这个业务场景。

第四步：深入分析索引和表结构

SHOW CREATE TABLE ecom.order_stock\G

表结构如下（简化）：

CREATE TABLE order_stock (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,   order_id BIGINT NOT NULL,   sku_id BIGINT NOT NULL,   warehouse_id INT NOT NULL,   stock_qty INT NOT NULL DEFAULT 0,   locked_qty INT NOT NULL DEFAULT 0,   UNIQUE KEY uk_order_sku_wh (order_id, sku_id, warehouse_id),   KEY idx_sku_warehouse (sku_id, warehouse_id, order_id) ) ENGINE=InnoDB;

这里有两个索引：

`uk_order_sku_wh`：以order_id开头

`idx_sku_warehouse`：以sku_id开头

事务A走`uk_order_sku_wh`，事务B走`idx_sku_warehouse`，两个索引指向同一行数据但锁定路径相反，死锁就产生了。

第五步：从业务层面解决

解决方案有好几个层面：

方案一：统一索引访问路径。

让两条SQL都用同一个索引，最简单的做法是把WHERE条件的字段顺序写成一致的。比如都用`order_id, sku_id, warehouse_id`的顺序：

-- 事务A（不变）
UPDATE order_stock SET locked_qty = locked_qty + 1  WHERE order_id = 10086 AND sku_id = 2001 AND warehouse_id = 5;

-- 事务B（改写WHERE顺序） UPDATE order_stock SET stock_qty = stock_qty - 1 WHERE order_id = 10086 AND sku_id = 2001 AND warehouse_id = 5;

MySQL优化器在大多数情况下会选择同一个唯一索引，这样两个事务的锁获取顺序一致，就不会交叉死锁。但要注意，优化器不保证100%选同一个索引，尤其是在数据分布变化时。

方案二：合并为单条SQL。

既然两条UPDATE操作的是同一行数据，为什么不合成一条？

UPDATE order_stock 
SET locked_qty = locked_qty + 1,      stock_qty = stock_qty - 1  WHERE order_id = 10086 AND sku_id = 2001 AND warehouse_id = 5;

一条SQL只涉及一次索引锁定，根本不存在交叉的可能。这是最干净的方案。和业务方确认后，这两个字段确实可以在同一个事务中一起更新——锁定库存和扣减库存本来就是同一个原子操作。

方案三：如果必须分两条SQL，加分布式锁或SELECT ... FOR UPDATE。

在UPDATE之前先加锁：

BEGIN;
SELECT id FROM order_stock  WHERE order_id = 10086 AND sku_id = 2001 AND warehouse_id = 5  FOR UPDATE;

UPDATE order_stock SET locked_qty = locked_qty + 1 WHERE id = ?; UPDATE order_stock SET stock_qty = stock_qty - 1 WHERE id = ?; COMMIT;

`FOR UPDATE`会直接在主键上加排他锁，后续的UPDATE都走主键，锁的获取顺序完全一致。

最终方案和落地

跟业务方确认后，采用了方案二——合并SQL。改完后观察了48小时，监控系统再没报过死锁告警。性能上也有所提升，因为从两次索引查找+两次行锁变成了一次。

顺便把`idx_sku_warehouse`这个索引也评估了一下。这个索引是之前为"按SKU查库存"的查询加的，但经过慢查询分析，那个查询命中率极低（每天不到10次），而且走了全表扫描也只需要几十毫秒。直接砍掉这个索引，减少维护开销，也从根本上消除了多索引交叉死锁的可能。

-- 执行前确认没人在用
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'ecom'; -- 确认后删除 ALTER TABLE order_stock DROP INDEX idx_sku_warehouse;

写在最后

排查死锁这件事，很多人的第一反应是"加大innodb_lock_wait_timeout"或者"减少事务粒度"。这些手段偶尔有用，但大多数时候只是在掩盖问题。死锁的本质是并发事务对同一组资源的访问顺序不一致。找到那个不一致的地方，问题就解决了一大半。

建议在生产环境开启死锁日志持久化：

SET GLOBAL innodb_print_all_deadlocks = ON;

配合`pt-deadlock-logger`做持续监控，下次再出问题就不用半夜爬起来逐条翻了。

【放心，我们兜底】

不管你是自己尝试修复，还是需要专业人员上门，易云城IT服务都给你托底。修不好不收费，修好了质保期内随时找我。

📞 服务热线：13708730161 💬 微信：eyc1689 📧 邮箱：service@eycit.com 🌐 https://www.eycit.com

您身边的IT专家。