-
2021-11-14 15:00:03
事务的并发问题
- 脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据
- 不可重复读:事务A多次读取同一数据,事务B在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果不一致。
- 幻读:A事务读取了B事务已经提交的新增数据。注意和不可重复读的区别,这里是新增,不可重复读是更改(或删除)。select某记录是否存在,不存在,准备插入此记录,但执行 insert 时发现此记录已存在,无法插入,此时就发生了幻读。
MySQL如何实现避免幻读
- 在快照读读情况下,MySQL通过MVCC来避免幻读。
- 在当前读读情况下,MySQL通过next-key来避免幻读
什么是MVCC
MVCC全称是多版本并发控制的简称(multi version concurrent control)。MySQL把每个操作都定义成一个事务,每开启一个事务,系统的事务版本号自动递增。每行记录都有两个隐藏列:创建版本号和删除版本号
MVCC多版本并发控制是MySQL中基于乐观锁理论实现隔离级别的方式,用于读已提交和可重复读取隔离级别的实现。
在MySQL中,会在表中每一条数据后面添加两个字段:最近修改该行数据的事务ID,指向该行(undolog表中)回滚段的指针。
Read View判断行的可见性,创建一个新事务时,copy一份当前系统中的活跃事务列表。意思是,当前不应该被本事务看到的其他事务id列表。已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在第一次读的时候生成一个ReadView,之后的读都复用之前的ReadView。
-
select:事务每次只能读到创建版本号小于等于此次系统版本号的记录,同时行的删除版本号不存在或者大于当前事务的版本号。
-
update:插入一条新记录,并把当前系统版本号作为行记录的版本号,同时保存当前系统版本号到原有的行作为删除版本号。
-
delete:把当前系统版本号作为行记录的删除版本号
-
insert:把当前系统版本号作为行记录的版本号
什么是next-key锁
InnoDB 采用 Next-Key Lock 解决幻读问题。
insert into test(xid) values (1), (3), (5), (8), (11)
在这条语句执行后,由于xid上是有索引的,该算法总是会去锁住索引记录
现在,该索引可能被锁住的范围如下:(-∞, 1], (1, 3], (3, 5], (5, 8], (8, 11], (11, +∞)。select * from test where id = 8 for update
这条语句执行后会锁住的范围:(5, 8], (8, 11]。
除了锁住8所在的范围,还会锁住下一个范围,所谓Next-Key。更多相关内容 -
mysql MVCC不能避免幻读
2021-04-15 12:56:52幻读 并发情况下,A事务读取了一条记录,此时B事务插入一条记录,A事务又读取,读到了两条数据,此时就造成了读取数据不一致,...既然读快照信息,那么是不是MVCC就可以避免幻读,因为事务已经有了快照数据,如果这样幻读
并发情况下,A事务读取了一条记录,此时B事务插入一条记录,A事务又读取,读到了两条数据,此时就造成了读取数据不一致,一般到这幻读通常说的是事务提交了,而且是指删除、插入带来的问题。
问题
mysql MVCC 多版本并发控制中我们介绍了什么是MVCC,MVCC会为每个事务生成一个某个时间点的快照数据,保证事务内可以避免每次读数据不一致的问题,是RC、RR隔离级别下用于提高数据库性能,避免频繁锁,读写分离实现的一种方式,既然读快照信息,那么是不是MVCC就可以避免幻读,因为事务已经有了快照数据,如果这样,那为啥RR、RC 隔离级别不能避免幻读?这个问题也有很多讨论,包括知乎上也有很多答案,本文也做一些思考和讨论,参考的文档在本文下方参考博客上。
快照读:snapshot read
MVCC针对每个事务都有一个快照数据,每次都从快照数据库读取,如果事务A内有这么一个逻辑:
- if sex =‘男’ 就执行sql:
update tb_user set type = 1 where id = 8;
因为每次都是快照读,那么每次读肯定都是男,如果在事务没commit 之前,事务B 执行结束了,它把sex改为了‘女‘,此时,你的条件不满足,但你因为读取快照信息,你也无法发现问题,所以你还是执行了sql,此时此刻,似乎也没有问题,但总体来说,这是错误的。所以当事务逻辑中有update的sql时,快照读是有问题的。
当前读:locking read
当前读是读取最新数据的一种读方式,不会从快照数据里读取,这样就可以读取到其他事务已经提交的数据,是特殊的读操作,插入/更新/删除操作属于当前读,需要加锁。
- select * from table where ? lock in share mode; (加S锁)
- select * from table where ? for update;(加X锁)
- insert into table values (…);(加X锁)
- update table set ? where ?;(加X锁)
- delete from table where ?;(加X锁)
所以MVCC为了解决快照读的问题引入了当前读,innodb 引擎实际执行的是当前读,但如果使用select for update,当前读就能看到每次读的数据就不一样了,就引起了幻读,你能看到其他事务insert的数据。
总结:快照读是没有幻读存在的,当前读才会出现幻读
幻读产生
select不会有phantom,读的是快照;可对于select for update(locking read)这种语句,被认为是写,如果:
- 事务A:select读快照(non-locking read)
- 事务B:insert 一条记录
- 事务A:select for update(locking read)
幻读就产生了,事务A两次读,数据数量变了。
如何解决幻读
很明显可重复读的隔离级别没有办法彻底的解决幻读的问题,如果我们的项目中需要解决幻读的话也有两个办法:
- 使用串行化读的隔离级别
- MVCC+next-key locks:next-key locks由record locks(索引加锁) 和 gap locks(间隙锁,每次锁住的不光是需要使用的数据,还会锁住这些数据附近的数据)
参考博客
MySQL的可重复读级别能解决幻读吗
既然MySQL中InnoDB使用MVCC,为什么REPEATABLE-READ不能消除幻读?
mysql在RR的隔离级别下,究竟是通过MVCC解决幻读的还是通过行锁的next key算法解决的?
-
【MySQL】幻读是什么?如何避免幻读?
2020-08-19 23:44:27这里,需要对幻读做一个说明: 1、mysql在可重复读级别下,普通的查询(select * from t where id=5)是快照读,是不会看到别的事务插入的数据的的, 因此“幻读”在“当前读”下才会出现。 2、上面sessionB的修改...一、前言
建表语句和初始化语句如下:
CREATE TABLE `t`( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY(`id`), KEY `c`(`c`) )ENGINE=InnoDB; insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
这个表除了主键id外,还有1个索引C,初始化语句在表中插入6行数据。
下面的语句是怎么加锁的?加的锁又是什么时候释放的?
begin; select * from t where d = 5 for update; commit;
比较好理解的是,这个语句会命中d=5这一行,对应的主键id=5,因此在select语句执行之后,id=5这一行记录会加一个写锁,而且由于两阶段锁协议,这个写锁,会在执行commit语句的时候释放。
由于字段d上没有索引,因此这条查询会做全表扫描,那么其他被扫描到的,但是不满足条件的5行记录上,会不会加锁呢?
众所周知,InnoDB默认的隔离级别是可重复读,所以本文在没有特殊说明的前提下,都是设定在可重复读隔离级别下的。
二、幻读是什么?
如果只在id=5,这一行加锁,而其他行不加锁的话,会怎么样呢?首先看一下这个场景。
可以看到session A里面执行了3次查询。分别是Q1、Q2和Q3。它们的sql相同,都是select * from t where d=5 for update;这条语句的意思是查所有d=5的行,而且使用的是当前读。并且加上写锁。接下来看下这三条SQL语句,会返回什么结果?
1、Q1只返回id=5的这一行;
2、在T2时刻,sessionB把id=0这一行的d值改成了5,因此T3时刻Q2查出来的是id=0和id=5这两行。
3、 在T4时刻,sessionC又插入了一行(1,1,5)。因此T5时刻Q3查出来的是id=0、id=1和id=5这三行。
其中,在T5时刻Q3读到id=1这一行的现象,被称为“幻读”。也就是说幻读指的是一个事务在前后两次查询同一范围的时候,后一次查询看到了前一次查询没有看到的值。
这里,需要对幻读做一个说明:
1、mysql在可重复读级别下,普通的查询(select * from t where id=5)是快照读,是不会看到别的事务插入的数据的的, 因此“幻读”在“当前读”下才会出现。
2、上面sessionB的修改结果,被sessionA之后的select语句用“当前读”看到,不能成为幻读。幻读仅专指“新插入的行”。
三、幻读有什么问题?
1、首先是语义上的。sessionA在T1时刻就声明了,“我要把所有id=5的行锁住,不准别的事务进行读写操作”。而实际上,这个语义被破坏了。
2、其次是数据一致性的问题。这个数据不一致到底是怎么引入的?
四、如何解决幻读?
产生幻读的原因是,行锁只能锁住行,但是新插入记录的这个动作,要更新的是记录之间的“间隙”。因此,为了解决幻读问题,InnoDB只好引入新的锁,也就是间隙锁(Gap Lock)。
顾名思义,锁的就是两个值之间的空隙。如开头时的表t,初始化插入了6条记录。这就产生了7个间隙。
当你执行select * from t where d=5 for update的时候,就不止是给数据库中已有的6个记录加上行锁,还同时加上了7个间隙锁。这样就确保无法再插入新记录。 也就是说在一行行的扫描过程中,不仅给行加上了行锁,还给行2边的空隙加上了间隙锁。
间隙锁和行锁合称next-key lock,每个next-key lock都是前开后闭区间。也就是说,我们的表t初始化之后,如果用select * from t for update 要把整个表所有记录锁起来,就形成了7个next-key lock。分别是(-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20,25]、(25,supremum]。我们把间隙锁标记为开区间,把next-key lock标记为闭区间。
五、间隙锁导致死锁?
1、session A执行select ... for update语句,由于id=9这一行并不存在,因此会加上间隙锁(5,10)。
2、session B执行select ... for update语句,同样会加上间隙锁(5,10)。间隙锁之间不会冲突,因此这个语句可以执行成功。
3、session B试图插入一行(9,9,9),被session A的间隙锁挡住,只好进入等待状态。
4、session A试图插入一行(9,9,9,),被session B的间隙锁挡住了。
此时,两个session进入了相互等待状态,形成了死锁。当然InnoDB的死锁检测机制马上就发现了这对死锁,让sessionA的insert语句报错返回啦。间隙锁的引入,可能导致同样的语句,锁住了更大的范围。这其实是影响了并发度的。
如果将隔离级别设置为读提交,就没有间隙锁了。一些公司使用读提交+ binlog_format=row的组合。这样配置是否合理呢?配置是否合理,跟业务场景有关,需要具体问题具体分析。比如说,如果大家都用读提交,可是逻辑备份的时候,mysqldump要把备份线程设置为可重复读。然后,在备份期间,备份线程用的是可重复读,而业务线程用的是读提交。同时存在两种事务隔离级别,会不会有问题呢?
六、注释
1、两阶段锁协议:
先举个例子,在下面的操作序列中,事务B的update语句执行时会是什么现象呢?假设字段id是表t的主键。根间隙锁存在冲突关系的,是“往这个间隙中插入1个记录”这个操作,间隙锁之间都不存在冲突关系。
这个问题的结论取决于事务A在执行完两条update语句后,持有哪些锁,以及在什么时候释放。可以验证一下,实际上事务B的update语句会被阻塞,知道事务A执行Commit之后,事务B才能执行。事务A持有的两条记录的行锁,都是在commit的时候才释放的,也就是说,在InnoDB事务中,行锁在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放,这个就是两阶段锁协议。
2、当前读和快照度:
① 快照读(snapshot read):简单的select语句(不包括select ... lock in share mode(共享锁),select ... for update(排它锁))
② 当前读(current read) :
select ... lock in share mode select ... for update insert update delete
七、面试回答间隙锁的相关引申
相关知识: 间隙锁——>快照读——>行锁——>共享锁、排他锁——乐观锁、悲观锁。
解决幻读问题?
id d
1 1
5 5
10 10
select * from T where 1<=id<=5
对于当前读而言, 1、lock in share mode 2、for update
1、假如只有行锁,锁住的是id=1和id=的这两条。间隙锁的存在导致不能插入 id值为2,3,4的记录。
2、从乐观锁、悲观锁的角度而言,lock in share mode for update 都是悲观锁。乐观锁、悲观锁仅仅是一个概念。刚刚说的是mysql层面的乐观锁、悲观锁。也可以扩展到java层面的乐观锁、悲观锁。比如synchronized和CAS(compareandswap)
从另一个角度而言,lock in share mode叫作共享锁,也叫做读锁。for update 叫作排他锁。
-
【大话Mysql面试】-InnoDB可重复读隔离级别下如何避免幻读?MVCC和next-key是什么
2021-03-12 13:55:50InnoDB可重复读隔离级别下如何避免幻读 1.当前读和快照读 快照读: 最简单的select操作,属于快照读,不加锁 select * from table where id = 1; 当前读: 特殊的读与增删改操作,属于当前读,会读取数据库原本的数据...InnoDB可重复读隔离级别下如何避免幻读
1.当前读和快照读
-
快照读: 最简单的select操作,属于快照读,不加锁
select * from table where id = 1;
-
当前读: 特殊的读与增删改操作,属于当前读,会读取数据库原本的数据,加锁
select * from table where xxx lock in share mode; (共享锁) select * from table where xxx for update; insert into table values() update table set xxx where xxx delete form table where xxx
2. 四种隔离级别(由低到高)
Read Uncommitted读未提交:可以看到其它事务未提交的内容;
Read Commited读已提交:可以看到其它事务已提交的内容;
Repeatable Read可重复读:事务开始时和事务结束时读到的数据完全相同;
Serializable串行:事务必须逐步执行,后来的会排队。
3.隔离级别下的问题
前提条件:同时开启A和B两个事务;
脏读:
事务查询id为1的数据,num字段为1
B事务将id为1的数据num更新为2,但未commit
事务查询id为1的数据,num字段变为2
B回滚,则A读到的数据为脏数据不可重复读:
事务查询id为1的数据,num字段为1
B事务将id为1的数据num更新为2,commit
事务查询id为1的数据,num字段变为2
A事务前后两次读到的同一条记录num字段不同,不可重复读幻读:
A事务查询id >=1 and id <=3的数据,得到id=1和id=3两条数据(注意:没有id=2的数据)
B事务插入id=2的数据
A事务再查询id >=1 and id <=3的数据,发现多了一条id=2的数据,即两次查询数据的行数不同各种隔离模式下会出现的问题
读未提交:脏读、不可重复读、幻读;
读已提交:不可重复读、幻读;
可重复读:幻读;
4.next-key
4.1 间隙锁(gap-key)
在一个事务中select for update查询某条记录,会锁定该条记录的前后空行,什么叫空行呢,看个例子就知道了
id num
1 2
2 4
3 6
4 7
执行select * from table where num = 4 for updateinsert into table (’’, 3)和insert into table (’’, 5)都会被阻塞,即4前后的空行都无法插入
4.2 next-key
由于gay-key只能锁定记录之间的间隙,但是我们上面查询num=4的行也不能被更改,索引该行也会被加行锁,此时这种既能加行锁,又能加间隙锁的,称之为next-key。
5. MVCC
事务A:查询num>=2 and num<=4的记录,但是不加for update!不加for update!不加for update!
事务B:insert into table (’’, 3),不会被阻塞,不会被阻塞,不会被阻塞!然后commit
事务A:查询num>=2 and num<=4的记录,和之前查询相同
事务A:提交
事务A:查询num>=2 and num<=4的记录,可以查询到num=3的记录,此时返回3条结果
原理:假如事务A在开启的时候版本号为2,当更改或者插入数据后,该条记录的版本号+1,也就是说事务B插入num=3的记录的版本号为3,事务A在提交前的所有select都只能查询到版本号<=2的记录,也就是说不会产生上面说的幻读。6.总结
MVCC和next-key
其实,innodb采用next-key + MVCC去解决幻读问题的:
在查询加for update时,会用next-key解决幻读问题,新的insert和update会阻塞
在查询不加for update时,会用MVCC解决幻读问题,新的insert和update不会阻塞 -
-
数据库之InnoDB可重复读隔离级别下如何避免幻读
2020-04-24 22:01:15文章目录一、先介绍几个概念1、什么是当前读2、什么是快照读3、什么是mvcc二、RR级别下避免幻读的方法三、RC级别下测试快照读和当前读3.1、测试快照读3.2、测试当前读四、RR级别下测试快照读和当前读五、RC、RR级别... -
深入学习InnoDB可重复读隔离级别下如何避免幻读
2019-10-22 23:04:11一、InnoDB可重复读隔离级别下如何避免幻读 在理解什么是幻读之前,先了解下脏读、幻读、不可重复读在实操场景中的现象。 脏读:指的就是一个事务读取到了另一个事务还未提交的数据,当该事物将数据回滚,则读取到的... -
MySQL 可重复读隔离级别(RR级别)是否可以避免幻读
2022-03-16 19:07:04发现在客户端B中插入的数据显示出来了,说明客户端A在事务执行期间,客户端B对该表的操作并不会对客户端A有影响,避免了幻读。出现这种情况的主要原因是 MySQL 的存储引擎通过多版本并发控制 MVCC 机制解决 -
避免幻读 : next-key锁与MVCC
2020-03-13 23:14:44在隔离级别为RR时,MySQL已经可以避免脏读和重复读,但还是无法避免幻读,MySQL采用next-key锁与MVCC(多版本并发控制)来避免幻读. next-key锁 next-key 锁是索引 record 上的 record 锁和 index record 之前的间隙上的... -
mysql innodb可重复读隔离级别下是如何实现避免幻读的
2020-12-24 16:15:14表象:快照读(非阻塞读)–伪MVCC 内在:next-key锁(行锁+gap锁) 文章目录快照读和当前读next-key lock案例分析情况1:where条件全部命中,则不会用Gap锁,只会加记录锁情况2:where条件部分命中或者全不命中,则... -
InnoDB可重复读隔离级别下如何避免幻读
2020-05-23 12:15:14InnoDB可重复读隔离级别下如何避免幻读 主要通过以下两种情况避免幻读 表象:快照读(非阻塞读)伪MVCC 表象避免幻读,是RR下查找数据,第一次读取创建快照,后面读取都是读取本次快照,不论别的事务是否提交相关... -
剑指Offer(sql)——InnoDB可重复读隔离级别下如何避免幻读
2019-11-22 15:04:091. 表象:快照读(非阻塞读)——伪MVCC 读取数据,也是有规范的,当前读和快照读。 当前读: 加了锁的增删改查事务。因为他读取的实际上就是最新的版本,并且读取之后,还不允许其他事务修改自己查询的结果。也... -
InnoDB可重复读隔离级别下如何避免幻读?
2020-02-22 18:25:04InnoDB可重复读隔离级别下如何避免幻读? 表象:快照读(非阻塞读)—伪MVCC 内在:next-key锁,(行锁+gap锁) 当前读:加锁的增删改查语句,无论什么锁,因为读取的是当前最新版本,还要保证并发事务不能修改当前... -
MySQL是如何解决幻读的
2020-12-14 18:46:29一、什么是幻读 在一次事务里面,多次查询之后,结果集的个数不一致的情况叫做幻读。而多出来或者少的哪一行被叫做 幻行 二、为什么要解决幻读 在高并发数据库系统中,需要保证事务与事务之间的隔离性,还有事务本身... -
InnoDB如何在RR隔离级别下实现避免幻读的?
2020-09-24 08:16:32InnoDB引擎,RR隔离级别下,并不能完全组织幻读的发生,想避免幻读,要保证gap锁是开启的,也就是innodb_locks_unsafe_for_binlog参数值为off(默认情况是off) -
Mysql(Innodb)如何避免幻读
2017-08-12 19:22:00幻读Phantom Rows 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 ... -
mysql Innodb在RR级别如何避免幻读
2020-03-04 09:34:12什么是幻读 事务不是独立执行时发生的一种现象,例如第一个事务对一个表中的数据进行了修改,这种修改涉及到表...mysql如何实现避免幻读 在快照读读情况下,mysql通过mvcc来避免幻读。 在当前读读情况下,mysql通过... -
innodb当前读 与 快照读 and rr级别是否真正避免了幻读
2021-01-27 04:43:57该技术不仅可以保证innodb的可重复读,而且可以防止幻读。(这也就是是此前以rr隔离级别实践时,不仅可以防止可重复读,也防止了欢度)但是它防止的是快照读,也就是读取的数据虽然是一致的,但是数据是历史数据。... -
mysql-repeatable read可以避免幻读
2018-08-04 00:36:541、repeatable read 允许幻读,这是ANSI/ISO SQL标准的定义要求,运行幻读依然有非常大的隐患,mysql 在repeatable read 即可满足没有幻读的要求。 2、不可重复读和幻读的区别:不可重复读的重点是修改,幻读的... -
【mysql学习笔记】InnoDB的RR级别如何避免幻读
2019-04-24 10:17:17RR级别(REPEATABLE-READ隔离级别)引入【next-key lock】避免幻读 next-key lock组成 record lock(记录锁) gap lock(间隙锁) gap lock应用场景 非唯一索引当前读 不走索引的当前读 仅命中部分结果的结果集当前读... -
【数据库】MySQL Innodb在RR(可重复读)级别如何避免幻读
2021-05-24 21:56:02什么是幻读 事务不是独立执行时发生的一种...mysql如何实现避免幻读 在快照读读情况下,mysql通过mvcc来避免幻读。 在当前读读情况下,mysql通过next-key来避免幻读 什么是MVCC MVCC 全称是multi version concurrent c -
MySQL到底是怎么解决幻读的?
2021-01-19 11:35:56一、什么是幻读在一次事务里面,多次查询之后,结果集的个数不一致的情况叫做幻读。而多出来或者少的哪一行被叫做幻行。二、为什么要解决幻读在高并发数据库系统中,需要保证事务与事务之间的隔离性,还有事务本身的... -
MVCC能否解决幻读
2021-02-02 04:53:24Mysql(Innodb)如何避免幻读幻读Phantom RowsThe 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 ... -
mysql如何防止幻读
2022-03-11 13:52:53前言 以下内容是作者在网上搜集和自己总结...脏读指的是读到了其他事务未提交的数据,未提交意味着这些数据可能会回滚,也就是可能最终不会存到数据库中,也就是不存在的数据。读到了并一定最终存在的数据,这就是脏读 -
高并发操作下的避免幻读
2020-05-19 17:14:14高并发操作下的避免幻读 既有读,又有修改的两个事务并行操作, 需要加锁(synchronized)— 事务只能一个执行完再执行下一个,避免幻读,否则第二个事务读到的数据是旧的数据,修改后会让第一个事务的修改操作失效 ... -
透彻解读mysql的可重复读、幻读及实现原理
2021-02-02 04:03:40目录一、事务的隔离级别二、mysql怎么实现的可重复读举例说明MVCC的实现MVCC逻辑流程-插入MVCC逻辑流程-删除MVCC逻辑流程-修改MVCC逻辑流程-查询三、幻读快照读和当前读四、如何解决幻读事务隔离级别有四种,mysql... -
探究Mysql可重复读隔离级别的实现以及该级别下如何避免幻读
2020-10-25 00:29:25当前读和快照读3.undo log二、RR级别下innodb避免幻读的方法。1.表象(快照读)1.内在,行锁+gap锁总结 前言 在学完数据库的索引和锁的一些知识后,今天我们来探讨下Mysql的innodb存储引擎在可重复读隔离级别下是... -
Mysql如何解决幻读:
2021-07-17 08:52:20在一次事务里面,多次查询之后,结果集的个数不一致的情况叫做幻读。而多或者少的那一行被叫做幻行,也就是说当一个事务在进行读取数据的时候,其他事务对该数据进行了改变。在高并发数据库系统中,需要保证事务与...