先说结论：Mysql 在RR隔离级别，指定索引下无数据的情况下，对supermum加next-key锁之后，并无法正常阻止其他线程继续对supermum加next-key锁。
1.场景复现
构造数据
创建一张测试表，同时对group_name字段添加索引。
create table t_test
(
id char(32) not null comment '主键' primary key,
group_name varchar(60) not null comment '组名',
user_name varchar(60) null comment '组员姓名',
index idx_group (group_name)
);
模拟事务
事务1执行SQL:
begin;
SELECT * FROM t_test WHERE group_name = 'xxxx' AND user_name = 'zhangsan' FOR UPDATE;
事务2执行SQL:
begin;
SELECT * FROM t_test WHERE group_name = 'xxxx' AND user_name = 'zhangsan' FOR UPDATE;
查看当前锁记录表【performance_schema.data_locks】：
可以看到，事务1【9358831】和事务2【9358842】均同是持有了supermum pseudo-record的next-key锁，IX意向排他锁形如虚设。
此处为mysql的一个bug，mysql官网bug反馈论坛上的有关探讨可见：bugs.mysql.com/bug.php?id=…
经查阅网上有一种说法是由于对应索引下无数据记录，所以mysql加在(0,supermum pseudo-record)区间的是一个Gap锁，而Gap锁相互之间不互斥，所以导致了两个事务同时持有了(0,supermum pseudo-record)区间的Gap锁，进而形成死锁。 这边具体实质原因还有待但探讨...
2.验证正常场景
构造数据
对如上【t_test】表插入一条数据
INSERT INTO t_test (id, group_name,user_name) VALUES (1,'one','lisi');
模拟事务
事务1执行SQL:
begin;
SELECT * FROM t_test WHERE group_name = 'one' AND user_name = 'zhangsan' FOR UPDATE;
事务2执行SQL:
begin;
SELECT * FROM t_test WHERE group_name = 'one' AND user_name = 'zhangsan' FOR UPDATE;
查看当前锁记录表【performance_schema.data_locks】：
可以看到，事务1【9361479】在idx_group的索引结构下创建了两个next-key锁，锁住了group_name='one'条件下的所有数据。另外对(1,'one','lisi')的主键聚集索引加了行锁，以及整个表的意向排他锁IX。
事务2【9361488】由于也需要申请在idx_group索引结构下('one',1)的next-key锁，正常被事务1【9361479】阻塞，LOCK_STATUS处于WAITING。
3.影响范围
此bug适用于Mysql事务隔离级别在REPEATABLE READ(RR)下, 已知以下几种情况：
-
并发执行先delete，后insert的事务。
-
并发执行先select * from for update，后insert的事务
以上情况均是由于在delete/for update 语句在对指定索引条件下加next-key范围锁的情况下，如果指定索引条件下无数据，则事务只持有持有supremum pseudo-record的next-key锁，而此时持有的next-key锁无法正常和其他事务的IX意向排他锁互斥，导致后续事务出现死锁等异常！
4.解决方法
这个编号为【25847】的bug在mysql的官方论坛里从2007年就有人提出，到现在一直没有修复，应该是修复无望了。
目前的解决方法都是针对几个出发bug的条件进行规避，主要有三个条件【并发事务】，【索引条件下无数据】，【可重复读(RR)隔离级别】
1.避免并发事务
避免并发事务可以将事务隔离级别提高到最高等级Serializable，对于比较小，简单的事务场景没有太大影响，但是对于高频，大事务场景就对整个数据库性能有较大的影响。另外可以尝试在事务开启之前加锁。锁的颗粒度可以自由控制(sync，分布式锁)，保证指定的事务之间保持串行，对比修改事务隔离级别的方法影响更加可控。
2.避免在索引条件下无数据的情况，执行delete/for update等相关会触发加Gap锁的语句。
可以避免delete数据库不存在的记录，在delete之前select一下，如果存在记录则不执行。
使用for update的情况下，保证select语句的返回结果必定有记录，这样数据库至少会持有该记录的Next-key锁，此时next-key锁能够正常发挥作用与其他事务的IX意向排他锁互斥。
3.降低事务隔离级别，避免Mysql加范围锁
如果是为了避免由此bug导致的死锁问题，将事务隔离级别降低到Read committed，这样Mysql不再会对数据库加Gap锁，但是会出现幻读的问题。
