MySQL的间隙锁

你好,我是god-jiang~

接上篇的MySQL行锁,我分享了RC隔离级别下的各种常见情况的加锁分析。这次分享的是MySQL间隙锁,分析RR隔离级别下各种常见情况的加锁分析。

1
2
3
4
5
6
7
8
9
10
11
create table `test`(
`id` int(11) NOT NULL,
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
PRIMARY KEY(`id`),
UNIQUE KEY unix_key('a'),
KEY ix_key(`b`)
)ENGINE=InnoDB;

insert into test values(0,0,0,0),(5,5,5,5),(10,10,10,10),(20,20,20,20);

创建一张test表,id是主键,a是唯一索引,b是普通索引,c是没有索引。

现在假设是在RC隔离级别下,我们来做一个实验看看:

事务A 事务B
set session transaction_isolation=’READ-COMMITTED’; set session transation_isolation=’READ-COMMITTED’;
begin;
select * from test where b=5 for update; 返回:(5,5,5,5)
begin;
insert into test values(6,5,5,5);
commit;
select * from test where b=5 for update; 返回:(5,5,5,5),(6,5,5,5)
commit;

由上面实验得出,在RC隔离级别下,事务A对b=5这一行记录加了X锁,但是事务B插入一条新的记录的b字段也是为5,然后在事务A中可以查出b=5有两条记录,这个就产生了“幻读”。

也就是说,幻读是指一个事务前后两次查询同一范围的时候,后一次查询看到了前一次查询没有看到的行记录。

为什么会产生幻读呢?

我们来分析一下此时RC隔离级别+b普通索引的加锁情况:

img

可以看到RC隔离级别下,b=5的记录加上了X锁,但是(0,5)还有(5,10)的间隙没有锁的情况,所以在这个间隙中可以插入新的数据。

现在我就可以回答出为什么会产生幻读了。产生幻读的原因是,行锁只能锁住行,但是新插入记录的这个动作,要更新的是记录之间的“间隙”。因此,为了解决幻读的问题,InnoDB在RR隔离级别引入了新的锁,它就是间隙锁(Gap Lock)。

上面讲了幻读怎么产生的,还有InnoDB引入间隙锁解决了幻读的情况,接下来分析一下RR隔离级别的加锁分析。

加锁分析(以下默认都是RR隔离级别并且都是当前读)

这里我挑选出RC隔离级别下三种常见情况分析SQL如何加锁:

  • RR隔离级别,where字段没有索引
  • RR隔离级别,where字段有普通索引
  • RR隔离级别,where字段有唯一索引

以下演示的表结构是文章开头的结构,数据是我个人臆想的。

RR隔离级别+无索引

img

上图所示,GAP锁加在c字段的(负无穷,0),(0,5),(5,10),(10,20),(20,正无穷)。但是在RR隔离级别下,我们都是默认是用next-key Lock(行锁+间隙锁),所以我们都是默认是左开右闭。

如上图所示,所有记录都加上了X锁,并且还加上了GAP锁。因此这张表在执行select * from test where c=5 for update期间还没有commit的话,除了不加锁的快照读,其他任何加锁的操作都会阻塞,如果这是在线上出现这种情况,将会是一件非常恐怖的事情。

总结:RR隔离级别下,无索引的条件字段的当前读不仅会把每条记录都加上X锁,还会加上GAP锁。再次强调,当前读或者插入/更新/删除操作需要加上索引。

RR隔离级别+普通索引

img

如上图所示,普通索引字段b=10给两条记录加了X锁,并且把聚集索引树的两条记录也加了X锁。GAP锁的是b的范围(5,10),(10,10),(10,正无穷)。所以next-key Lock锁的是(5,10],(10,10],(10,正无穷]。期间只要是b在next-key Lock的范围内就更新全部阻塞。

举例:insert into test values(6,6,6,6)就会被阻塞,原因就是RR隔离级别的间隙锁锁住了记录之间的“间隙”,所以会阻塞操作。

RR隔离级别+唯一索引

唯一索引的情况是最简单的,因为不管是RC隔离级别或者是RR隔离级别,唯一索引都只能查出一条记录,只会在对应的行记录加上X锁就没了。

为什么会这样?

因为GAP锁的目的是为了防止同一事务被连续两次当前读,然后两次读的情况不一致。如果能够保证字段是唯一的(唯一索引),其实就是最多只有一条记录满足条件,所以查询唯一索引的时候绝对不会出现GAP锁。

可以理解为RR+唯一索引和RC+唯一索引的加锁情况是一样的就好了。

总结

  • 这次分享了RC隔离级别下出现幻读的情况,然后分析了为什么会出现幻读。
  • InnoDB为了解决幻读,在RR下引入了GAP锁,和行锁组成next-key Lock
  • 分析了三种常见情况的加锁情况

特别注意:InnoDB引入的next-key Lock只解决了快照读的幻读,并没有解决当前读的幻读。

其实next-key Lock解决了快照读的幻读很大的一部分原因是因为undo log和MVCC,这里先留个悬念,关于MVCC的分享我会抽空写出来,并且分享出来。

参考资料

  • 《MySQL实战45讲》林晓斌
  • 《MySQL加锁处理分析》何登成
-------------本文结束感谢您的阅读-------------

本文标题:MySQL的间隙锁

文章作者:god-jiang

发布时间:2021年03月20日 - 17:43:18

最后更新:2021年03月20日 - 17:44:32

原始链接:https://god-jiang.github.io/2021/03/20/MySQL的间隙锁/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

创作不易,您的支持就是我坚持的动力,谢谢大家!
0%