InnoDB并发插入—间隙锁引发死锁的原因分析

1 间隙锁生成条件

• 事务隔离级别为RR（可重复读），间隙锁是为了防止幻读
• 执行delete/update/select for update操作，且where条件中索引未命中（命中加行锁，没有索引加表锁）

2 间隙锁的区间

间隙锁的区间取最靠近索引的左右值
例如：

create table user(
    id int primary key,
    no varchar(16)
    );
create index idx on user(no);

执行

delete from user where no = 3;

间隙锁锁定的区间为（1,6）

3 案例分析

下面用笔者在项目中遇到的真实案例进行分析
假设给用户批量导入课程，导入前需要先删除以前的，表结构如下

create table user(
    id int primary key,
    no varchar(16)
    );
create index idx on user(no);
#测试数据
insert into user(id, no) VALUE (1,'1');
insert into user(id, no) VALUE (2,'2');

create table course(
    id int primary key,
    user_id int,
    no varchar(16)
) comment '课程';
create index idx on course(user_id);
#测试数据
insert into course(id, user_id, no) VALUE (1,1,'1');
insert into course(id, user_id, no) VALUE (2,2,'2');

现给两个新用户（注意是新用户）导入数据，id分别为3,4。此时course表里没有这两个user_id，sql语句如下

##事务A
delete from course where user_id = 3;
insert into course(id, user_id, no) VALUE (3,3,'3');

##事务B
delete from course where user_id = 4;
insert into course(id, user_id, no) VALUE (4,4,'4');

事务A在执行delete时会加间隙锁，左区间的最近值是2，右区间为无穷大，最终锁定区间为（2，无穷大）。
事务B在执行delete时，在并发情况下，可能事务A还没有来得及执行insert语句，所以锁定区间也是（2，无穷大），此时事务B执行insert语句时就需要事务A释放间隙锁，而事务A执行insert也同样需要事务B释放间隙锁，这样就进入了死循环，造成死锁。
下面通过手动执行sql进行演示：

从上面的截图中可以看出事务A中的insert语句出现了死锁。

解决方案：

• 降低事务级别至读已提交（不推荐）
• 先判断有无数据，有数据删，无数据则不删，因为删除不存在的数据一定会加间隙锁
• 间隙锁非互斥锁，只针对写锁，只要两个线程锁定的区间有交叉就会出现死锁。可以根据条件查出所有主键，根据主键删除数据，这样只会加行锁而不是间隙锁（推荐）