MySQL 死锁

MySQL 死锁深度解析：辅助索引与主键操作引发的循环等待案例

死锁是数据库运维中常见的技术难题，其本质是多个事务对资源的争夺导致的循环等待。本文通过一个真实案例，详细剖析 MySQL 中因辅助索引更新与主键删除操作引发的死锁场景，深入解读锁机制原理，并提供可落地的解决方案，帮助开发者和 DBA 掌握死锁分析与预防的核心方法。

一、案例背景与环境说明

业务场景

某业务系统中存在两个并发操作流程：

1. 事务 A：先通过SELECT ... FOR UPDATE锁定特定记录，进行业务处理后删除该记录
2. 事务 B：通过辅助索引更新同一条记录的字段值

在高并发场景下，这两个操作频繁触发死锁，导致事务回滚，影响业务稳定性。

环境配置

• 数据库版本：MySQL 8.0.30
• 事务隔离级别：REPEATABLE-READ（默认级别）
• 表结构：

  CREATE TABLE dl (
    id INT AUTO_INCREMENT PRIMARY KEY,
    c1 INT NOT NULL,
    c2 INT NOT NULL,
    KEY idx_c1 (c1)  -- 辅助索引
  );
  -- 初始化数据
  INSERT INTO dl(c1,c2) VALUES (3,1),(3,2),(3,2),(3,3),(4,4),(5,5);
  

   
  
  表中 id=6 的记录对应 c1=5，是本次死锁的核心争夺资源。

二、死锁发生过程还原

事务执行时序

通过两个会话模拟并发操作，关键步骤如下：

时间点	会话 1（事务 A）	会话 2（事务 B）
T1	BEGIN 开启事务	-
T2	SELECT * FROM dl WHERE id=6 FOR UPDATE;（持有 id=6 的 X 锁）	-
T3	-	UPDATE dl SET c2=10 WHERE c1=5;（获取 idx_c1 的 X 锁，等待 id=6 的 X 锁）
T4	DELETE FROM dl WHERE id=6;（持有 id=6 的 X 锁，等待 idx_c1 的 X 锁）	-
T5	死锁触发，事务回滚	获得锁，执行成功

死锁日志解析

MySQL 的死锁日志清晰记录了锁的持有与等待关系：

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 1416764, ACTIVE 15 sec starting index read
update dl set c2=10 where c1=5
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 49 page no 5 n bits 80 index idx_c1 of table `test`.`dl` 
trx id 1416764 lock_mode X  -- 持有辅助索引idx_c1的X锁

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49 page no 4 n bits 80 index PRIMARY of table `test`.`dl` 
trx id 1416764 lock_mode X locks rec but not gap waiting  -- 等待主键id=6的X锁

*** (2) TRANSACTION:
TRANSACTION 1416759, ACTIVE 23 sec updating or deleting
delete from dl where id=6
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 49 page no 4 n bits 80 index PRIMARY of table `test`.`dl` 
trx id 1416759 lock_mode X locks rec but not gap  -- 持有主键id=6的X锁

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49 page no 5 n bits 80 index idx_c1 of table `test`.`dl` 
trx id 1416759 lock_mode X locks rec but not gap waiting  -- 等待辅助索引idx_c1的X锁

*** WE ROLL BACK TRANSACTION (2)

日志关键信息提取：

• 事务 1（会话 2）持有辅助索引 idx_c1 的 X 锁，等待主键 id=6 的 X 锁
• 事务 2（会话 1）持有主键 id=6 的 X 锁，等待辅助索引 idx_c1 的 X 锁
• 形成循环等待，MySQL 选择回滚代价较小的事务 2

三、死锁成因深度剖析

加锁机制原理

MySQL 在 REPEATABLE-READ 隔离级别下的加锁遵循以下原则：

1. 基本单位：next-key lock（行锁 + 间隙锁），但存在优化场景
2. 唯一索引等值查询：next-key lock 退化为行锁（仅锁定记录本身）
3. 辅助索引操作：先锁定辅助索引记录，再锁定对应的主键记录

本案例加锁顺序分析

• 会话 1（事务 A）：
  1. SELECT ... FOR UPDATE WHERE id=6：直接锁定主键 id=6 的记录（行锁，X 模式）
  2. DELETE WHERE id=6：需同时锁定该记录对应的辅助索引 idx_c1 条目（X 模式）
• 会话 2（事务 B）：
  1. UPDATE WHERE c1=5：先通过辅助索引 idx_c1 找到 c1=5 的记录，锁定该索引条目（X 模式）
  2. 再尝试锁定对应的主键 id=6 的记录（X 模式）

死锁根源：两个事务加锁顺序完全相反，形成 "主键锁→辅助索引锁" 与 "辅助索引锁→主键锁" 的循环等待链。

四、解决方案与优化实践

核心优化思路

解决死锁的关键是统一事务的加锁顺序，避免不同事务以相反顺序获取锁资源。

具体实施方案

将会话 2 的更新操作从基于辅助索引改为基于主键：

1. 先通过 c1=5 查询出对应的主键 id：
    

   SELECT id FROM dl WHERE c1=5;  -- 假设返回id=6
   

    
2. 基于主键进行更新：
    

   UPDATE dl SET c2=10 WHERE id=6;
   

    

优化效果验证

• 会话 2 的加锁顺序变为：直接锁定主键 id=6（无需先锁定辅助索引）
• 两个事务的加锁顺序统一为 "主键锁→辅助索引锁"（会话 1 的删除操作仍需锁定辅助索引，但此时会话 2 已无辅助索引锁持有）
• 循环等待条件被打破，死锁不再发生

五、MySQL 死锁分析方法论

死锁排查步骤

1. 查看死锁日志：通过SHOW ENGINE INNODB STATUS获取最新死锁信息
2. 梳理事务操作：还原各事务的 SQL 执行顺序
3. 分析加锁顺序：根据索引类型和查询条件，判断每个操作的加锁对象与顺序
4. 定位循环等待：找出不同事务间相反的加锁顺序

预防死锁的通用原则

1. 统一加锁顺序：所有事务对资源的锁定遵循相同顺序（如先主键后辅助索引）
2. 缩小锁范围：尽量使用主键查询，避免大范围扫描导致的锁冲突
3. 控制事务时长：减少事务持有锁的时间，降低并发冲突概率
4. 合理设计索引：避免不必要的辅助索引，减少锁竞争点
5. 使用较低隔离级别：如 READ-COMMITTED，可减少间隙锁的使用（需评估业务影响）

六、总结

本案例展示了 MySQL 中辅助索引与主键操作引发死锁的典型场景，其核心原因是事务间加锁顺序的不一致。通过统一加锁顺序（优先基于主键操作），可有效避免此类死锁。

在实际开发中，应充分理解 MySQL 的加锁机制，尤其是不同索引类型、查询条件对加锁范围的影响。面对死锁问题时，需结合日志还原加锁过程，从根本上打破循环等待条件，而非简单依赖重试机制。

掌握死锁分析的方法论，不仅能解决具体问题，更能帮助开发者设计出更健壮的并发事务逻辑，提升系统整体稳定性。