数据库mysql

mysql 间隙锁和临键锁原理

2020-03-21  本文已影响0人  java后端领域

间隙锁产生的背景

备注: 本文使用的 MySQL 版本是: 8.0.13

隔离级别：可重复读(RR)

存储引擎：Innodb

以下面的表为例子进行说明

CREATE TABLE `tb` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`)) ENGINE=InnoDB;
insert into tb values(0,0,0),(10,10,10),(20,20,20),(30,30,30),(40,40,40),(50,50,50);

间隙锁的产生来自于 InnboDB 引擎在可重复读的级别基础上执行当前读时出现的幻读问题。下面来分析一下幻读的例子，假如没有间隙锁的话，那么会出现下面的现象：

sessionA sessionB
T1 begin;
select * from tb where b = 10 for update;
返回:(10,10,10)
T2 insert into tb value(2,2,10);
T3 select * from tb where b = 10 for update;
返回:(10,10,10),(2,2,10)
T4 commit;

如上表如示，是基于没有间隙锁的假设，sessionA 事务内执行两次相同的当前读返回的数据不一样，出现幻读的现象。因为(2,2,10)这条记录在原本的数据并不存在，行锁就锁不住，因此诞生间隙锁。

间隙锁加锁规则

间隙锁和行锁合称 next-key lock，每一个 next-keylock 是前开后闭区间，如: (0,10]

分析上面例子

select * from tb where b = 10 for update;

  1. 加锁的基于索引的
    因为 b 不是索引，索引走的是主键索引，加锁就是加在主键索引上

  2. 加锁范围

    遍历主键索引，发现 b=10 时，需要在前后记录之间加锁，所以在前一主键记录(0,0,0)和本记录之间加锁(0,10]，在后一条主键记录(20,20)和本记录之间加锁 (10,20]。然后继续向右遍历，判断 b=20，!=10，满足优化 2 规则，next-key lock 退化为间隙锁，变成(10,20)。同时 b=10 加了行锁，汇总锁范围: (0,20)，针对 id 主键

  3. 间隙锁与“往这个间隙插入操作”冲突

    因为上面主键 id 锁的范围是(0,20)，因此插入(2,2,10)中 主键 2 属于锁范围，因此阻塞

    注意: 线程之间的间隙锁是不冲突的

查询条件走二级索引例子

sessionA sessionB
T1 begin;
select id from tb where a = 20 for update;
T2 场景 1：insert into tb values(5,11,11);//阻塞
场景 2: insert into tb values(15,10,8);//阻塞
场景 3：insert into tb values(17,9,1);//成功
场景 4: insert into tb values(25,35,88);//成功
T3 commit;

首先看看sessionA的执行计划，发现用到覆盖索引

explain select id from tb where a = 20 for update;

  1. 因为条件走了 a 索引，查询字段 id 在索引 a中，用到的覆盖索引，索引在搜索过程中，只用到索引 a，所以只会在索引 a 中间隙锁，同时命中条件对应的主键也要加上行锁，所以主键 id=20 被加了行锁。

  2. 间隙锁加在索引 a 上的范围是（10，30），为了更加深度理解加锁范围，如下图：

image.png

场景 1：插入到索引 a 时，要插入是索引是(11,5)，属于(a=10,id=10)和(a=30,id=30)之间的锁范围，所以阻塞

场景 2、3、4 同理分析得出结论

查询条件走主键索引例子

sessionA sessionB
T1 begin;
select * from tb where a = 10 for update;
T2 场景 1：insert into tb values(5,11,11);//阻塞
场景 2: insert into tb values(15,10,8);//阻塞
场景 3：insert into tb values(17,9,1);//阻塞(主键上的锁)
场景 4: insert into tb values(25,35,88);//成功
场景 5：insert into tb values(5,5,5);//阻塞(主键上的锁)
T3 commit;

本例子跟《查询条件走二级索引例子》区别在于 sessionA 是 select * ，因此需要回到主键索引查询所有字段，扫描了主键索引，所以也会在扫描到的索引进行加 next-key lock。该语句回表一次，扫描到是行是 id=10，所以加锁是(0,10],(10,20)，因此 sessionA 一共加了锁是索引 a 的(10,30)和主键索引的（0,20）。

上一篇下一篇

猜你喜欢

热点阅读