你不得不知道的Mysql间隙锁

共计 1835 个字符,预计需要花费 5 分钟才能阅读完成。

前言

巴纳姆效应:人很容易相信一个笼统的一般性的人格描述,并认为他特别适合自己并准确的揭示了自己的人格特点,即使内容空洞。

注:这真的是一篇很重要的文章,这不是心理暗示

思考问题

  1. InnoDB 中的加锁操作如果没有使用到索引,会怎样?
  2. 在RR事务隔离级别下,在事务A里读取某个范围内的记录,这时事务B在该范围里插入了新的记录,事务A再次读取该范围的记录就会产生幻行。就算我们给范围内的所有记录都加上行锁,也无法阻止insert语句的执行,因为insert的是新的行,那怎么办?


几种锁的简单说明

看了很多文章,发现大部分都把间隙锁和临键锁完全混为一谈,所以有必要解释一下它们的区别

记录锁(Record Lock)

为某行记录加锁,封锁该行的索引记录

// name 列是唯一索引。
select * from table where name = dustin for update;
// id 为主键列
update table set age = 18 where id = 10

注:如果name 不是唯一索引,那么这个加锁语句就会变成 临键锁(next-key lock)

记录锁的两个必要条件

  1. 记录锁针对的列必须是 唯一索引(主键也是唯一索引)
  2. 如果是查询,那么查询语句必须为 精确匹配(=),不能是>,<,like这种的,不然也会退化成临键锁

间隙锁(Gap Lock)

锁定一段范围内的索引记录,它是基于 Next-Key Locking 算法的

select * from table where id between 10 and 20 for update;

上面这条sql会锁住 (1,10) 区间内的所有记录行,都是开区间

  1. 间隙锁是基于 非唯一索引
  2. 间隙锁锁住的是一个区间,而不仅仅是区间内的每一条记录!!!
  3. 非唯一索引不管是锁住单条还是多条记录,都会产生间隙锁
  4. 唯一索引产生间隙锁的条件:锁住多条记录 或者 查询一条不存在的记录

临键锁(Next-Key Lock)

Next-Key 算是一种特殊的间隙锁,我们常说的间隙锁其实就是这个临键锁。通过临键锁可以解决上面的思考问题2(幻读)。

临键锁也是基于非唯一索引的,锁住的是一段 左开右闭 区间的数据

间隙锁和临键锁的事务隔离级别是RR,如果是RC就会失效

间隙锁的锁定区域

根据检索条件向左寻找最靠近检索条件的记录值A,作为左区间,向右寻找最靠近检索条件的记录值B作为右区间

场景解读

准备数据
你不得不知道的Mysql间隙锁

间隙锁的测试表

说明:id为主键,number为普通索引,name无索引,数据库隔离级别为RR

插入测试数据,如下
你不得不知道的Mysql间隙锁

该表number列潜在的间隙锁(临键锁)有:

(-∞, 1],
(1, 6],
(6, 12],
(12, 18],
(18, 24],
(24, +∞]

一、普通索引的间隙锁问题

  • 1.等值查询

你不得不知道的Mysql间隙锁

你不得不知道的Mysql间隙锁

分析:事务1执行了 number=6 的等值查询,按照上面提到的间隙锁锁定区域的算法,加锁的范围为 (1, 6], (6,12],故事务2插入 number=7 的记录,被堵塞出现锁超时现象,如果插入 number=2 一样也会锁超时

  • 2.范围查询

你不得不知道的Mysql间隙锁

你不得不知道的Mysql间隙锁

分析:事务1执行的范围查找,因为是非唯一索引,索引加锁的范围是 (6,18],故 事务2 插入 number=7 和 事务3 插入 number=14 会被堵塞,同样如果执行 update 语句,来更新 number=12 的记录也会被堵塞

  • 3.死锁

你不得不知道的Mysql间隙锁

你不得不知道的Mysql间隙锁

分析:事务1执行 number=11 的查询后,加锁范围是 (6,12],事务2执行 number=10 的查询后,同样获得 (6,12] 的间隙锁,然后事务1执行插入 number=9 的记录,等待事务2释放锁,堵塞住了,事务2 执行插入 number=8,等待事务1释放锁,也堵塞住了

  • 4.等值limit查询

你不得不知道的Mysql间隙锁

你不得不知道的Mysql间隙锁

你不得不知道的Mysql间隙锁

分析:事务1 执行查询 number<19 的记录,限制2条,查到 number=1 和 number=6 这两条记录就停止了,所以加锁的范围是 (-∞, 6],故事务2插入 number=5,被堵塞,事务3插入 number=7 成功(为了后续测试把事务3回滚了)

二、唯一索引的间隙锁问题

  • 1.查询一个不存在的记录

你不得不知道的Mysql间隙锁

你不得不知道的Mysql间隙锁

分析:事务1根据主键唯一索引查找了一个不存在的记录,产生了间隙锁,加锁范围 (1,6],故事务2执行插入id=3的记录被堵塞,锁超时

  • 2.范围查询

你不得不知道的Mysql间隙锁

你不得不知道的Mysql间隙锁

分析:事务1执行范围查询,加锁范围为 (12,18],因为 查询条件里 包含 id=12,所以根据唯一索引的等值查询,id=12 升级为行锁,故加锁范围为 [12,18],事务2执行插入 id=14 被堵塞

  • 3.等值查询

你不得不知道的Mysql间隙锁

你不得不知道的Mysql间隙锁

分析:事务1执行唯一索引的等值查询,故不产生间隙锁,而是为 id=12 的行加上行锁,事务2执行插入 id=13 插入成功

正文完
 1
Dustin
版权声明:本站原创文章,由 Dustin 2021-05-27发表,共计1835字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。