MySQL锁超时与间隙锁死锁

1. 前提条件

mysql数据分段区间:(-∞,+supremum]

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

id索引会划分为以下区间
(-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]

需要用到的表
information_schema.INNODB_LOCK_WAITS;正在等待锁的信息
information_schema.INNODB_LOCKS;锁类型相关信息
要用到的表(可以自行建表)
tb_storage_data_info
当前表索引
show index from tb_storage_data_info;

我们只会用到business_id与id俩个索引
准备已存在的数据
select * from tb_storage_data_info where business_id=‘4c09ca95327d4f6393adb2bc9dc6c343’;

准备不存在的数据
select * from tb_storage_data_info where business_id=‘4c09ca95327d4f6393adb2bc9dc6c343a’;

2. 加锁规则

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

3. 验证

3.1 存在的数据(使用二级索引)

Session A 执行

begin;
select * from tb_storage_data_info where business_id='4c09ca95327d4f6393adb2bc9dc6c343' for update ;

Session B 执行 这个时候会进入等待

begin;
select * from tb_storage_data_info where business_id='4c09ca95327d4f6393adb2bc9dc6c343' for update ;

此时我们通过Session A执行SQL
select * from information_schema.INNODB_LOCK_WAITS;可以看到在等待的锁信息

select * from information_schema.INNODB_LOCKS;

我们可以看到加锁的索引以及对应的索引数据锁的类型是record行锁

3.2 不存在的数据(使用二级索引)

Session A执行

begin;
select * from tb_storage_data_info where business_id='4c09ca95327d4f6393adb2bc9dc6c343a' for update ;

Session B执行 这个时候会进入等待

begin;
insert into tb_storage_data_info(id,business_id,create_time,is_deleted) values (1710853801526865922,'4c09ca95327d4f6393adb2bc9dc6c343a',now(),0);

此时我们通过Session A执行SQL
select * from information_schema.INNODB_LOCK_WAITS;可以看到在等待的锁信息

select * from information_schema.INNODB_LOCKS;

我们可以发现锁的类型已经变成了next-key-lock 依旧是行锁+间隙锁 锁的索引依旧是business_id

死锁问题

Session A执行

begin;
select * from tb_storage_data_info where business_id='4c09ca95327d4f6393adb2bc9dc6c343a' for update ;

Session B也同样执行 因为记录不存在并且加锁区间为一个区间所以不会进入等待

begin;
select * from tb_storage_data_info where business_id='4c09ca95327d4f6393adb2bc9dc6c343a' for update ;

但俩个Session分别执行insert(update语句也是如此,当前间隙的锁分别被俩个Session占有，导致不能俩个同时操作一个间隙)

insert into tb_storage_data_info(id,business_id,create_time,is_deleted) values (1710853801526865922,'4c09ca95327d4f6393adb2bc9dc6c343a',now(),0);

这个时候就会出现间隙锁的死锁

3.3 存在的数据(不使用二级索引)

Session B执行

begin;
select * from tb_storage_data_info where business_id='4c09ca95327d4f6393adb2bc9dc6c343' for update ;

Session A执行 这是一条修改表结构的SQL 需要获取table meta lock写锁 但是读锁已被上个Session 占用只能进入等待

alter table tb_storage_data_info drop index idx_business_id;

表级锁信息不在innodb_locks中，我们通过
select * from information_schema.PROCESSLIST;查看可以看到这个语句在等待表锁

我们将Session B回滚
rollback ;
索引就删除掉了
show index from tb_storage_data_info;

Session A 执行

begin;
select * from tb_storage_data_info where business_id='4c09ca95327d4f6393adb2bc9dc6c343' for update ;

Session B 执行 这个时候会进入等待

begin;
select * from tb_storage_data_info where business_id='4c09ca95327d4f6393adb2bc9dc6c343' for update ;

此时我们通过Session A执行SQL
select * from information_schema.INNODB_LOCK_WAITS;可以看到在等待的锁信息

select * from information_schema.INNODB_LOCKS;

可以看到锁的类型为行锁，索引为主键（这里就是就算没有索引，走的其实就是主键索引）

3.4 不存在的数据(不使用二级索引)

Session A执行

begin;
select * from tb_storage_data_info where business_id='4c09ca95327d4f6393adb2bc9dc6c343a' for update ;

Session B执行 这个时候会进入等待

begin;
insert into tb_storage_data_info(id,business_id,create_time,is_deleted) values (1710853801526865922,'4c09ca95327d4f6393adb2bc9dc6c343a',now(),0);

此时我们通过Session A执行SQL
select * from information_schema.INNODB_LOCK_WAITS;可以看到在等待的锁信息

select * from information_schema.INNODB_LOCKS;

可以看到加锁的范围就是next-key-lock 是行锁+间隙锁 索引依旧是主键
这个期间如果session A一直不释放锁那么session B就会出现
[2023-12-25 09:46:48] [40001][1205] Lock wait timeout exceeded; try restarting transaction