InnoDB非唯一索引导致死锁

死锁日志

收到警报，数据库死锁回滚交易失败

SHOW ENGINE INNODB STATUS; 获取最近发生的deadlock

配置：innodb_print_all_deadlocks并在error log查看

（图1）

翻译

• 行号：“1: len 8; hex 000000000000B75; asc”：B75（16进制） = 2933（10进制）。
• （1）WAITING FOR THIS LOCK TO BE GRANTED：事务（1）等待获取锁
• （2）HOLDS THIS LOCK(S)：事务（2）持有该锁

过程

• TRANSACTION（1）通过update语句1获取2934行记录锁，等待2933行记录锁释放；
• TRANSACTION（2）持有2933行记录锁，等待2934行记录锁释放。
• MYSQL发现死锁：WE ROLL BACK TRANSACTION（1）。

---

分析

表T结构：

Table	Non_unique	Key_name	Column_name
T	0	PRIMARY	id
T	1	m	m_no

简化语句：

图1update语句1（id = 2933）：update T set flag = 0 where m_no = 123 and f = 1;
图1update语句2（id = 2934）：update T set flag = 0 where m_no = 123 and f = 2;

select * from T where m_no = 123;

id	m_no	f	flag
2993	123	1	0
2994	123	2	0

explain update T set flag = 0 where m_no = 123 and f = 1;

possible_keys（计划用到的索引）	rows（计划查询的行数）
m_no_index	2

即使只查询f=1的记录，仍会查询2行

由于MySql是在索引上行锁，两个事务同时用一个key–m_no_index索引，两个事务都需要同时对m_no=123的两条记录上行锁，当两个记录上锁顺序不一样（事务1锁2933行，事务2锁2934行）就有几率发生死锁。

---

解决方案

• 固定上锁顺序

  • 先select所有的行，然后按照主键id排序，每个事务都按顺序上锁。
    • 每个事务都先上锁2933行，如果没抢到2933行就阻塞等待，不会去抢2934行
    • 优化点：仅select主键可以在当前索引树直接拿到主键id，减少一次回表
  • 缺点：因为每个事务都增加了查询和排序，增加了性能损耗，

• 重试机制

  • 死锁发生需要一定的巧合，在非唯一索引导致的死锁问题重试在大多数时候不会有问题
  • 缺点：个别事务会发生失败，影响用户体验

• 避免长时间持有锁，减少死锁概率

  • 避免长事务
  • 优化业务逻辑，在事务尽量接近结束再上锁，而不是事务刚开始的时候
  • 尽早commit

具体需要根据业务量和死锁发生的概率权衡用哪种方案

---

InnoDB如何发现死锁

配置：innodb_deadlock_detect（默认开）

事务等待图wait-for-graph（有向图）

一旦有向图形成了环，表示造成死锁，InnoDB报错死锁并回滚相应事务

---

References

How to Minimize and Handle Deadlocks：https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks-handling.html