mysql 为什么默认的隔离级别是RR,间隙锁机制

rr隔离级别的间隙锁;

1.rr + 唯一索引

       范围查询,等值查询(等值不存在的情况)

 2.rr + 非唯一索引。

首先查看mysql的当前事务隔离级别:select @@tx_isolation;

查看全局的事务隔离级别:SELECT @@global.tx_isolation;

修改事务隔离级别

MySQL 提供了 SET TRANSACTION 语句,该语句可以改变单个会话或全局的事务隔离级别。语法格式如下:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

其中,SESSION 和 GLOBAL 关键字用来指定修改的事务隔离级别的范围:

  • SESSION:表示修改的事务隔离级别将应用于当前 session(当前 cmd 窗口)内的所有事务;
  • GLOBAL:表示修改的事务隔离级别将应用于所有 session(全局)中的所有事务,且当前已经存在的 session 不受影响;
  • 如果省略 SESSION 和 GLOBAL,表示修改的事务隔离级别将应用于当前 session 内的下一个还未开始的事务。

任何用户都能改变会话的事务隔离级别,但是只有拥有 SUPER 权限的用户才能改变全局的事务隔离级别。

修改mysql的隔离级别为rc;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

在rc的隔离级别下没有索引的情况,左边事务还没有提交,左边事务可以更新成功。 

 

rc隔离级别若id列上没有索引,MySQL会走聚簇索引进行全表扫描过滤。由于是在MySQl Server层面进行的。因此每条记录无论是否满足条件,都会加上X锁,但是,为了效率考虑,MySQL在这方面进行了改进,在扫描过程中,若记录不满足过滤条件,会进行解锁操作。同时优化违背了2PL原则。

两阶段锁

传统的RDMS加锁的一个原则,就是2PL(Two-Phase Locking,二阶段锁)。也就是说锁操作分为两个阶段:加锁阶段和解锁阶段,并且保证加锁阶段和解锁阶段不想交。也就是说在一个事务中,不管有多少条增删改,都是在加锁阶段加锁,在 commit 后,进入解锁阶段,才会全部解锁。

 RR隔离级别下,没有索引的情况,在左边事务执行update t1 set a=11 where b = 2;会锁住全表每条记录要加X锁,每个Gap加上Gap锁,相当于锁住全表。右边事务不允许做任何写操作。 

 

什么是幻读。

间隙锁的作用:防止幻读

间隙锁是怎样防止幻读的呢?RR隔离级别下,也不是能够完全解决幻读的,只能是在某个区间上加上间隙锁,从而阻止其他事物在这个区间上插入数据,避免当前事物进行当前读的时候出现幻读。

RR隔离级别下,主键索引/唯一索引,update t1 set b =7 where a = 7,即使a = 7 不存在,也会加上间隙锁(5,10),导致右边事务不能正常插入数据。

 

 RR隔离级别下,主键索引,select * from t1 where a>=10 and a<11 for update;会加间隙锁 [10,20]. 

RR隔离级别下,主键索引, 执行 select * from t1 where a>5 and a<10 for update; 间隙锁(5,10],左开右闭。

RR隔离级别下,主键索引,select * from t1 where a>7 and a<10 for update; 7不存在,会锁住7左边第一个存在值的间隙锁:(5,10],

在rr  主键  范围查询的时候间隙锁区间是左开右闭。

eg:  where a>7 and a < 10   (5,10].

组合三:id不唯一索引+RC

该组合中,id列不在唯一,而是个普通索引,那么当执行sql语句时,MySQL又是如何加锁呢?看下图:

由上图可以看出,首先,id列索引上,满足id = 10查询的记录,均加上X锁。同时,这些记录对应的主键索引上的记录也加上X锁。与组合er的唯一区别,组合二最多只有一个满足条件的记录,而在组合三中会将所有满足条件的记录全部加上锁。

结论若id列上有非唯一索引,那么对应的所有满足SQL查询条件的记录,都会加上锁。同时,这些记录在主键索引上也会加上锁。

组合七:id不唯一索引+RR

在组合一到组合四中,隔离级别是Read Committed下,会出现幻读情况,但是在该组合Repeatable Read级别下,不会出现幻读情况,这是怎么回事呢?而MySQL又是如何给上述语句加锁呢?看下图:

该组合和组合三看起来很相似,但差别很大,在改组合中加入了一个间隙锁(Gap锁)。这个Gap锁就是相对于RC级别下,RR级别下不会出现幻读情况的关键。实质上,Gap锁不是针对于记录本身的,而是记录之间的Gap。所谓幻读,就是同一事务下,连续进行多次当前读,且读取一个范围内的记录(包括直接查询所有记录结果或者做聚合统计), 发现结果不一致(标准档案一般指记录增多, 记录的减少应该也算是幻读)。

那么该如何解决这个问题呢?如何保证多次当前读返回一致的记录,那么就需要在多个当前读之间,其他事务不会插入新的满足条件的记录并提交。为了实现该结果,Gap锁就应运而生。

如图所示,有些位置可以插入新的满足条件的记录,考虑到B+树的有序性,满足条件的记录一定是具有连续性的。因此会在 [4, b], [10, c], [10, d], [20, e] 之间加上Gap锁。

Insert操作时,如insert(10, aa),首先定位到 [4, b], [10, c]间,然后插入在插入之前,会检查该Gap是否加锁了,如果被锁上了,则Insert不能加入记录。因此通过第一次当前读,会把满足条件的记录加上X锁,还会加上三把Gap锁,将可能插入满足条件记录的3个Gap锁上,保证后续的Insert不能插入新的满足 id = 10 的记录,也就解决了幻读问题。

而在组合五,组合六中,同样是RR级别,但是不用加上Gap锁,在组合五中id是主键,组合六中id是Unique键,都能保证唯一性。一个等值查询,最多只能返回一条满足条件的记录,而且新的相同取值的记录是无法插入的。

结论:在RR隔离级别下,id列上有非唯一索引,对于上述的SQL语句;首先,通过id索引定位到第一条满足条件的记录,给记录加上X锁,并且给Gap加上Gap锁,然后在主键聚簇索引上满足相同条件的记录加上X锁,然后返回;之后读取下一条记录重复进行。直至第一条出现不满足条件的记录,此时,不需要给记录加上X锁,但是需要给Gap加上Gap锁吗,最后返回结果。

一条简单的更新语句,MySQL是如何加锁的? - 掘金

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

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值