乐趣区

关于mysql:拿捏隔离级别幻读Gap-LockNextKey-Lock

后面我写了很多 Mysql 相干的知识点,到这一篇略微能够串一下了,从 SQL 执行流程、MVCC 到锁,很多时候可能感觉对于间隙锁和 Next-Key Lock 如同曾经了解了,然而如同又感觉了解差那么一点意思,这篇文章从头来梳理一下概念,明确一下这些常识。

首先,对于 Mysql 来说实现了两种行级锁:

共享锁:容许事务读一行数据,个别记为 S,也称为读锁

排他锁:容许事务删除或者更新一行数据,个别记为 X,也称为写锁

对于读写锁的互斥性,应该都很分明,读锁只能和读锁兼容,其余场景都无奈兼容,这里不再赘述吧。

隔离级别

持续回顾下对于 Mysql 的 4 个隔离级别:

读未提交 Read Uncommitted:能读到其余事务还没有提交的数据,这种景象叫做脏读。

读已提交 Read Committed:只会读取其余事务曾经提交的数据,所以不会产生 RC 的脏读问题。所以又带来一个问题叫做不可反复读,一个事务中两次一样的 SQL 查问可能查到的后果不一样。

可反复读 Repeatable Read:RR 是 Mysql 的默认隔离级别,一个事务中两次 SQL 查问总是会查到一样的后果,不存在不可反复读的问题,然而还是会有幻读的问题。

串行 Serializable:串行场景没有任何问题,齐全串行化的操作,读加读锁,写加写锁。

幻读、Next-Key Lock、MVCC

简略的回顾完了根底,那么咱们看看 RR 级别下还会存在的幻读到底是什么问题,Mysql 官网文档这样形容的:

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a“phantom”row.

翻译过去就是,幻读指的是同一事务下,不同的工夫点,同样的查问,失去不同的行记录的汇合。

如果说一个 select 执行了两次,然而第二次比第一次多进去行记录,这就是幻读。

所以,对于幻读来说那肯定是新增插入的数据!

比如说在一个事务内,先查问 select * from user where age=10 for update,失去的后果是 id 为[1,2,3] 的记录,再次执行查问,失去了后果为 [1,2,3,4] 的记录,这是幻读。

那怎么解决幻读的问题?以前我在文章里说解决幻读的原理是 MVCC(MVCC 原理看这里)很多网上的文章也有这么写的,其实不能说错,然而必定也是不太对的,精确地来说应该是通过 MVCC+Next-Key Lock 的形式才解决了幻读的问题。

对于 MVCC 中的读能够分为两种,别离叫做 快照读 以后读(这个以后读的说法我在书里翻了半天也没有找到,然而看网上一堆材料和大佬都叫以后读,那么咱们就叫以后读吧,你晓得的话能够通知我哪本书有这个称说,Mysql 我只看见 Lock reading 或者锁定读的叫法,有的也说锁定读就是以后读,然而并没有找到以后读这种称说的出处在哪儿)。

快照读 就是简略的 select 查问,查问的都是快照版本,这个场景下因为都是基于 MVCC 来查问快照的某个版本,所以不会存在幻读的问题,也能够认为是解决了幻读的计划之一,对于 RC 级别来说,因为每次查问都从新生成一个 read view,也就是查问的都是最新的快照数据,所以会可能每次查问到不一样的数据,造成不可反复读,而对于 RR 级别来说只有第一次的时候生成 read view,查问的是事务开始的时候的快照数据,所以就不存在不可反复读的问题,当然就更不可能有幻读的问题了。

所以,当初咱们说幻读,其实不是指快照读的场景,而是指的是以后读的场景。

以后读 指的是 lock in share modefor updateinsertupdatedelete 这些须要加锁的操作。对于 MVCC 来说就是解决的快照读的场景,而对于以后读那么就是 Next-Key Lock 要解决的事件。

那么 Next-Key Lock 是什么?怎么解决的幻读?

行锁有写锁 X 和读锁 S 两种,实际上行锁有 3 种实现算法,Next-Key Lock 是其中之一。

第一种叫做Record Lock,字面意思,行记录的锁,实际上指的是对索引记录的锁定。

比方执行语句 select * from user where age=10 for update,将会锁住user 表所有 age=10 的行记录,所有对 age=10 的记录的操作都会被阻塞。

第二种都比拟相熟,叫做 Gap Lock,也就是 间隙锁,它用于锁定的索引之间的间隙,然而不会蕴含记录自身。

比方语句 select * from user where age>1 and age<10 for update,将会锁住age 在(1,10)的范畴区间,此时其余事务对该区间的操作都会被阻塞。

间隙锁是可反复读 RR 隔离级别下特有的,另外还有几种场景也会不应用间隙锁。

  1. 事务隔离级别设置为不可反复读 RC,这样必定没有间隙锁了。
  2. Innodb_locks_unsafe_for_binlog设置为 1
  3. 另外一种状况实用于 主键索引或者惟一索引 的等值查问条件,比方 select * from user where id=1id 是主键索引,这样只应用 Record Lock 就能够了,因为能惟一锁定一条记录,所以没有必要再加间隙锁了,这是锁降级的过程。

而第三种 Next-Key Lock 实际上就是相当于 Record Lock+Gap Lock 的组合。比方索引有 10,20,30 几个值,那么被锁住的区间可能会是(-∞,10],(10,20],(20,30],(30,+∞)。

解决幻读

上一篇对于更新 SQL 执行过程咱们曾经对这个根底有了肯定的理解,在这里咱们去掉和这里内容无关的一些日志的细节,把给数据加锁的流程退出进去,这样通过 SQL 执行能够更好地了解 Next-Key Lock 到底是如何解决幻读的,执行过程如下:

  1. 首先第一步 Server 层会来查问数据
  2. 存储引擎依据查问条件查到数据之后对数据进行加锁,Record Lock 或者间隙锁,而后返回数据
  3. Server 层拿到数据之后调用 API 去存储引擎更新数据
  4. 最初存储引擎返回后果,流程完结

搞一张表阐明一下,user表有 4 个字段,id是主键索引,name是惟一索引,age是一般索引,city没有索引,而后插入一些测试数据,上面辨别一下几种状况来阐明是怎么加 Next-Key Lock 的,而后就晓得为啥会没有幻读的问题了。

没有索引

更新语句 update user set city='nanjing' where city='wuhan' 会产生什么?

因为 city 是没有索引的,所以存储引擎只能给所有的记录都加上锁,而后把数据都返回给 Server 层,而后 Server 层把 city 改成nanjing,再更新数据。

因而,首先 Record Lock 会锁住现有的 7 条记录,间隙锁则会对主键索引的间隙全副加上间隙锁。

所以,更新的时候没有索引是十分可怕的一件事件,相当于把整个表都给锁了,那表都给锁了当然不存在幻读了。

一般索引

咱们再假如一个语句select * from user where age=20 for update

因为 age 是一个一般索引,存储引擎依据条件过滤查到所有匹配 age=20 的记录,给他们加上写锁,间隙锁会加在 (10,20),(20,30) 的区间上,因而当初无论怎样都无奈插入 age=20 的记录了

为什么要锁定这两个区间?如果不锁定这两个区间的话,那么还能插入比方 id=11,age=20 或者 id=21,age=20 的记录,这样就存在幻读了。

(那实际上写锁不光是在会加在 age 一般索引上,还会加在主键索引上,因为数据都是在主键索引下对吧,这个必定也要加锁的,为了看起来简略点,就不画进去了)

惟一 & 主键索引

如果查问的是惟一索引又会产生什么呢?比方有查问语句select * from user where name='b' for update

下面咱们提到过,如果是惟一索引或者主键索引的话,并且是等值查问,实际上会产生锁降级,降级为 Record Lock,就不会有间隙锁了。

因为主键或者惟一索引能保障值是惟一的,所以也就不须要再减少间隙锁了。

很显然,是无奈插入 name=b 的的记录的,也不存在幻读问题。

如果是范畴查问比方 id>1 and id<11 呢,实际上也是一样的锁定形式,不再赘述。

相比略微有点不同的是下面也说过,惟一索引不光锁定惟一索引,还会锁定主键索引,主键索引的话只有索引主键索引就行了。

总结

那最初说了这么多,RR 级别下不是都曾经解决了幻读的问题吗,怎么还说有幻读的问题呢?

对于这个问题,能够看看这个报出的 BUGhttps://bugs.mysql.com/bug.ph…,回复说了这不是 BUG,这是合乎隔离标准的设计,有趣味的本人看看吧。

退出移动版