mysql间隙锁与行锁分析与处理

基本概念

死锁产生的四个必要条件

1. 互斥条件。
2. 请求并持有。
3. 不可剥夺条件。
4. 形成环路等待。

要了解的锁类型

1. 记录锁：record lock，即行锁，锁住一条记录
2. 间隙锁：gap lock，即锁定一个区间，左开右开（需要注意的是间隙锁并不会影响查询和更新操>>作，只会影响插入操作）
3. 临键锁：next-key lock 记录锁+间隙锁锁定的区间，左开右闭

间隙锁

问题现象：下述代码会产生死锁

// 删除数据
relationMapper.delete(new QueryWrapper<RelationModel>()
        .lambda()
        .eq(RelationModel::getRelationId, id)
);

// 新增数据
relationMapper.saveBatch(relationList);

单独一个事务是不会有死锁问题的，但是当QPS上来，多个事务并发执行，就有可能死锁。

问题模拟

对上述死锁问题进行模拟，首先构建如下表t
id：唯一索引
a：普通索引
b：无索引

CREATE TABLE `t` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `a` int COMMENT 'a',
  `b` int COMMENT 'b',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_a` (`a`) USING BTREE
) COMMENT='测试表';

insert into t (id, a, b) values (1, 1, 1);
insert into t (id, a, b) values (3, 3, 3);
insert into t (id, a, b) values (6, 6, 6);
insert into t (id, a, b) values (12, 12, 12);
insert into t (id, a, b) values (24, 24, 24);

形成如下数据

id	a	c
1	1	1
3	3	3
6	6	6
12	12	12
24	24	24

执行如下sql会出现间隙锁死锁

事务 1	事务 2
begin;	begin;
delete from t where a = 20;
	delete from t where a = 5;
insert into t (a,b) values (4,4); 阻塞
	insert into t (a,b) values (19,19); Deadlock found when trying to get lock; try restarting transaction

• 解决方法
  • 降低事务隔离级别到 Read Committed，该隔离级别下间隙锁降级为行锁（不考虑）
  • 避免这种场景，可以先查询出 id 再根据 id 做删除或修改，这样间隙锁会退化成行锁

行锁

执行如下sql会出现行锁死锁

事务 1	事务 2
begin;	begin;
update t set b = -1 where id = 1;
	update t set b = -1 where id = 3;
update t set b = -1 where id = 3;
	update t set b = -1 where id = 1; Deadlock found when trying to get lock; try restarting transaction

• 解决方法
  • 避免循环更新，优化为一条 where 锁定要更新的记录批量更新
  • 尝试取消事务（能接受的话），即每一条更新为一个独立的事务
  • 不取消事务的话，尝试每个事务更新数据的顺序一致（统一升序或降序）

间隙锁加锁过程

介绍

1. 间隙锁的定义：

• 对于键值在条件范围内但并不存在的记录，即“间隙（GAP）”，InnoDB 会对这个“间隙”加锁。

1. 间隙锁的作用：
   • 防止幻读。

唯一索引(字段 id)

等值查询(equal)

记录存在

• 结论：当更新的记录是存在的时候，会加行锁。
  

事务 1	事务 2
begin;	begin;
delete from t where id = 6; 对 id=6 这条记录加行锁
	INSERT INTO t (id, a, b) VALUES ('4', '4', '4'); 正常
	update t set b = -1 where id = 6; 阻塞(行锁)

1. 加锁的基本单位是 next-key lock，因此会话 1 的加锁范围是(3, 6];
2. 但是由于是用唯一索引进行等值查询，且查询的记录存在，所以 next-key lock 退化成记录锁，因此最终加锁的范围是 id = 6 这一行。

记录不存在

• 结论：当更新的记录是不存在的，会加间隙锁。
• 

事务 1	事务 2
begin;	begin;
delete from t where id = 5; 对 id 在(3, 6)之间的记录加间隙锁
	update t set b = -1 where id = 3; 正常(无行锁)
	update t set b = -1 where id = 6; 正常(无行锁)
	INSERT INTO t (id, a, b) VALUES ('4', '4', '4'); 阻塞

1. 加锁的基本单位是 next-key lock，因此主键索引 id 的加锁范围是(3, 6];
2. 但是由于查询到 id = 5 记录不存在，next-key lock 退化成间隙锁，因此最终加锁的范围是 (3, 6)。

范围查询

• 结论：范围查询会寻找范围内的间隙，若是查询范围横跨多个间隙，则锁住多个间隙，并对范围内的记录加行锁。
• 

事务 1	事务 2	事务 3
begin;	begin;	begin;
delete from t where id >= 3 and id < 10; 对 id 在[3, 12)之间的记录加间隙锁
	update t set b = -1 where id = 12; 正常(无行锁)
	update t set b = -1 where id = 3; 阻塞(行锁)	INSERT INTO t (id, a, b) VALUES ('11', '11', '11'); 阻塞(间隙锁)

1. 最开始要找的第一行是 id = 3，因此 next-key lock(1, 3]，但是由于 id 是唯一索引，且该记录是存在的，因此会退化成记录锁，也就是只会对 id = 3 这一行加锁；
2. 由于是范围查找，就会继续往后找存在的记录，加 next-key lock(3, 6]
3. 最终就是会找到 id = 12 这一行停下来，然后加 next-key lock (6, 12]，但由于 id = 12 不满足 id < 10 且是唯一索引，所以会退化成间隙锁，加锁范围变为 (6, 12)。
4. 最终锁住[3, 12)

普通索引（字段 a）

等值查询(equal)

记录存在

• 结论：更新的记录不管存不存在都会加间隙锁，如果记录存在还会对记录加行锁，组成 next-key-lock
• 

事务 1	事务 2	事务 3
begin;	begin;	begin;
delete from t where a = 6;
	update t set b = 2 where a = 3; 正常(无行锁)
	update t set b = 2 where a = 12; 正常(无行锁)
	INSERT INTO t (id, a, b) VALUES ('5', '5', '5'); 阻塞	INSERT INTO t (id, a, b) VALUES ('7', '7', '7'); 阻塞

1. 先会对普通索引 a 加上 next-key lock，范围是(3, 6];
2. 然后因为是非唯一索引，且查询的记录是存在的，所以还会加上间隙锁，规则是向后遍历到第一个不符合条件的值才能停止，因此间隙锁的范围是(6, 12)。
3. 最终锁住的范围是(3, 12)

记录不存在

查询的值如果不存在，则和上述等值查询锁定的规则相同，都是只加间隙锁，此处不做赘述。

范围查询

• 结论：同唯一索引，可能会在多个间隙上加间隙锁，同时还会对范围最右边的记录加行锁
• 

事务 1	事务 2	事务 3	事务 4
begin;	begin;	begin;	begin;
delete from t where a >= 3 and a < 10; 对 a 在(1, 12)之间的记录加间隙锁
	update t set b = 2 where a = 1; 正常(无行锁)
	INSERT INTO t (id, a, b) VALUES ('2', '2', '2'); 阻塞	update t set b = 2 where a = 3; 阻塞(行锁)	update t set b = 2 where a = 12; 阻塞(行锁)

1. 最开始要找的第一行是 a = 3，因此 next-key lock(1, 3]，但是由于 a 不是唯一索引，并不会退化成行锁。
2. 由于是范围查找，就会继续往后找存在的记录，也就是会找到 a = 12 这一行停下来，然后加 next-key lock (8, 12]，因为是普通索引查询，所以并不会退化成间隙锁。
3. 最终加锁的范围是(1, 12]

无索引（字段 b）

• 锁表