Home Tags Posts tagged with "next-key lock"

next-key lock

0 44

间隙锁和next-key lock(转自超级大佬)

0.521字数 1,767阅读 3,938
本篇作为学习笔记,文章内容来自“极客时间”专栏《MySQL实战45讲》,如有侵权,请告知,必即时删除。为了便于说明问题,建表和初始化语句如下:

 

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);

行锁只能锁住行,但是新插入记录这个动作,要更新的是记录之间的“间隙”。因此,为了解决幻读问题,InnoDB 只好引入新的锁,也就是间隙锁 (Gap Lock)。

顾名思义,间隙锁,锁的就是两个值之间的空隙。比如文章开头的表 t,初始化插入了 6 个记录,这就产生了 7 个间隙。

间隙锁1.png

这样,当你执行 select * from t where d=5 for update 的时候,就不止是给数据库中已有的 6 个记录加上了行锁,还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。

也就是说这时候,在一行行扫描的过程中,不仅将给行加上了行锁,还给行两边的空隙,也加上了间隙锁。

跟间隙锁存在冲突关系的,是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系

这句话不太好理解,我给你举个例子:

间隙锁2.png

这里 session B 并不会被堵住。因为表 t 里并没有 c=7 这个记录,因此 session A 加的是间隙锁 (5,10)。而 session B 也是在这个间隙加的间隙锁。它们有共同的目标,即:保护这个间隙,不允许插入值。但,它们之间是不冲突的。

间隙锁行锁合称 next-key lock每个 next-key lock 是前开后闭区间。也就是说,我们的表 t 初始化以后,如果用 select * from t for update 要把整个表所有记录锁起来,就形成了 7 个 next-key lock,分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

今天分析的问题都是在可重复读隔离级别下的,间隙锁是在可重复读隔离级别下才会生效的。所以,你如果把隔离级别设置为读提交的话,就没有间隙锁了。

加锁规则

加锁规则里面,包含了两个“原则”、两个“优化”和一个“bug”。

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

第一个例子是关于等值条件操作间隙:

间隙锁3.png

由于表 t 中没有 id=7 的记录,所以用我们上面提到的加锁规则判断一下的话:

  1. 根据原则 1,加锁单位是 next-key lock,session A 加锁范围就是 (5,10];
  2. 同时根据优化 2,这是一个等值查询 (id=7),而 id=10 不满足查询条件,next-key lock 退化成间隙锁,因此最终加锁的范围是 (5,10)。

所以,session B 要往这个间隙里面插入 id=8 的记录会被锁住,但是 session C 修改 id=10 这行是可以的。

案例二:非唯一索引等值锁

第二个例子是关于覆盖索引上的锁:

间隙锁4.png

这里 session A 要给索引 c 上 c=5 的这一行加上读锁。

  1. 根据原则 1,加锁单位是 next-key lock,因此会给 (0,5]加上 next-key lock。
  2. 要注意 c 是普通索引,因此仅访问 c=5 这一条记录是不能马上停下来的,需要向右遍历,查到 c=10 才放弃。根据原则 2,访问到的都要加锁,因此要给 (5,10]加 next-key lock。
  3. 但是同时这个符合优化 2:等值判断,向右遍历,最后一个值不满足 c=5 这个等值条件,因此退化成间隙锁 (5,10)。
  4. 根据原则 2 ,只有访问到的对象才会加锁,这个查询使用覆盖索引,并不需要访问主键索引,所以主键索引上没有加任何锁,这就是为什么 session B 的 update 语句可以执行完成。

但 session C 要插入一个 (7,7,7) 的记录,就会被 session A 的间隙锁 (5,10) 锁住。

lock in share mode 只锁覆盖索引但是如果是 for update不一样 执行 for update 时,系统会认为你接下来要更新数据,因此顺便给主键索引上满足条件的行加上行锁

这个例子说明,锁是加在索引上的同时,它给我们的指导是,如果你要用 lock in share mode 来给行加读锁避免数据被更新的话,就必须得绕过覆盖索引的优化,在查询字段中加入索引中不存在的字段。比如,将 session A 的查询语句改成 select d from t where c=5 lock in share mode。你可以自己验证一下效果。

案例三:主键索引范围锁

第三个例子是关于范围查询的。

间隙锁5.png

现在我们就用前面提到的加锁规则,来分析一下 session A 会加什么锁呢?

  1. 开始执行的时候,要找到第一个 id=10 的行,因此本该是 next-key lock(5,10]。 根据优化 1, 主键 id 上的等值条件,退化成行锁,只加了 id=10 这一行的行锁。
  2. 范围查找就往后继续找,找到 id=15 这一行停下来,因此需要加 next-key lock(10,15]。

所以,session A 这时候锁的范围就是主键索引上,行锁 id=10 和 next-key lock(10,15]。这样,session B 和 session C 的结果你就能理解了。

这里你需要注意一点,首次 session A 定位查找 id=10 的行的时候,是当做等值查询来判断的,而向右扫描到 id=15 的时候,用的是范围查询判断。