精华内容
下载资源
问答
  • 2021-01-27 05:44:02

    1.1 实现InnoDB下的快照读

    然后,接下来说说,在READ-COMMITTED和REPEATABLE-READ级别下的InnoDB的非阻塞读是如何实现的。

    实际上,在InnoDB存储数据的时候,还会额外存储三个不显示出来的字段:DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID,下面来简单介绍一下字段的含义。

    DB_TRX_ID:最后一次修改本行记录的事务ID。

    DB_ROLL_PTR:滚指针,指向这条记录的上一个版本(存储于rollback segment里)。

    DB_ROW_ID:隐含的自增ID,如果数据表没有主键,InnoDB会自动以DB_ROW_ID产生一个聚簇索引。

    然后再额外解释一下什么是undo日志:

    undo日志:逻辑日志,其记录时间点为修改缓冲中页面之前

    2. 内在:next-key锁(行锁+gap锁)

    行锁

    所谓行锁,就是对行上锁

    Gap锁

    Gap:索引树中,插入新纪录的空隙

    Gap锁:指一段距离,将插入的索引占用的空隙用锁包住。Gap锁的目的是,防止事务因为两次当前读出现幻读的情况。但是READ-COMMITTED及以下的级别都没有Gap锁,所以无法避免幻读。

    而在REPEATABLE-READ级别下,无论是删改查,如果我们要使用到主键索引或者唯一索引,会需要到gap锁吗?

    其实是视情况而定的,如果where条件全部命中,就可以不用gap锁,否则,就得用gap锁。(命中:比如我们where id in (1,3,5),id是主键,1 3 5在table中都能查到东西,就是全部命中,只查到一部分,就是部分命中)

    更多相关内容
  • gap lock 可以通过设置隔离级别为已提交或者将innodb_locks_unsafe_for_binlog = 1取消,但是从隔离性,性能,以及主从数据一致等方面都不建议这样做。 insert的特殊情况 对于insert 会检查下一条记录是否被锁定...

    接上篇

    Record lock

    Gap lock

    Next-Key lock

    record lock 按照索引记录加锁,如果没有则采用隐式的主键来锁定。

    next-key 结合了Gap lock 和 record lock ,例如索引值:10,11,13,20。则按next-key 锁定的区间为(-∞,10],(10,11]

    ,(11,13],(13,20],(20,+∞)

    注意此处指非唯一索引,如果是唯一索引,会降级为Record lock ,仅仅锁住索引本身,而非范围。同时这种降级只发生在查询所有索引列的情况下,如果存在联合索引且只是查询联合索引的某一列,即属于range类型查询,仍是采用Next-key锁定,不会降级。

    举个存在唯一索引和辅助索引的例子做说明:

    create table test ( a int , b int , primary key (a), key(b));

    insert into test select 1, 1;

    insert into test select 3, 1;

    insert into test select 5, 3;

    insert into test select 7, 6;

    insert into test select 10,8;

    执行 select * from test where b = 3 for update

    存在两个索引,分别加锁,唯一主键列a加record lock , 辅助索引列b加next-key lock (1,3) 以及给下一个值的区间(3,6)加gap锁;

    因此在另一个事务里执行以下语句都会阻塞,具体分析:

    select * from test where a = 5 lock in share mode;

    insert into test select 4,2;

    insert into test select 6,5;

    第一个阻塞因为加了唯一索引的record lock a = 5;

    第二个主键插入4,符合条件,但是根据辅助索引b 的范围, b = 2 在(1,3)中,同样阻塞;

    第三个a =6 不在主键a锁定范围,b = 5 也不在辅助索引b 的范围(1,3)中,但在另一个gap锁范围(3,6)中,因此也阻塞;

    这种锁定情形下,可以执行的包括类似语句:

    insert into test select 8,6;

    insert into test select 2,0;

    gap lock 可以通过设置隔离级别为读已提交或者将innodb_locks_unsafe_for_binlog = 1取消,但是从隔离性,性能,以及主从数据一致等方面都不建议这样做。

    insert的特殊情况

    对于insert 会检查下一条记录是否被锁定,如上述例子有select * from test where b = 3 for update插入insert into test select 2,2会检测到b = 3 已经被锁定,而insert into test select 2,0可以执行;

    [1]:《MySQL技术内幕:InnoDB存储引擎》-第六章:锁

    展开全文
  • 发现在客户端B中插入的数据显示出来了,说明客户端A在事务执行期间,客户端B对该表的操作并不会对客户端A有影响,避免幻读。出现这种情况的主要原因是 MySQL 的存储引擎通过多版本并发控制 MVCC 机制解决

    在MySQL的A客户端中查看事务隔离级别(默认是RR级别)
    在这里插入图片描述
    在客户端A开启事务,并查看trax_learn表。
    在这里插入图片描述
    新开一个客户端B,在trax_learn表中添加一条新数据
    在这里插入图片描述
    再次在客户端A中查看trax_learn表,发现,仍然是只有两条记录的。
    在这里插入图片描述
    在客户端A提交事务。commit,并再次查看。
    在这里插入图片描述
    发现在客户端B中插入的数据显示出来了,说明客户端A在事务执行期间,客户端B对该表的操作并不会对客户端A有影响,避免了幻读。
    出现这种情况的主要原因是因为普通的select语句是快照读,而事务A启动时,它的快照数据就已经被版本锁定了。 如果将事务A调整为当前读,就会产生幻读。

    所以说InnoDB的RR隔离级别解决了幻读问题或没解决都不太准确。应该说它并没有完全解决幻读的问题。

    展开全文
  • 但是对于幻读,我发现在可重复读的隔离级别下没有出现,当时想到难道是MySQL幻读做了什么处理? 测试: 创建一张测试用的表dept: CREATE TABLE `dept` ( `id` int(11) NOT NULL AUTO_IN

    cnblogs.com/liyus/p/10556563.html

    引言

    之前在深入了解数据库理论的时候,了解到事物的不同隔离级别可能存在的问题。为了更好的理解所以在MySQL数据库中测试复现这些问题。关于脏读和不可重复读在相应的隔离级别下都很容易的复现了。但是对于幻读,我发现在可重复读的隔离级别下没有出现,当时想到难道是MySQL对幻读做了什么处理?

    测试:

    创建一张测试用的表dept:

    CREATE TABLE `dept` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
    
    insert into dept(name) values("后勤部")
    
    事物 1事物 2
    beginbegin
    select * from dept
    -insert into dept(name) values("研发部")
    -commit
    select * from dept
    commit

    根据上面的流程执行,预期来说应该是事物1的第一条select查询出一条数据,第二个select查询出两条数据(包含事物2提交的数据)。

    但是在实际测试中发现第二条select实际上也只查询处理一条数据。这是但是根据数据库理论的可重复读的实现(排他锁和共享锁)这是不应该的情况。

    在了解实际原因前我们先复习下事物的相关理论。

    数据库原理理论

    事物

    事务(Transaction),一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。在关系数据库中,一个事务可以是一组SQL语句或整个程序。

    为什么要有事物

    一个数据库事务通常包含对数据库进行读或写的一个操作序列。它的存在包含有以下两个目的:

    1. 为数据库操作提供了一个从失败中恢复到正常状态的方法,同时提供了数据库在异常状态下仍能保持一致性的方法。

    2. 当多个应用程序在并发访问数据库时,可以在这些应用程序之间提供一个隔离方法,保证彼此的操作互相干扰。

    事物特性

    事务具有4个特性:原子性、一致性、隔离性、持久性。这四个属性通常称为 ACID 特性。

    • 原子性(atomicity):
      一个事务应该是一个不可分割的工作单位,事务中包括的操作要么都成功,要么都不成功。

    • 一致性(consistency):
      事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

    • 隔离性(isolation):
      一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据在事物未提交前对并发的其他事务是隔离的,并发执行的各个事务之间不能互相影响。

    • 持久性(durability):
      一个事务一旦成功提交,它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

    事物之间的几个特性并不是一组同等的概念:

    如果在任何时刻都只有一个事物,那么其天然是具有隔离性的,这时只要保证原子性就能具有一致性。

    如果存在并发的情况下,就需要保证原子性和隔离性才能保证一致性。

    数据库并发事物中存在的问题

    如果不考虑事务的隔离性,会发生以下几种问题:

    • 脏读:脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。当一个事务正在多次修改某个数据,而在这个事务中这多次的修改都还未提交,这时一个并发的事务来访问该数据,就会造成两个事务得到的数据不一致。

    • 不可重复读:不可重复读是指在对于数据库中的某条数据,一个事务范围内多次查询返回不同的数据值(这里不同是指某一条或多条数据的内容前后不一致,但数据条数相同),这是由于在查询间隔,该事物需要用到的数据被另一个事务修改并提交了。不可重复读和脏读的区别是,脏读是某一事务读取了另一个事务未提交的脏数据,而不可重复读则是读取了其他事务提交的数据。需要注意的是在某些情况下不可重复读并不是问题。

    • 幻读:幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作,这时事务T2又对这个表中插入了一行数据项,而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据,会发现还有一行没有修改,其实这行是从事务T2中添加的,就好像产生幻觉一样,这就是发生了幻读。幻读和不可重复读都是读取了另一条已经提交的事务(这点就脏读不同),所不同的是不可重复读可能发生在update,delete操作中,而幻读发生在insert操作中。

    排他锁,共享锁

    排它锁(Exclusive),又称为X 锁,写锁。

    共享锁(Shared),又称为S 锁,读锁。

    读写锁之间有以下的关系:

    • 一个事务对数据对象O加了 S 锁,可以对 O进行读取操作,但是不能进行更新操作。加锁期间其它事务能对O 加 S 锁,但是不能加 X 锁。
    • 一个事务对数据对象 O 加了 X 锁,就可以对 O 进行读取和更新。加锁期间其它事务不能对 O 加任何锁。

    即读写锁之间的关系可以概括为:多读单写

    事物的隔离级别

    在事物中存在以下几种隔离级别:

    • 读未提交(Read Uncommitted):解决更新丢失问题。如果一个事务已经开始写操作,那么其他事务则不允许同时进行写操作,但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现,即事物需要对某些数据进行修改必须对这些数据加 X 锁,读数据不需要加 S 锁。

    • 读已提交(Read Committed):解决了脏读问题。读取数据的事务允许其他事务继续访问该行数据,但是未提交的写事务将会禁止其他事务访问该行。这可以通过“瞬间共享读锁”和“排他写锁”实现, 即事物需要对某些数据进行修改必须对这些数据加 X 锁,读数据时需要加上 S 锁,当数据读取完成后立刻释放 S 锁,不用等到事物结束。

    • 可重复读取(Repeatable Read):禁止不可重复读取和脏读取,但是有时可能出现幻读数据。读取数据的事务将会禁止写事务(但允许读事务),写事务则禁止任何其他事务。Mysql默认使用该隔离级别。这可以通过“共享读锁”和“排他写锁”实现,即事物需要对某些数据进行修改必须对这些数据加 X 锁,读数据时需要加上 S 锁,当数据读取完成并不立刻释放 S 锁,而是等到事物结束后再释放。

    • 串行化(Serializable):解决了幻读的问题的。提供严格的事务隔离。它要求事务序列化执行,事务只能一个接着一个地执行,不能并发执行。仅仅通过“行级锁”是无法实现事务序列化的,必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

    MySQL中的隔离级别的实现

    上面的内容解释了一些数据库理论的概念,但是在MySQL、ORACLE这样的数据库中,为了性能的考虑并不是完全按照上面介绍的理论来实现的。

    MVCC

    多版本并发控制(Multi-Version Concurrency Control, MVCC)是MySQL中基于乐观锁理论实现隔离级别的方式,用于实现读已提交和可重复读取隔离级别的实现。

    实现(隔离级别为可重复读)

    在说到如何实现前先引入两个概念:

    系统版本号:一个递增的数字,每开始一个新的事务,系统版本号就会自动递增。

    事务版本号:事务开始时的系统版本号。

    在MySQL中,会在表中每一条数据后面添加两个字段:

    创建版本号:创建一行数据时,将当前系统版本号作为创建版本号赋值

    删除版本号:删除一行数据时,将当前系统版本号作为删除版本号赋值

    SELECT

    select时读取数据的规则为:创建版本号<=当前事务版本号,删除版本号为空或>当前事务版本号。

    创建版本号<=当前事务版本号保证取出的数据不会有后启动的事物中创建的数据。这也是为什么在开始的示例中我们不会查出后来添加的数据的原因

    删除版本号为空或>当前事务版本号保证了至少在该事物开启之前数据没有被删除,是应该被查出来的数据。

    INSERT

    insert时将当前的系统版本号赋值给创建版本号字段。

    UPDATE

    插入一条新纪录,保存当前事务版本号为行创建版本号,同时保存当前事务版本号到原来删除的行,实际上这里的更新是通过delete和insert实现的。

    DELETE

    删除时将当前的系统版本号赋值给删除版本号字段,标识该行数据在那一个事物中会被删除,即使实际上在位commit时该数据没有被删除。根据select的规则后开启懂数据也不会查询到该数据。

    MVCC真的解决了幻读?

    从最开始我们的测试示例和上面的理论支持来看貌似在MySQL中通过MVCC就解决了幻读的问题,那既然这样串行化读貌似就没啥意义了,带着疑问继续测试。

    测试前数据:

    测试前数据

    事物 1事物 2
    beginbegin
    select * from dept
    -insert into dept(name) values("研发部")
    -commit
    update dept set name="财务部"(工作中如果不想被辞退一定要写where条件)
    commit

    根据上面的结果我们期望的结果是这样的:

    id  name
    1   财务部
    2   研发部
    

    但是实际上我们的经过是:

    测试后数据

    本来我们希望得到的结果只是第一条数据的部门改为财务,但是结果确实两条数据都被修改了。这种结果告诉我们其实在MySQL可重复读的隔离级别中并不是完全解决了幻读的问题,而是解决了读数据情况下的幻读问题。而对于修改的操作依旧存在幻读问题,就是说MVCC对于幻读的解决时不彻底的。

    快照读和当前读

    出现了上面的情况我们需要知道为什么会出现这种情况。在查阅了一些资料后发现在RR级别中,通过MVCC机制,虽然让数据变得可重复读,但我们读到的数据可能是历史数据,不是数据库最新的数据。这种读取历史数据的方式,我们叫它快照读 (snapshot read),而读取数据库最新版本数据的方式,叫当前读 (current read)。

    select 快照读

    当执行select操作是innodb默认会执行快照读,会记录下这次select后的结果,之后select 的时候就会返回这次快照的数据,即使其他事务提交了不会影响当前select的数据,这就实现了可重复读了。快照的生成当在第一次执行select的时候,也就是说假设当A开启了事务,然后没有执行任何操作,这时候B insert了一条数据然后commit,这时候A执行 select,那么返回的数据中就会有B添加的那条数据。之后无论再有其他事务commit都没有关系,因为快照已经生成了,后面的select都是根据快照来的。

    当前读

    对于会对数据修改的操作(update、insert、delete)都是采用当前读的模式。在执行这几个操作时会读取最新的记录,即使是别的事务提交的数据也可以查询到。假设要update一条记录,但是在另一个事务中已经delete掉这条数据并且commit了,如果update就会产生冲突,所以在update的时候需要知道最新的数据。也正是因为这样所以才导致上面我们测试的那种情况。

    select的当前读需要手动的加锁:

    select * from table where ? lock in share mode;
    select * from table where ? for update;
    

    有个问题说明下

    在测试过程中最开始我以为使用begin语句就是开始一个事物了,所以在上面第二次测试中因为先开始的事物1,结果在事物1中却查到了事物2新增的数据,当时认为这和前面MVCC中的select的规则不一致了,所以做了如下测试:

    image

    SELECT * FROM information_schema.INNODB_TRX //用于查询当前正在执行中的事物
    

    可以看到如果只是执行begin语句实际上并没有开启一个事物。

    下面在begin后添加一条select语句:

    事物2

    所以要明白实际上是对数据进行了增删改查等操作后才开启了一个事物。

    如何解决幻读

    很明显可重复读的隔离级别没有办法彻底的解决幻读的问题,如果我们的项目中需要解决幻读的话也有两个办法:

    • 使用串行化读的隔离级别
    • MVCC+next-key locks:next-key locks由record locks(索引加锁) 和 gap locks(间隙锁,每次锁住的不光是需要使用的数据,还会锁住这些数据附近的数据)

    实际上很多的项目中是不会使用到上面的两种方法的,串行化读的性能太差,而且其实幻读很多时候是我们完全可以接受的。

    展开全文
  • MySQL 可重复读隔离级别与幻读

    千次阅读 2022-03-16 17:40:02
    MySQL可重复读的隔离级别下,能很大程度上避免幻读,而不能完全避免。 场景复现 环境信息: MySQL版本:5.7.23-log 隔离级别:REPEATABLE-READ 测试数据: SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; --...
  • 透彻解读mysql可重复读、幻读及实现原理

    万次阅读 多人点赞 2019-05-15 15:21:30
    目录 一、事务的隔离级别 二、mysql怎么实现的可重复读 ...事务隔离级别有四种,mysql默认使用的是可重复读,mysql是怎么实现可重复读的?为什么会出现幻读?是否解决了幻读的问题? 一、事务的隔离级别 Re...
  • 但是对于幻读,我发现在可重复读的隔离级别下没有出现,当时想到难道是MySQL幻读做了什么处理? 测试: 创建一张测试用的表dept: ​​CREATE​​​ ​​TABLE​​​ ​​`dept` (​​ ​​`id` ​​​​int...
  • mysql的innodb引擎是通过 "行排他锁+MVCC" 一起实现的, 不仅可以保证可重复读, 还可以部分防止幻读, 而非完全防止;为啥说并没有完全杜绝呢?下面的情况就是mysql通过next-key锁机制杜绝了一些幻读。操作...
  • 可重复读 同一事务中,同一查询条件多次查询,数据一致 串行化 在一个事务中访问的行加锁(读写锁),其它事务出现锁冲突,阻塞等待前面事务执行完成提交后,再继续执行 mysql innodb引擎默认事务隔离级别 可重复读...
  • 在查阅了一些资料后发现在RR级别中,通过MVCC机制,虽然让数据变得可重复读,但我们到的数据可能是历史数据,不是数据库最新的数据。这种读取历史数据的方式,我们叫它快照 (snapshot read),而读取数据库最新...
  • 因此 Mysql 在Repeatable下面 幻读可重复读,脏读 三者都不会发生 read committed隔离级别:采用Record锁,不会出现脏读,但是会产生"幻读"问题. 也会出现可重复读。 什么是MVCC 呢? MVCC只是工
  • 当然,从总的结果来看,似乎两者都表现为两次读取的结果不一致.但如果你从控制的角度来看,两者的区别就比较大对于前者,只需要锁住满足条件的记录对于后者,要锁...避免可重复读需要锁行就行避免幻影则需要锁表-----...
  • MySQL如何解决幻读和不可重复度

    千次阅读 2021-04-23 19:58:05
    注意:探讨MySQL如何防止不可重复度幻读问题之前,默认大家已经理解脏读、幻读、不重复读的区别,以及数据库事务的3种隔离级别! 1. MySQL中的3种锁算法 首先了解下MySQL中的3种锁算法: .
  • 其中,可重复读这个隔离级别,有效地防止了脏读和不可重复读,但仍然可能发生幻读,可能发生幻读就表示可重复读这个隔离级别防不住幻读吗?我不管从数据库方面的教科书还是一些网络教程上,经常看到RR级别是可以重复读的,...
  • 幻读:delete,insert. MySQL中的四种事务隔离级别 未提交读 未提交读(READ UNCOMMITTED)是最低的隔离级别,在这种隔离级别下,如果一个事务已经开始写数据,则另外一个事务则不允许同时进行写操作,但允许其他事务读...
  • MySQL可重复读隔离级别为何没有解决幻读(MVCC原理简介) 一.MCVV简介 二.可重复读隔离级别能解决幻读? 三.什么是当前读和快照读? 四.MVCC的实现原理 五.RC,RR级别下的InnoDB快照读有什么不同? 六.如何解决幻读 ...
  • 想了解数据库隔离级别,MVCC,next-key lock,gap lock,可重复度到底能不能防止幻读,我通过在 mysql 8.0 上进行了 5 种现象的实验,给你结论
  • 面试官:MySQL可重复读级别能解决幻读问题吗? 引言 之前在深入了解数据库理论的时候,了解到事务的不同隔离级别可能存在的问题。为了更好的理解所以在MySQL数据库中测试复现这些问题。关于脏读和不可重复读在相应...
  • MVCC解决可重复读的幻读了吗?
  • 事务隔离级别有四种,mysql默认使用的是可重复读,mysql是怎么实现可重复读的?为什么会出现幻读?是否解决了幻读的问题? 一、事务的隔离级别 Read Uncommitted(未提交读) 在该隔离级别,所有事务都可以看到...
  • 幻读(phantom read)幻读是一次事务中前后数据量发生变化,用户产生不预料的问题4. 总结5. 解决方法 1. 脏读(dirty read) 脏读是指事务读取到其他事务未提交的数据 例如:有事务A、B和一条记录:id为1,name为张三...
  • 1. 事务的隔离级别 1.1 read uncommited:未提交。一个事务到了另一个事务未提交的脏数据,称之为脏。 1.2 read commited:
  • MySQL命令行的默认设置下,事务都是自动提交(auto commit)的,即执行SQL语句后就会马上执行COMMIT操作 因此要显式地开启一个事务需使用命令BEGIN、START TRANSACTION,或者执行命令SET AUTOCOMMIT=0,禁用当前...
  • 作者:sanyuesan0000https://blog.csdn.net/sanyuesan0000事务隔离级别有四种,mysql默认使用的是可重复读mysql是怎么实现可重复读的?为...
  • 可重复幻读的危害复现和MySQL的解决方案) ## 前言 事务的隔离级别分为**读未提交RU**、**读已提交RC**、**可重复读取RR**、**序列化**。概念不必多说,随处搜,而危害很难搜到文章描述。主要原因还是主流...
  • mysql如何防止幻读

    千次阅读 2022-03-11 13:52:53
    MySQL 事务都是指在 InnoDB 引擎下,MyISAM 引擎是不支持事务的。 事务具有原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)四个特性,简称 ACID,缺一不可。今天要说的就是...
  • 可重复读和幻读

    2022-01-03 16:56:25
    二者很相似,不可重复读指的是对同一条记录(可以理解为对同一行)前后两次的读取结果是不一样的。 幻读指的是一个事务
  • 在了解脏读,不可重复度幻读之前,首先要明白这三种情况的出现都是和数据库并发事务有关联的,如果所有的读写都按照队列的形式进行,就不会出现问题。名词解析和解决方案脏读脏读又称无效数据读出(读出了脏数据)。...
  • 避免了脏读、不可重复读和幻读的发生。 4. Serializable(序列化) 最高隔离级别。所有事务操作依次顺序执行。会导致大量的超时以及锁竞争,同时导致并发下降,性能最差。不建议生产使用。 四、不同事务级别带来的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 12,071
精华内容 4,828
关键字:

mysql可重复度避免幻读

mysql 订阅