Mysql next-key lock加锁规则

作草分茶About 4 min

Mysql next-key lock加锁规则

最近在读丁奇老师的《Mysql 实战 45 讲》,收获颇多,在此把一些以前不懂的知识点记录下来,方便以后复习。

《Mysql 实战 45 讲》链接

地址在极客时间,链接奉上。MySQL实战45讲_MySQL_数据库-极客时间open in new window

加锁规则总结

不同版本结论不一样

丁奇老师的总结(丁奇老师的版本是 5.x 系列 <=5.7.24,8.0 系列 <=8.0.13):包含了两个“原则”、两个“优化”和一个“bug”。

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

因为间隙锁只有在可重复读下才有效,所以以下分析都是在可重复度级别下,且笔者的 Mysql 版本是 8.0.27

加锁规则包括 两个原则两个优化

  1. 原则 1:加锁的基本单位是 next-key lock, 是前开后闭区间。

  2. 原则 2:查找过程中访问到的对象才会加锁

  3. 优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。

  4. 优化 2:索引上的等值查询,向右遍历时不满足条件的时候,next-key lock 退化为间隙锁、也可能缩小间隙锁的范围

场景实战

Tips

以下案例来源于《Mysql 实战 45 讲》第 21 讲《为什么我只改一行的语句,锁这么多》open in new window。 丁奇老师的版本是 5.x 系列 <=5.7.24,8.0 系列 <=8.0.13,我的版本是 8.0.27,我的版本以下场景测试的结果跟丁奇老师的有些出入,即丁奇老师总结的这个 bug 被修复了。

第一步初始化表结构和数据。

下面开始实战场景分析。

等值查询间隙锁

session Asession Bsession C
begin ;
update t set d = d+1 where id = 7;
insert into t value (8,8,8);(阻塞)
update t set d = d+1 where id = 10;(成功)

场景说明:

  1. session A 开启一个事务,更新 id = 7 的数据(该数据不存在)。
  2. session B 插入一条 id = 8 的数据,此时阻塞。
  3. session C 更新 id = 10 的数据,更新成功。

分析:

  1. session A 更新一条不存在的数据 id = 7,根据原则一和原则二,从左到右查找数据,找到间隙锁id(5,10),再加上行锁 10 组成 id(5,10] 前开后闭的 next-key lock
  2. 根据优化2,因为 session A 的更新条件是等值查询,next-key lock 最后一个值是 10,不满足 id = 7,所以 session A 加锁退化为间隙锁,即 session A 最终的加锁范围是 id(5,10)。
  3. 由此分析 session B 插入数据会阻塞,而 session C 成功。

非唯一索引等值锁

session Asession Bsession Csession D
begin ;
select id from t where c=5 lock in share mode ;
update t set d = d+1 where id = 5;(成功)
insert into t value (7,7,7);(阻塞)
update t set d = d+1 where c = 5;(阻塞)

场景说明:

  1. session A 开启一个事务,且查询覆盖索引 c=5加上了读锁。
  2. session B 成功更新 id =5 的数据。
  3. session C 插入 id = 7 阻塞。
  4. sessoin D 更新 c=5 的数据。

分析:

  1. session A 从左往右查询,第一个间隙锁是 c(0,5),加上行锁 5 组成 c(0,5) 的 next-key lock。
  2. 因为 c 是普通索引,所以此时还要继续往右找,找到下一个间隙 (5,10),因为加锁的基本但是是 next-key lock,所以 id =10 的行锁也会被加上,即 c(5,10] 也会被加上锁。根据优化2,最后一条数据 c=10 不满足 c=5 的条件,所以此时会退化成 c(5,10) 的间隙锁。
  3. 根据原则2,只有访问到的对象才会被加锁,因为查询结果是id,走的是覆盖索引不需要回表,主键 id=5 并不会被加锁,所以 session B 才能更新成功。
  4. session C 需要插入 id=7 的数据,此时会被间隙锁 c(5,10) 锁住。
  5. session D 更新 c=5 的数据,会被 next-key lock c(0,5] 锁住。

主键索引范围锁

这个案例我测试话来跟丁奇老师的结果不一样,这是跟 mysql 的版本有关系的。

非唯一索引范围锁