MySQL 并发更新冗余索引字段导致的死锁

一 前言

死锁，其实是一个很有意思也很有挑战的技术问题，大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。

二 案例分析

2.1 业务场景

业务开发同学要做业务逻辑迁移，由A表迁移到B表,B表承担所有的类型的业务，他们的业务逻辑是:上游发task_id列表给下游，下游的业务根据一定的逻辑进行处理，在并发更新时遇到死锁。

注意: 因为B是新表，不一定有对应task_id的值。

2.2 环境说明

MySQL 5.6.24 事务隔离级别为RR

create table  x
(id int not null auto_increment,
 c int not null default  0,
 d int not null default 0,
 primary key(id),
 key idxcd(c,d));
insert into x(c,d) values(1,0),(3,0),(5,0),(7,0),(10,0),(12,0),(14,0),(16,0);

2.3 测试用例

2.4 死锁日志

2018-04-20 23:05:55 0x7f75cdfff700
*** (1) TRANSACTION:
TRANSACTION 2235, ACTIVE 161 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 8 row lock(s), undo log entries 1
MySQL thread id 1016626, OS thread handle 140143880890112, query id 4070003 127.0.0.1 root updating
update x set d=1 where c in (5,10)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 44 page no 4 n bits 80 
index idxcd of table `test`.`x` trx id 2235 
lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 2237, ACTIVE 36 sec updating or deleting, thread declared inside InnoDB 4997
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 1016629, OS thread handle 140143944005376, query id 4070021 127.0.0.1 root updating
update x set d=1 where c=7
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 44 page no 4 n bits 80 
index idxcd of table `test`.`x` trx id 2237 
lock_mode X locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 44 page no 4 n bits 80 
index idxcd of table `test`.`x` trx id 2237 
lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

2.5 分析死锁日志

首先我们要再次强调insert 插入操作的加锁逻辑。

第一阶段: 唯一性约束检查，先申请LOCK_INSERT_INTENTION

第二阶段: 如果没有唯一键冲突，新数据插入完成之后:LOCK_X + LOCK_REC_NOT_GAP

对于insert操作来说，若发生唯一约束冲突，需要对冲突的唯一索引申请加上S Next-key Lock。如果其他会话中包含已经插入记录的事务没有提交，则申请加锁出现等待，show engine innodb status中的事务列表中会提示 lock mode S waiting 。

从这里会发现，即使是RC事务隔离级别，也同样会存在Next-Key Lock锁，从而阻塞并发。然而，文档没有说明的是，对于检测到冲突的唯一索引，等待线程在获得S Lock之后，还需要对下一个记录进行加锁，在源码中由函数row_ins_scan_sec_index_for_duplicate进行判断.

其次 我们需要了解锁的兼容性矩阵。

从兼容性矩阵我们可以得到如下结论:

INSERT操作之间不会有冲突。GAP,Next-Key会阻塞插入意向锁INSERT_INTENTIONGAP与Record,Next-Key不会冲突Record与Record、Next-Key之间相互冲突。已有的Insert锁不阻止任何准备加的锁。

另外 RR事务隔离级别下,对于通过索引(唯一或者非唯一)更新或者删除不存在的记录，会申请加上gap锁。

当update 更新被索引字段时，相当于删除之后重新插入新的记录，需要重新组织索引节点。

了解上面的基础知识，我们开始对死锁日志进行分析:

T1时刻 开始事务。

T2时刻 sess2 更新c=6的值，但是c=6 不存在，申请加上(5,0)---(7,0)的X gap锁，日志提示:持有idxcd of table test.x trx id 2207 lock_mode X。

T3时刻 sess1 更新2行记录c=5,c=10,并且修改d=1,ses1 会申请(5,0),(10,0) 记录之间的Next-key锁,由于sess1需要插入新的记录(5,1)sess2 持有的X GAP 锁范围内,根据锁的兼容性矩阵，GAP,Next-Key会阻塞插入意向锁INSERT_INTENTION，故日志提示:index idxcd of table test.x trx id 2235 lock_mode X locks gap before rec insert intention waiting 。

T4时刻 sess2 更新c=7对应d=1,同样相当于insert (7,1) 在(5,0),(10,0)之间。根据兼容矩阵GAP,Next-Key会阻塞插入意向锁INSERT_INTENTION 故日志提示: index idxcd of table test.x trx id 2237 lock_mode X locks gap before rec insert intention waiting 。

至此,sess2 持有gap锁阻塞sess1 插入(5,1)，sess1持有Next-key 阻塞sess2 插入(7,1),循环等待出现死锁。

2.6 解决方法

其实解决方法比较简单 把组合索引 idxcd(c,d) 中的d去掉，改为idxc(c),避免GAP/Next-key 阻塞插入意向锁INSERT_INTENTION 即可，开发同学18:10分左右修改索引之后，业务不再出现死锁。

三 小结

本文的死锁算是蛮有意思的一个案例:并发多个update更新二级索引列，相当于索引节点重新组织,更新等于删除加插入，在死锁日志出现了插入意向锁。第一次猜想的时候还以为有 多个insert 操作，但是实际上只有更新动作。

最后想说关于解决死锁问题的思路:

具备扎实的锁相关的基础知识。

单单根据死锁日志其实比较难以判断具体的sql执行情况，需要和开发同学沟通好(当然开发也要提提供完整的业务逻辑)，理清业务执行sql的逻辑，然后去模拟测试。