Mysql 间隙锁原理,以及 Repeatable Read 隔离级别下可以防止幻读原理

Mysql 知识实在太丰富了,前几天百度的面试官问我 MySql 在 Repeatable Read 下面是否会有幻读出现,我说按照事务的特性当然会有,

但是面试官却说 Mysql 在Repeatable Read 底下 也不会发生幻读的情况,因为 Mysql 有间隙锁的可以防止幻读;


我一头雾水,啥叫间隙锁。以下就是 Mysql 如何利用间隙锁防止幻读的总结:

我们都知道 Mysql,Oracle PostgreSQL 可以利用 MVCC 来处理事务,防止加锁,来提高访问效率

MVCC只是工作在两种事务级别底下:(a) Read Committed (b) Repeatable Read;

因为其他两种:

(c) READ UNCOMMITTED==》总是读取最新的数据,不符合当前事务版本的数据行,

(d) Serializable则会对所有的行加锁。

这两种都不需要MVCC; 

参考:Mysql 的InnoDB事务方面的 多版本并发控制如何实现 MVCC

这样说来 Mysql 也跟其他的数据库一样,当 Repeatable Read的时候会出现幻读的情况,其实不然,Mysql 还有一种机制可以保证即使在 Repeatable Read 级别下面也不会出现幻读。


一、间隙锁

间隙锁跟 MVCC 一起工作。实现事务处理:

Repeatable Read 隔离级别:采用 Next-key Lock(间隙锁)来解决幻读问题。因此 Mysql 在Repeatable 下面 幻读,可重复读,脏读,三者都不会发生。


read committed 隔离级别:采用 Record 锁,不会出现脏读,但是会产生 “幻读” 问题。也会出现可重复读。

(我查了很久,这个 read committed 模式下也会出现可重复读的问题参考:MySQL中Innodb的事务隔离级别和锁的关系的讲解教程


1、间隙锁简介

MySQL InnoDB 支持三种行锁定方式:InnoDB 的默认加锁方式是 next-key 锁。

  • 行锁(Record Lock):锁直接加在索引记录上面,锁住的是 key。
  • 间隙锁(Gap Lock):锁定索引记录间隙,确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。
  • Next-Key Lock :行锁和间隙锁组合起来就叫 Next-Key Lock。 

默认情况下,InnoDB 工作在可重复读(Repeatable Read)隔离级别下,并且会以 Next-Key Lock 的方式对数据行进行加锁,这样可以有效防止幻读的发生。Next-Key Lock 是行锁和间隙锁的组合,当 InnoDB 扫描索引记录的时候,会首先对索引记录加上行锁(Record Lock),再对索引记录两边的间隙加上间隙锁(Gap Lock)。加上间隙锁之后,其他事务就不能在这个间隙修改或者插入记录。 read committed隔离级别下

Gap Lock 在 InnoDB 的唯一作用就是防止其他事务的插入操作,以此防止幻读的发生。

  Innodb 自动使用间隙锁的条件:

(1)必须在Repeatable Read级别下
(2)检索条件必须有索引(没有索引的话,mysql会全表扫描,那样会锁定整张表所有的记录,包括不存在的记录,此时其他事务不能修改不能删除不能添加)
 

2、行锁(Record Lock)

记录锁其实很好理解,对表中的记录加锁,叫做记录锁,简称行锁。 

3、生活中的间隙锁(Gap Lock)

编程的思想源于生活,生活中的例子能帮助我们更好的理解一些编程中的思想。
生活中排队的场景,小明、小红、小花三个人依次站成一排,此时,如何让新来的小刚不能站在小红旁边,这时候只要将小红和她前面的小明之间的空隙封锁,将小红和她后面的小花之间的空隙封锁,那么小刚就不能站到小红的旁边。
这里的小红、小明、小花、小刚就是数据库的一条条记录。
他们之间的空隙也就是间隙,而封锁他们之间距离的锁,叫做间隙锁。 


4、Mysql 中的间隙锁

下表中(见图一),id 为主键,number 字段上有非唯一索引的二级索引,有什么方式可以让该表不能再插入 number=5 的记录? 

图一 

根据上面生活中的例子,我们自然而然可以想到,只要控制几个点,number=5 之前不能插入记录,number=5 现有的记录之间不能再插入新的记录,number=5 之后不能插入新的记录,那么新的 number=5 的记录将不能被插入进来。 

那么,mysql 是如何控制 number=5 之前,之中,之后不能有新的记录插入呢(防止幻读)?
答案是用间隙锁,在 RR 级别下,mysql 通过间隙锁可以实现锁定 number=5 之前的间隙,number=5 记录之间的间隙,number=5 之后的间隙,从而使的新的记录无法被插入进来。 


间隙是怎么划分的? 

注:为了方面理解,我们规定(id=A,number=B)代表一条字段 id=A,字段 number=B 的记录,(C,D)代表一个区间,代表C-D这个区间范围。

图一中,根据number列,我们可以分为几个区间:(无穷小,2),(2,4),(4,5),(5,5),(5,11),(11,无穷大)。
只要这些区间对应的两个临界记录中间可以插入记录,就认为区间对应的记录之间有间隙。
例如:区间(2,4)分别对应的临界记录是(id=1,number=2),(id=3,number=4),这两条记录中间可以插入(id=2,number=3)等记录,那么就认为(id=1,number=2)与(id=3,number=4)之间存在间隙。

很多人会问,那记录(id=6,number=5)与(id=8,number=5)之间有间隙吗?
答案是有的,(id=6,number=5)与(id=8,number=5)之间可以插入记录(id=7,number=5),因此(id=6,number=5)与(id=8,number=5)之间有间隙的,


5、间隙锁锁定的区域

根据检索条件向左寻找最靠近检索条件的记录值 A,作为左区间,向右寻找最靠近检索条件的记录值 B 作为右区间,即锁定的间隙为(A,B)。
图一中,where number=5的话,那么间隙锁的区间范围为(4,11);

间隙锁的目的是为了防止幻读,其主要通过两个方面实现这个目的:

(1)防止间隙内有新数据被插入
(2)防止已存在的数据,更新成间隙内的数据(例如防止numer=3的记录通过update变成number=5)

间隙锁在 InnoDB 的唯一作用就是防止其它事务的插入操作,以此来达到防止幻读的发生,所以间隙锁不分什么共享锁与排它锁。 默认情况下,InnoDB 工作在 Repeatable Read 隔离级别下,并且以 Next-Key Lock 的方式对数据行进行加锁,这样可以有效防止幻读的发生。Next-Key Lock 是行锁与间隙锁的组合,当对数据进行条件,范围检索时,对其范围内也许并存在的值进行加锁!当查询的索引含有唯一属性(唯一索引,主键索引)时,Innodb 存储引擎会对 next-key lock 进行优化,将其降为 record lock,即仅锁住索引本身,而不是范围!若是普通辅助索引,则会使用传统的 next-key lock 进行范围锁定!

要禁止间隙锁的话,可以把隔离级别降为 Read Committed,或者开启参数innodb_locks_unsafe_for_binlog

对于快照读来说,幻读的解决是依赖 mvcc 解决。而对于当前读则依赖于 gap-lock 解决。


二、深层次的原理分析

在 MVCC 并发控制中,读操作可以分成两类:快照读(snapshot read)与当前读(current read)。

快照读,读取的是记录的可见版本(有可能是历史版本),不用加锁。

当前读,读取的是记录的最新版本,并且,当前读返回的记录都会加上锁,保证其他事务不会再并发修改这条记录。 

在一个支持 MVCC 并发控制的系统中,哪些读操作是快照读?哪些操作又是当前读呢?以 MySQL InnoDB 为例: 

  • 快照读:简单的select操作,属于快照读,不加锁。(当然,也有例外,下面会分析)

    • select * from table where ?; 

  • 当前读:特殊的读操作,插入/更新/删除操作,属于当前读,需要加锁。

    • select * from table where ? lock in share mode;

    • select * from table where ? for update;

    • insert into table values (…);

    • update table set ? where ?;

    • delete from table where ?;

所有以上的语句,都属于当前读,读取记录的最新版本。并且,读取之后,还需要保证其他并发事务不能修改当前记录,对读取记录加锁。其中,除了第一条语句,对读取记录加 S 锁(共享锁)外,其他的操作,都加的是 X 锁(排它锁)。 


1、MySQL / InnoDB 定义的 4 种隔离级别

  • Read Uncommited

可以读取未提交记录。此隔离级别,不会使用,忽略。

  • Read Committed (RC)

快照读忽略,本文不考虑。

针对当前读,RC隔离级别保证对读取到的记录加锁 (record lock),存在幻读现象。

  • Repeatable Read (RR)

快照读忽略,本文不考虑。

针对当前读,RR隔离级别保证对读取到的记录加锁 (记录锁),同时保证对读取的范围加锁,新的满足查询条件的记录不能够插入 (间隙锁),不存在幻读现象。

  • Serializable

从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读,所有的读操作均为当前读,读加读锁 (S锁),写加写锁 (X锁)。

Serializable隔离级别下,读写冲突,因此并发度急剧下降,在MySQL/InnoDB下不建议使用。


三、参考内容 

参考:MySQL中Innodb的事务隔离级别和锁的关系的讲解教程

参考:Mysql数据库事务的隔离级别和锁的实现原理分析

参考:Mysql innodb 间隙锁 (转)

参考:mysql repeatable-read 一次利用间隙锁解决幻读案例

参考:InnoDB间隙锁简介

参考:innodb中幻读与mvcc和间隙锁分析

参考:mysql-repeatable read可以避免幻读

  • 2
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值