精华内容
下载资源
问答
  • MySQL如何避免幻读
    千次阅读
    2021-11-14 15:00:03

    事务的并发问题

    1. 脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据
    2. 不可重复读:事务A多次读取同一数据,事务B在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果不一致。
    3. 幻读: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算法解决的?

    展开全文
  • 这里,需要对幻读做一个说明: 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 叫作排他锁。

         

          

     

    展开全文
  • InnoDB可重复读隔离级别下如何避免幻读 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 update

    insert 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不会阻塞

    展开全文
  • 文章目录一、先介绍几个概念1、什么是当前读2、什么是快照读3、什么是mvcc二、RR级别下避免幻读的方法三、RC级别下测试快照读和当前读3.1、测试快照读3.2、测试当前读四、RR级别下测试快照读和当前读五、RC、RR级别...
  • 一、InnoDB可重复读隔离级别下如何避免幻读 在理解什么是幻读之前,先了解下脏读、幻读、不可重复读在实操场景中的现象。 脏读:指的就是一个事务读取到了另一个事务还未提交的数据,当该事物将数据回滚,则读取到的...
  • 发现在客户端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 之前的间隙上的...
  • 表象:快照(非阻塞)–伪MVCC 内在:next-key锁(行锁+gap锁) 文章目录快照和当前next-key lock案例分析情况1:where条件全部命中,则不会用Gap锁,只会加记录锁情况2:where条件部分命中或者全不命中,则...
  • InnoDB可重复读隔离级别下如何避免幻读 主要通过以下两种情况避免幻读 表象:快照读(非阻塞读)伪MVCC 表象避免幻读,是RR下查找数据,第一次读取创建快照,后面读取都是读取本次快照,不论别的事务是否提交相关...
  • 1. 表象:快照(非阻塞)——伪MVCC 读取数据,也是有规范的,当前和快照。 当前: 加了锁的增删改查事务。因为他读取的实际上就是最新的版本,并且读取之后,还不允许其他事务修改自己查询的结果。也...
  • InnoDB可重复读隔离级别下如何避免幻读? 表象:快照读(非阻塞读)—伪MVCC 内在:next-key锁,(行锁+gap锁) 当前读:加锁的增删改查语句,无论什么锁,因为读取的是当前最新版本,还要保证并发事务不能修改当前...
  • 一、什么是幻读 在一次事务里面,多次查询之后,结果集的个数不一致的情况叫做幻读。而多出来或者少的哪一行被叫做 幻行 二、为什么要解决幻读 在高并发数据库系统中,需要保证事务与事务之间的隔离性,还有事务本身...
  • InnoDB引擎,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如何实现避免幻读 在快照读读情况下,mysql通过mvcc来避免幻读。 在当前读读情况下,mysql通过...
  • 该技术不仅可以保证innodb的可重复读,而且可以防止幻读。(这也就是是此前以rr隔离级别实践时,不仅可以防止可重复读,也防止了欢度)但是它防止的是快照读,也就是读取的数据虽然是一致的,但是数据是历史数据。...
  • mysql-repeatable read可以避免幻读

    千次阅读 2018-08-04 00:36:54
    1、repeatable read 允许幻读,这是ANSI/ISO SQL标准的定义要求,运行幻读依然有非常大的隐患,mysql 在repeatable read 即可满足没有幻读的要求。 2、不可重复读和幻读的区别:不可重复读的重点是修改,幻读的...
  • RR级别(REPEATABLE-READ隔离级别)引入【next-key lock】避免幻读 next-key lock组成 record lock(记录锁) gap lock(间隙锁) gap lock应用场景 非唯一索引当前读 不走索引的当前读 仅命中部分结果的结果集当前读...
  • 什么是幻读 事务不是独立执行时发生的一种...mysql如何实现避免幻读 在快照读读情况下,mysql通过mvcc来避免幻读。 在当前读读情况下,mysql通过next-key来避免幻读 什么是MVCC MVCC 全称是multi version concurrent c
  • 一、什么是幻读在一次事务里面,多次查询之后,结果集的个数不一致的情况叫做幻读。而多出来或者少的哪一行被叫做幻行。二、为什么要解决幻读在高并发数据库系统中,需要保证事务与事务之间的隔离性,还有事务本身的...
  • MVCC能否解决幻读

    2021-02-02 04:53:24
    Mysql(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
    前言 以下内容是作者在网上搜集和自己总结...脏指的是到了其他事务未提交的数据,未提交意味着这些数据可能会回滚,也就是可能最终不会存到数据库中,也就是不存在的数据。到了并一定最终存在的数据,这就是脏
  • 高并发操作下的避免幻读 既有读,又有修改的两个事务并行操作, 需要加锁(synchronized)— 事务只能一个执行完再执行下一个,避免幻读,否则第二个事务读到的数据是旧的数据,修改后会让第一个事务的修改操作失效 ...
  • 目录一、事务的隔离级别二、mysql怎么实现的可重复读举例说明MVCC的实现MVCC逻辑流程-插入MVCC逻辑流程-删除MVCC逻辑流程-修改MVCC逻辑流程-查询三、幻读快照读和当前读四、如何解决幻读事务隔离级别有四种,mysql...
  • 当前读和快照读3.undo log二、RR级别下innodb避免幻读的方法。1.表象(快照读)1.内在,行锁+gap锁总结 前言 在学完数据库的索引和锁的一些知识后,今天我们来探讨下Mysql的innodb存储引擎在可重复读隔离级别下是...
  • Mysql如何解决幻读

    千次阅读 2021-07-17 08:52:20
    在一次事务里面,多次查询之后,结果集的个数不一致的情况叫做幻读。而多或者少的那一行被叫做幻行,也就是说当一个事务在进行读取数据的时候,其他事务对该数据进行了改变。在高并发数据库系统中,需要保证事务与...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 40,717
精华内容 16,286
关键字:

怎么避免幻读