深入了解mysql--gap locks,Next-Key Locks

Next-Key Locks

Next-Key Locks是在存储引擎innodb、事务级别在可重复读的情况下使用的数据库锁,官网上有介绍,Next-Key Locks是行锁和gap锁的组合。行锁是什么我们都很清楚,这篇文章主要简单分析一下mysql中的gap锁是什么。innodb默认的锁就是Next-Key locks。

GAP锁

gap锁,又称为间隙锁。存在的主要目的就是为了防止在可重复读的事务级别下,出现幻读问题。

在可重复读的事务级别下面,普通的select读的是快照,不存在幻读情况,但是如果加上for update的话,读取是已提交事务数据,gap锁保证for update情况下,不出现幻读。

那么gap锁到底是如何加锁的呢?

假如是for update级别操作,先看看几条总结的何时加锁的规则。

  • 唯一索引
    • 精确等值检索,Next-Key Locks就退化为记录锁,不会加gap锁
    • 范围检索,会锁住where条件中相应的范围,范围中的记录以及间隙,换言之就是加上记录锁和gap 锁(至于区间是多大稍后讨论)。
    • 不走索引检索,全表间隙加gap锁、全表记录加记录锁
  • 非唯一索引
    • 精确等值检索,Next-Key Locks会对间隙加gap锁(至于区间是多大稍后讨论),以及对应检索到的记录加记录锁。
    • 范围检索,会锁住where条件中相应的范围,范围中的记录以及间隙,换言之就是加上记录锁和gap 锁(至于区间是多大稍后讨论)。
  • 非索引检索,全表间隙gap lock,全表记录record lock

gap锁演示例子

假如有以下一张表结构,主键简单点是字符,属性列只有一个数字,是非唯一索引。

create table gap_table
(
  letter varchar(2) default '' not null primary key,
  num int not null
);

create index gap_table_num_uindex  on gap_table (num);
  
INSERT INTO gap_table (letter, num) VALUES ('d', 3);
INSERT INTO gap_table (letter, num) VALUES ('g', 6);
INSERT INTO gap_table (letter, num) VALUES ('j', 8);

无gap锁

假如没有gap锁,也就是把事务级别调到读提交,执行以下两个session

session1session2
select * from gap_table where num=6 for update,结果是一条  
  INSERT INTO gap_table (letter, num) VALUES (’’, 6);
select * from gap_table where num=6 for update,结果是二条,出现幻读  

非唯一索引等值检索gap锁

假如有gap锁,演示一个非唯一索引等值检索gap锁。也就是把事务级别调到可重复读,执行以下两个session

session1session2
select * from gap_table where num=6 for update,结果是一条。  
  INSERT INTO gap_table (letter, num) VALUES (’’, 6);gap锁住间隙,阻塞无法插入数据。
select * from gap_table where num=6 for update,结果是一条。不出现幻读  

唯一索引(主键)范围检索gap锁

假如有gap锁,演示一个唯一索引范围检索gap锁。也就是把事务级别调到可重复读,执行以下两个session

session1session2
select * from gap_table where letter>‘d’ for update,结果是两条。  
  INSERT INTO gap_table (letter, num) VALUES (‘z’, 10);gap锁住间隙,阻塞无法插入数据。
select * from gap_table where letter>‘d’ for update,结果是两条。不出现幻读  

gap锁是如何锁区间?

经过上面的演示可以知道gap锁的基本作用就是保证可重复读的情况下不出现幻读。那么还有一点就是gap是按照什么原则进行锁的呢?要了解gap锁的原则,需要先了解innodb中索引树的结构。下面一张图片描述了在innodb中,索引的数据结构是如何组织的
索引树结构

从上面的图片可以看出,索引结构分为主索引树和辅助索引树,辅助索引树的叶子节点中包含了主键数据,主键数据影响着叶子节点的排序,gap锁的关键就是锁住索引树的叶子节点之间的间隙,不让新的记录插入到间隙之中,说起来可能拗口,下面画图分析。

非唯一索引gap锁原则分析

假如还是使用一开始演示的表结构和数据,那么当前的辅助索引树(数字列)叶子节点的排序结构应该如下。


在这里插入图片描述
假如执行以下sql的话

INSERT INTO gap_table (letter, num) VALUES ('k', 6);

辅助索引树的叶子节点结构变为以下图片结构,k大于g,所以(6,k)排在后面,我们先把(6,k)这条数据删除,方便后面演示。
在这里插入图片描述

了解了以上的规则,我们进行实际操作演示gap锁区间原则,从而推测锁住哪些区间。

情况1

分别有两个session,session1执行以下语句:

select * from gap_table where num=6 for update

session2执行以下sql,执行成功:

INSERT INTO gap_table (letter, num) VALUES ('a', 3);

按照排序规则,叶子节点插入结构如下


在这里插入图片描述

情况2

分别有两个session,session1执行以下语句:

select * from gap_table where num=6 for update

session2执行以下sql,执行失败:

INSERT INTO gap_table (letter, num) VALUES ('e', 3);

按照排序规则,叶子节点应该插入如下地方,但是因为区间被锁插入失败。


在这里插入图片描述

情况3

分别有两个session,session1执行以下语句:

select * from gap_table where num=6 for update

session2执行以下sql,执行失败:

INSERT INTO gap_table (letter, num) VALUES ('h', 6);

按照排序规则,叶子节点应该插入如下地方,但是因为区间被锁插入失败。


在这里插入图片描述

情况4

分别有两个session,session1执行以下语句:

select * from gap_table where num=6 for update

session2执行以下sql,执行失败:

INSERT INTO gap_table (letter, num) VALUES ('h', 7);

按照排序规则,叶子节点应该插入如下地方,但是因为区间被锁插入失败。


在这里插入图片描述

情况5

分别有两个session,session1执行以下语句:

select * from gap_table where num=6 for update

session2执行以下sql,执行成功:

INSERT INTO gap_table (letter, num) VALUES ('h', 9);

按照排序规则,插入在未锁区间就能插入成功。


在这里插入图片描述

总结

当session1执行以下语句:

select * from gap_table where num=6 for update

锁住的区间如图所示。按照B+索引树排序规则,计算好叶子节点插入位置时,在被gap锁住的区间段内,不能插入任何数据,只有在gap锁释放时才能进行插入。


在这里插入图片描述

在上面的各种情况中锁住的区间其实是(3,d)到(6,g)和(6,g)到(8,j),落到这个区间段的叶子节点都是无法插入的。主键也作为一个信息参与到叶子节点的排序规则中。这里面边界都是开区间,插入(3,d),(8,j)的数据会报错主键重复而不是lock等待超时。

唯一索引或者非唯一索引范围检索gap锁原则分析

另一种会出现gap锁的情况就是使用索引时,用到范围检索,就会出现gap 锁。

使用以下表结构。

create table gap_tbz
(
  id   int default 0 not null
    primary key,
  name varchar(11) not null
);

INSERT INTO test.gap_tbz (id, name) VALUES (1, 'a');
INSERT INTO test.gap_tbz (id, name) VALUES (5, 'h');
INSERT INTO test.gap_tbz (id, name) VALUES (8, 'm');
INSERT INTO test.gap_tbz (id, name) VALUES (11, 'ds');

情况1

分别有两个session,session1执行以下语句:

select * from gap_tbz where id > 5 for update;

session2执行以下sql,执行失败:

insert into gap_tbz values(6,'cc');

按照排序规则,这里应该是在主键索引树检索,叶子节点插入结构如下。由于session1执行了范围的for update sql语句,因此范围内添加了gap锁,gap锁的区间是id在(5,+无限)
在这里插入图片描述

当执行插入的id范围在5之前,如下sql,能够执行成功。

insert into gap_tbz values(4,'cc');

情况2

分别有两个session,session1执行以下语句:

select * from gap_tbz where id > 5 and id < 11 for update;

session2执行以下sql,执行失败:

#以下报错 lock等待超时
insert into gap_tbz values(11,'cc');

#以下报错 主键重复
insert into gap_tbz values(5,'cc');

#从两种报错来看也可以看出gap锁区间是左开右闭

按照排序规则,这里应该是在主键索引树检索,由于session1执行了范围的for update sql语句,因此范围内添加了gap锁,gap锁的区间是id在(5,11],唯一索引gap锁区间是左开右闭。

思考

假如条件是一个非索引列,那么如何处理?

假如是非索引咧,那么将会全表间隙加上gap锁。

条件是唯一索引等值检索且记录不存在的情况,会使用gap lock?

我们要考虑,gap lock是防止幻读,那么尝试思考,使用唯一索引所谓条件查找数据for update,如果对应的记录不存在的话,是无法使用行锁的。这时候,会使用gap lock来锁住区间,保证记录不会插入,防止出现幻读。

 

文章转载自:https://blog.csdn.net/tb3039450/article/details/66475638

posted @ 2019-07-11 10:28  aizhen  阅读(4442)  评论(1编辑  收藏  举报