likes
comments
collection
share

深入探究MySQL临键锁

作者站长头像
站长
· 阅读数 13

2.4 临键锁

2.4.1 临键锁的区间测试

临键锁(Next-Key Lock):临键锁是查询时InnoDB根据查询的条件而锁定的一个范围,这个范围中包含有间隙锁和记录数;临键锁=间隙锁+记录锁

其设计的目的是为了解决Phantom Problem(幻读);主要是阻塞insert,但由于临键锁中包含有记录锁,因此临键锁所锁定的范围内如果包含有记录,那么也会给这些记录添加记录锁,从而造成阻塞除insert之外的操作;

Tips:临键锁的主要目的,也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC,临键锁则也会失效。

临键锁锁住的区间为:记录+区间(左开右闭)

左开右闭:不锁住左边,锁右边

测试表:

drop table if exists t2;
CREATE TABLE `t2`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) ,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ;
INSERT INTO `t2`(`id`, `num`) VALUES (5, 5);
INSERT INTO `t2`(`id`, `num`) VALUES (10, 10);
INSERT INTO `t2`(`id`, `num`) VALUES (15, 15);
INSERT INTO `t2`(`id`, `num`) VALUES (20, 20);
-- 创建普通索引
create index idx_num on t2(num);
-- 创建唯一索引
create unique index idx_num on t2(num);
-- 删除索引
drop index idx_num on t2;
  • 区间示意图:

深入探究MySQL临键锁

Tips:间隙锁只会阻塞insert,记录锁会阻塞任意的锁(单要注意排他锁和共享锁的关系);

【测试案例-01-间隙锁】

临键锁的触发不仅把条件区间(11-16)的数据行锁住了,还把临键的数据行统统锁住了;锁住的区间为:(10,15]、(15,20]

锁住的id范围:10(不含)~20(含)

session1session2
begin;
begin;
select * from t2 where id>11 and id<16 for update;
insert into t2 values(10,0); -- 不阻塞
insert into t2 values(11,0); -- 阻塞
insert into t2 values(15,0); -- 阻塞
insert into t2 values(16,0); -- 阻塞
insert into t2 values(18,10); -- 阻塞
insert into t2 values(20,0); -- 阻塞
insert into t2 values(21,0); -- 不阻塞
rollback;
rollback;

【案例测试-02-记录锁】

临键锁是间隙锁+记录数的;上述案例中测试了临键锁中的间隙锁,这次我们来测试一下临键锁中的记录锁;

session1session2
begin;
begin;
select * from t2 where id>11 and id<16 for update;
select * from t2 where id=12 for update; -- 间隙锁,不阻塞
select * from t2 where id=15 for update; -- 记录数,阻塞
select * from t2 where id=17 for update; -- 间隙锁,不阻塞
select * from t2 where id=20 for update; -- 记录数,不阻塞
rollback;
rollback;

2.4.2 临键锁-普通列

我们刚刚测试的是以主键索引进行测试,如果采用不同的列(普通列、普通索引、唯一索引/主键索引等),则临键锁中的间隙锁和记录锁住的内容大不相同;

如果查询的是普通列,那么触发的临键锁为:表级别的间隙锁+表级别的记录锁

  • 测试表:
drop table if exists t2;
CREATE TABLE `t2`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) ,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ;
INSERT INTO `t2`(`id`, `num`) VALUES (5, 5);
INSERT INTO `t2`(`id`, `num`) VALUES (10, 10);
INSERT INTO `t2`(`id`, `num`) VALUES (15, 15);
INSERT INTO `t2`(`id`, `num`) VALUES (20, 20);

1)间隙锁

【案例测试-01-表级别间隙锁】

session1session2
begin;
begin;
select * from t2 where num=11 for update;
insert into t2 values(null,3); -- 阻塞
insert into t2 values(null,5); -- 阻塞
insert into t2 values(null,8); -- 阻塞
insert into t2 values(null,10); -- 阻塞
insert into t2 values(null,18); -- 阻塞
insert into t2 values(null,21); -- 阻塞
rollback;
rollback;

Tips:innoDB查询如果没有使用到索引默认触发表级临键锁,把所有的间隙都锁住了

2)记录锁

以普通列查询除了会触发表级别的临键锁外,同时还会触发表级别的记录锁;

【案例测试-02-表级别记录锁】

session1session2
begin;
begin;
select * from t2 where num=11 for update;
select * from t2 where id=3 for update; -- 间隙锁,不阻塞
select * from t2 where id=5 for update; -- 记录数,阻塞
select * from t2 where id=8 for update; -- 间隙锁,不阻塞
select * from t2 where id=15 for update; -- 记录数,阻塞
select * from t2 where id=18 for update; -- 间隙锁,不阻塞
select * from t2 where id=20 for update; -- 记录数,阻塞
rollback;
rollback;

2.4.3 临键锁-普通索引

如果查询的列为普通索引列,要看被查询的记录是否在临界值,以及是否是范围查询,才能判断临建锁的范围;

  • 被查询的记录是否在临界值情况:
    • 非临界值:那么间隙锁为当前记录所在的区间,记录锁则不会生效(记录锁不存在);
    • 临界值:那么间隙锁为相邻的两个区间,记录锁退化成行锁(即只会锁住被查询的那条记录);
  • 范围查询情况:间隙锁为范围所涉及到的所有区间,记录锁也会升级为范围锁涉及到的区间
drop table if exists t2;
CREATE TABLE `t2`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) ,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ;
INSERT INTO `t2`(`id`, `num`) VALUES (5, 5);
INSERT INTO `t2`(`id`, `num`) VALUES (10, 10);
INSERT INTO `t2`(`id`, `num`) VALUES (15, 15);
INSERT INTO `t2`(`id`, `num`) VALUES (20, 20);
-- 创建普通索引
create index idx_num on t2(num);

1)非临界值

当使用普通索引列查询,查询的记录不处于临界值时,那么间隙锁为被查询记录所在的区间,记录锁则不会生效;

【测试案例-01-间隙锁】

session1session2
begin;
begin;
-- 触发间隙锁,锁住(15,20]区间 select * from t2 where num=17 for update;
insert into t2 values(null,15); -- 阻塞
insert into t2 values(null,18); -- 阻塞
insert into t2 values(null,20); -- 不阻塞
rollback;
rollback;

num=17这条记录不是会锁定(15,20]区间吗?为什么15被阻塞了,20反而没被阻塞呢?

这里需要牵扯到另一个问题了,在InnoDB中,相同的 普通索引 的叶子节点是以主键的顺序进行排列的,我们来模拟一下刚刚插入的数据在B+Tree上的变化:

深入探究MySQL临键锁

只考虑叶子节点的变化,可以看到在上图在演变的过程中产生了分裂情况(假设每个叶子节点都只存储两个元素),如果普通索引的重复值太多势必会造成大量的分裂情况,减低插入效率,因此索引列不宜选择重复率太大的列;

再看下图数据库表中实际存储的列的样子我们就会明白为什么num=20不阻塞,num=15阻塞了

  • num索引列排列情况:

深入探究MySQL临键锁

查询示意图:

深入探究MySQL临键锁

【测试案例-02-间隙锁】

当我们把id列的影响也计算进来时,数据就符合我们正常分析的情况了:

session1session2
begin;
begin;
-- 触发间隙锁,锁住(15,20]区间 select * from t2 where num=17 for update;
insert into t2 values(14,15); -- 不阻塞
insert into t2 values(18,18); -- 阻塞
insert into t2 values(19,20); -- 阻塞
rollback;
rollback;

深入探究MySQL临键锁

【测试案例-03-记录锁】

当使用普通索引列查询,查询的记录不处于临界值时,那么间隙锁为被查询记录所在的区间,记录锁则不会生效

session1session2
begin;
begin;
-- 没有num=17的这条记录,记录锁不会存在 select * from t2 where num=17 for update;
select * from t2 where num=15 for update; -- 不阻塞
select * from t2 where num=16 for update; -- 不阻塞
select * from t2 where num=17 for update; -- 不阻塞
select * from t2 where num=20 for update; -- 不阻塞
rollback;
rollback;

2)临界值

【测试案例-01-间隙锁】

当使用普通索引列来查询,并且查询的记录处于临界值时,那么间隙锁为相邻的两个区间,记录锁退化成行锁;

下面案例将会锁住(10,15]、(15,20]两个区间

session1session2
begin;
begin;
-- 触发的间隙锁的区间为(10,15]、(15,20] select * from t2 where num=15 for update;
insert into t2 values(null,8); -- 不阻塞
insert into t2 values(null,10); -- 阻塞
insert into t2 values(null,11); -- 阻塞
insert into t2 values(null,15); -- 阻塞
insert into t2 values(null,18); -- 阻塞
insert into t2 values(null,20); -- 不阻塞
rollback;
rollback;

发现实际插入的数据跟我们分析的情况不一致,这个时候我们依然也要观察B+Tree的实现:

  • 索引底层构建过程:

深入探究MySQL临键锁

  • 临键锁区间:

深入探究MySQL临键锁

15处于 (10,15] (15,20] 两个临键区间,因此在两个区间内的数据行都被锁住了

【测试案例-02-记录锁】

当使用普通索引列来查询,并且查询的记录处于临界值时,那么间隙锁为相邻的两个区间,记录锁退化成行锁

session1session2
begin;
begin;
-- 记录锁只锁住num=15这行记录 select * from t2 where num=15 for update;
select * from t2 where num=10 for update; -- 不阻塞
select * from t2 where num=12 for update; -- 不阻塞
select * from t2 where num=15 for update; -- 阻塞
select * from t2 where num=18 for update; -- 不阻塞
select * from t2 where num=20 for update; -- 不阻塞
select * from t2 where num=22 for update; -- 不阻塞
rollback;
rollback;

3)范围值

【测试案例-01-间隙锁】

当使用普通索引进行条件范围查询时,那么间隙锁查询范围所涉及到的区间,记录锁也会升级为查询范围涉及到的区间;

session1session2
begin;
begin;
-- 间隙锁为(10,20]区间 select * from t2 where num>11 and num <16 for update;
insert into t2 values(9,10); -- 不阻塞
insert into t2 values(11,10); -- 阻塞(参考B+Tree的构建)
insert into t2 values(11,11); -- 阻塞
insert into t2 values(12,12); -- 阻塞
insert into t2 values(15,15); -- 阻塞(被记录锁阻塞)
insert into t2 values(18,18); -- 阻塞
insert into t2 values(19,20); -- 阻塞
insert into t2 values(21,20); -- 不阻塞(参考B+Tree的构建)
rollback;
rollback;
  • 分析底层B+Tree构建情况:

深入探究MySQL临键锁

【测试案例-02-记录锁】

当使用普通索引进行条件范围查询时,那么间隙锁查询范围所涉及到的区间,记录锁也会升级为查询范围涉及到的区间;

session1session2
begin;
begin;
-- 记录锁的区间为(10,20]区间 select * from t2 where num>11 and num <16 for update;
select * from t2 where num=10 for update; -- 不阻塞(左开右闭)
select * from t2 where num=12 for update; -- 不阻塞(属于间隙)
select * from t2 where num=15 for update; -- 阻塞(触发记录锁)
select * from t2 where num=16 for update; -- 不阻塞(属于间隙)
select * from t2 where num=18 for update; -- 不阻塞(属于间隙)
select * from t2 where num=20 for update; -- 阻塞(左开右闭,触发记录锁)
select * from t2 where num=21 for update; -- 不阻塞(即是间隙,也不在区间)
rollback;
rollback;

2.4.4 临键锁-主键和唯一索引

如果查询的是唯一索引或主键索引,也要看被查询的记录是否在临界值;是否是范围查询等

  • 被查询的记录是否在临界值情况:
    • 不在临界值:间隙锁为当前被查询的记录所在的区间,记录锁会消失;
    • 在临界值:间隙锁会消失,记录锁退化成行锁
  • 范围查询情况:间隙锁为范围查询所涉及到的所有区间,记录数也会升级为范围所涉及到的区间(和普通索引的效果一致);

创建唯一索引:

-- 删除索引
drop index idx_num on t2;
-- 创建唯一索引
create unique index idx_num on t2(num);

1)非临界值

唯一索引在查询非临界值的记录时和普通索引的特点一样,即==间隙锁为当前记录所在的区间,记录锁不生效; ==

【测试案例-01-间隙锁】

session-01session-02
begin;
begin;
-- 间隙锁锁住的区间为(15,20] select * from t2 where num=17 for update;
insert into t2 values(null,11); -- 不阻塞
insert into t2 values(null,15); -- 不阻塞
insert into t2 values(null,16); -- 阻塞
insert into t2 values(null,18); -- 阻塞
insert into t2 values(null,20); -- 不阻塞
insert into t2 values(null,21); -- 不阻塞
  • 分析num列索引的B+Tree底层构建情况:

深入探究MySQL临键锁

Tips:唯一索引冲突时MySQL会立即响应,不会触发临键锁

【测试案例-02-记录锁】

唯一索引在查询非临界值的记录时,记录锁不生效;

session1session2
begin;
begin;
-- 没有num=17的这条记录,记录锁不会存在 select * from t2 where num=17 for update;
select * from t2 where num=15 for update; -- 不阻塞
select * from t2 where num=16 for update; -- 不阻塞
select * from t2 where num=17 for update; -- 不阻塞
select * from t2 where num=20 for update; -- 不阻塞
rollback;
rollback;

2)临界值

在使用唯一索引查询临界值时,间隙锁会消失,记录锁会退化成行锁;

【测试案例-01-间隙锁】

session1session2
begin;
begin;
select * from t2 where num=15 for update;
insert into t2 values(null,4); -- 不阻塞
insert into t2 values(null,8); -- 不阻塞
insert into t2 values(null,11); -- 不阻塞
insert into t2 values(null,15); -- 阻塞(阻塞的原因是记录锁,而不是间隙锁)
insert into t2 values(null,28); -- 不阻塞
rollback;insert into t2 values(null,20); -- 不阻塞
rollback;

【测试案例-02-记录锁】

session1session2
begin;
begin;
-- 记录锁只锁住num=15这行记录 select * from t2 where num=15 for update;
select * from t2 where num=10 for update; -- 不阻塞
select * from t2 where num=12 for update; -- 不阻塞
select * from t2 where num=15 for update; -- 阻塞
select * from t2 where num=18 for update; -- 不阻塞
select * from t2 where num=20 for update; -- 不阻塞
select * from t2 where num=22 for update; -- 不阻塞
rollback;
rollback;

3)范围值

【测试案例-01-间隙锁】

当使用普通索引进行条件范围查询时,那么间隙锁查询范围所涉及到的区间,记录锁也会升级为查询范围涉及到的区间;

session1session2
begin;
begin;
-- 间隙锁为(10,20]区间 select * from t2 where num>11 and num <16 for update;
insert into t2 values(9,10); -- 不阻塞
insert into t2 values(11,10); -- 阻塞(参考B+Tree的构建)
insert into t2 values(11,11); -- 阻塞
insert into t2 values(12,12); -- 阻塞
insert into t2 values(15,15); -- 阻塞(被记录锁阻塞)
insert into t2 values(18,18); -- 阻塞
insert into t2 values(19,20); -- 阻塞
insert into t2 values(21,20); -- 不阻塞(参考B+Tree的构建)
rollback;
rollback;
  • 分析底层B+Tree构建情况:

深入探究MySQL临键锁

【测试案例-02-记录锁】

当使用普通索引进行条件范围查询时,那么间隙锁查询范围所涉及到的区间,记录锁也会升级为查询范围涉及到的区间;

session1session2
begin;
begin;
-- 记录锁的区间为(10,20]区间 select * from t2 where num>11 and num <16 for update;
select * from t2 where num=10 for update; -- 不阻塞(左开右闭)
select * from t2 where num=12 for update; -- 不阻塞(属于间隙)
select * from t2 where num=15 for update; -- 阻塞(触发记录锁)
select * from t2 where num=16 for update; -- 不阻塞(属于间隙)
select * from t2 where num=18 for update; -- 不阻塞(属于间隙)
select * from t2 where num=20 for update; -- 阻塞(左开右闭,触发记录锁)
select * from t2 where num=21 for update; -- 不阻塞(即是间隙,也不在区间)
rollback;
rollback;

2.4.5 临键锁总结

临键锁是InnoDB在查询数据时锁定的一个范围,这个范围包含有间隙锁和记录锁;根据查询的条件不同、列的类型不同(是否是索引等)触发的临键锁范围也不同;

  • 普通列:临键锁中的间隙锁和记录数均为表级别;
  • 普通索引列:
    • 非临界值:间隙锁为被查询的记录所在的区间,记录锁不生效
    • 临界值:间隙锁为被查询记录所在的相邻两个区间,记录数退化为行锁
    • 范围值:间隙锁和记录数均为查询条件所涉及到的区间
  • 唯一索引或主键索引列:
    • 非临界值:间隙锁为被查询的记录所在的区间,记录锁不生效
    • 临界值:间隙锁失效,记录锁退化为行锁
    • 范围值:间隙锁和记录数均为查询条件所涉及到的区间

Tips:临键锁的主要目的,也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC,临键锁则也会失效。

转载自:https://juejin.cn/post/7265896869539119143
评论
请登录