MYSQL RR隔离级别下MVCC及锁解读

MVCC(Multi-Version Concurrent Control):多版本并发控制,只作用于RC和RR隔离级别,主要是为了避免脏读、非重复读,而非幻读,很多文章说通过MVCC避免幻读,其实这种说法是不完善的,RR隔离级别是通过next-key lock 来避免幻读。

优点:避免了许多需要加锁的情形

缺点:需要维护每行记录版本号,造成额外资源消耗

事物四种隔离级别:

我们采用什么隔离级别?

四种隔离级别的锁粒度由小到大,并发性能由优到差,所以采用哪种隔离级别需要根据业务情况来定。目前采用较多的就是RC和RR两种,RR为默认隔离级别。

脏读

所有事务都可以看到其他未提交事务的执行结果

不可重复读

同一个事务执行过程中,另外一个事务提交了新数据,因此本事务先后两次读到的数据结果会不一致

幻读

当事物A查询某一范围的数据时,另一个事务B又在该范围内插入了新行并作了提交,此时事物A看不到新行,却在新行做了更新操作,此时事物A再查询会看到新行,就想产生了幻觉一样。

怎么避免脏读、不可重复读、幻读?

采用RR隔离级别,结合MVCC特性,可以避免脏读、非重复读,有些文章说MVCC用来避免幻读,其实这是不准确的,MVCC通过多版本并发控制来避免非重复读,像幻读定义所说的情况即使有MVCC还是会存在。RR隔离级别是通过禁用innodb_locks_unsafe_for_binlog,在搜索和扫描索引的时候使用next-key locks来避免幻读(下面有对锁说明)。也就是为什么RR隔离级别下,非主键索引DML的操作并发性能会下降的原因了。

为了减少Next-key lock影响,可以设置innodb_locks_unsafe_for_binlog=1,就是disable Next-Key lock,但是并不建议。

想要真正避免幻读只能采取serializable串行化隔离级别,因为都要加表级共享锁或排他锁,所以性能会很差,一般不会采用。

MVCC如何避免非重复读:

MVCC为查询提供了一个基于时间的点的快照。这个查询只能看到在自己之前提交的数据,而在查询开始之后提交的数据是不可以看到的。

在每行记录后面记录两个隐藏的列,一个记录创建时间,一个记录删除时间,记录的是版本号,这里可以理解为事物号。

INSERT:Innodb 为新插入的每一行保存当前系统版本号作为行版本号;

DELETE:Innodb 为删除的每一行保存当前系统版本号作为行删除标识;

UPDATE:Innodb 为插入一行新记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为行删除标识。

示例:特殊幻读

Session A:

Session B:

begin;

begin;

insert into test6(id,name) values(11,'aa');update test6 set name='JJ' wher eid=10;commit;

select * from test6;(第一次查询)| 8 | h || 9 | I || 10 | j |

update test6 set name='AA' where id=11;(更新了它并没有看到的行)

select * from test6;(第二次查询)| 8 | h || 9 | I || 10 | j || 11 | AA |

commit;

select * from test6;(第三次查询)| 8 | h || 9 | I || 10 | JJ || 11 | AA |

Session A的第二次查询中出现了一个不存在的值,这里Session A的第一次、第二次读,均为快照读,而且是在同一个事务中。但是Session B先插入直接提交,此时A再update,update属于当前读,所以可以作用于新插入的行,并且将修改行的当前版本号设为Session A的事务号,所以第二次的快照读,是可以读取到的,因为同事务号。这种情况符合MVCC的规则,如果要称为一种幻读也非不可,算为一个特殊情况来看待。

RR隔离级别下锁介绍

Record Lock:

在主键或唯一索引上对单行记录加锁

Gap Lock:

针对非唯一索引而言,锁定一个范围的记录,但不包括记录本身。锁加在未使用的空闲空间上,可能是两个索引记录之间,也可能是第一个索引记录之前或最后一个索引之后的空间。

如果更新两端的记录会影响到间隙锁,那么操作会被挂起,等待间隙锁释放。

比如锁定范围(4,7),update table set v1=6 where v1=1; 虽然1不在此范围,但是6在(4,7)范围还是会锁定。

Next-Key Lock:

针对非唯一索引而言,行记录锁与间隙锁组合起来用就叫做Next-Key Lock。锁定一个范围,并且锁定记录本身。对于行的查询,都是采用该方法,主要目的是解决幻读的问题。

通过一个例子介绍间隙锁

表test5中存在如下数据:

select * from test5 where v1=45 for update; 对v1=45的行加X锁,此时会对(40,45][45,50)加间隙锁,其他事物不能操作在此范围内的数据。

但是为什么在左侧值为40,右侧值为50的时候,有时候操作会被挂起,有时候操作不会挂起呢?

update table set v1=41 where v1=40;41在(40,50)范围会被锁定。

update table set v1=39 where v1=40; 39不在(40,50)范围不会被锁定。

update table set v1=42 where v1=1; 42在(40,50)范围会被锁定。

update table set v1=30 where v1=45; 30不在(40,50)范围,但是45行上面存在的行级record lock,45行记录也被加了锁。

insert into table(id,name) values(14,40);可以插入

insert into table(id,name) values(20,40);不可以插入

insert into table(id,name) values(13,50);不可以插入

insert into table(id,name) values(21,40);可以插入

当插入左侧值的时候,即插入v1=40的时候,要求插入的id值小于id=16的范围。当v1=40的记录有多条的时候,插入的id值要小于其中的最大id值。则可以成功插入;

当插入右侧值的时候,即插入v1=50的时候,要求插入的id值要大于id=18的范围。当v1=50的记录有多条的时候,插入的id值要大于其中的最小id值。则可以成功插入。

所以为什么RR隔离级别下并发性能会有所下降,就是因为存在间隙锁。我们应该尽量使用主键或唯一索引,因为唯一索引会把Next-Key Lock降级为Record Lock。

AUTO-INC Lock:

只针对存在主键的insert操作,由innodb_autoinc_lock_mode参数决定锁粒度。

在了解自增锁前需要知道mysql都有哪些insert操作:

INSERT-like

所有可以向表中增加行的语句

Simple inserts

可以预先确定要插入的行数insert...values…

Bulk inserts

事先不知道要插入的行数(INSERT…SELECT,REPLACE…SELECT,LOAD DATA)

Mixed-mode inserts

一些是“Simple inserts”语句但是有一些是null的自增值

innodb_autoinc_lock_mode= 0 传统锁定模式(所有insert采用传统AUTO-INC机制),所有“INSERT-like”语句获得一个特殊的表级AUTO-INC锁,在存在自增列的表获得一个特殊的表级AUTO-INC锁,(statement-based replication)操作是安全。

innodb_autoinc_lock_mode= 1 默认锁定模式(bulk-insert采用表级锁)

“bulk inserts”仍然使用AUTO-INC表级锁,并保持到语句结束;“Simple inserts”(要插入的行数事先已知)通过在mutex(轻量锁)的控制下获得所需数量的自动递增值来避免表级AUTO-INC锁,只在分配的时间内持有,不是整个语句,(statement-based replication)操作是安全。

innodb_autoinc_lock_mode= 2 轻量锁定模式(所有insert采用轻量级)

所有类INSERT(“INSERT-like” )语句都不会使用表级AUTO-INC lock,"批量插入"时,在由任何给定语句分配的自动递增值中可能存在间隙,(statement-based replication)操作是不安全。

可以汇总为如下表格:

示例:innodb_autoinc_lock_mode= 1时不连续

创建一个表id为自增主键:CREATE TABLE `test6` ( id int(11) NOT NULL AUTO_INCREMENT, name int(11), modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

先插入一条记录,然后再多次自插入数据,发现id没有5、10~12,如下:

这种情况就是上面锁说的,insert...select...属于Bulk insert,不能预判要插入多少条数据,所以在自增值分配上每次都会按照2^n-1分配:

第一次,先分配一个自增值,因为只有一条数据,正好

第二次,先分配一个自增值3,发现还有数据,继续按2^n-1分配,分配4、5,此时只剩一条数据4,但5已经被分配出去。

第三次,因为5已经被分配出去,此时只能从6开始,以此类推。

Dead lock:

是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象。

死锁检测开关innodb_deadlock_detect 5.7.15后引入,关闭会提升性能,一般应用在秒杀等场景。

出现死锁场景很多,绝大多数是高并发下同时操作一行数据,加锁顺序相反引起。

先删再插,两条insert当需要进行唯一性冲突检测时,需要先加一个S锁,也会产生死锁。

那么对应的解决死锁问题的关键就是:让不同的session加锁有次序。

>>关于死锁的案例可以查看:insert事务产生duplicate key error引发的死锁分析

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-next-key-locks

原文发布于微信公众号 - MYSQL轻松学(learnmysql)

原文发表时间:2017-09-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏同步博客

MySQL查询优化

     一个好的web应用,最重要的一点是有着优秀的访问性能。数据库MySQL是web应用的组成部分,也是决定其性能的重要部分。所以提升MySQL的性能至关重...

1053
来自专栏技术博文

从MyISAM转到InnoDB需要注意什么

转自 MySql中文网 http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=200910426&idx=1...

33814
来自专栏北京马哥教育

MySQL 5.7原生JSON格式支持

在MySQL与PostgreSQL的对比中,PG的JSON格式支持优势总是不断被拿来比较。其实早先MariaDB也有对非结构化的数据进行存 储的方案,称为dyn...

2806
来自专栏微信公众号:Java团长

深入理解Mysql——锁、事务与并发控制

本文对锁、事务、并发控制做一个总结,看了网上很多文章,描述非常不准确。如有与您观点不一致,欢迎有理有据的拍砖!

923
来自专栏Java3y

数据库两大神器【索引和锁】

所以说,如果我们写select * from user where username = 'Java3y'这样没有进行任何优化的sql语句,默认会这样做:

1330
来自专栏java一日一条

优化SQL查询:如何写出高性能SQL语句

执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语句如果用来从一个 10万条记录的表中查...

573
来自专栏java一日一条

优化SQL查询:如何写出高性能SQL语句

执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语句如果用来从一个 10万条记录的表中查...

671
来自专栏idba

order by 原理以及优化

一 简介 偏向于业务的(MySQL)DBA或者业务的开发者来说,order by 排序是一个常见的业务功能,将结果根据指定的字段排序,满足前端展示的需求。然而...

723
来自专栏乐沙弥的世界

Buffer cache 的调整与优化(一)

Buffer Cache是SGA的重要组成部分,主要用于缓存数据块,其大小也直接影响系统的性能。当Buffer Cache过小的时候,将会造成更多的

673
来自专栏后台及大数据开发

mysql 中select for update 锁表的范围备注

  实例:指定了锁定id=1的行且数据存在①,在更新1时lock wait超时②,但是更新id不为1的项目时可以直接更新③,释放锁后④,可以任意更新⑤

632

扫码关注云+社区