数据库隔离级别和各个级别下的缺点

2021-01-20 更新

Mysql RR 级别,使用 next-key lock(record lock + gap lock)解决了当前读的幻读问题,但是还是有一些场景能重现出幻读问题,就比如:

  1. create table ab(a int primary key, b int);
  2. Tx1:
    begin;
    select * from ab; // empty set
  3. Tx2:
    begin;
    insert into ab values(1,1);
    commit;
  4. Tx1:
    select * from ab; // empty set, expected phantom read missing.
    update ab set b = 2 where a = 1; // 1 row affected.
    select * from ab; // 1 row. phantom read here!!!!
    commit;

例子来自:https://stackoverflow.com/questions/5444915/how-to-produce-phantom-reads

2021-01-07 更新

对幻读加一个总结,RR 下的幻读其实是「快照读不存在,当前读存在,而且是针对的新增数据来说」,一会儿有,一会儿没有,像幻影一样。

2018 - 04 - 19 更新

今天突然感觉对 RC 下的不可重复读不太理解了,然后找了些网上的资料回顾下。比如 MySQL 四种事务隔离级的说明,发现大家在测不可重复读和可重复读的时候,都只直接用 select 去查的,也就是快照读。但是其实 RC 和 RR 下都是有 MVCC 的,自己又弄了几个例子测了测。本来理解的是无论 RC 还是 RR,只要在一个事务内的 select 都应该返回一样的结果。RC 下的不可重复读应该是对当前读来说的,RC 下的快照读本来也以为是能两次查询返回一致的。但是自己跑去测了下发现 RC 下两次读其实能读到其他事务提交的新数据,但是对数据的更新读取不到,还是只能读取到快照。

2018 - 03 - 02 更新

幻读:同一个事务的两次查询(delete insert update 触发唯一约束也算查询)第一次查没有,第二次查又有。幻体现在开始看没有,转头一看又有了,跟平时理解的幻觉有点相反。平时理解的幻觉是当时看有,之后看没有,怀疑当时出现了幻觉。

大牛何登成关于幻读的定义:

所谓幻读,就是同一个事务,连续做两次当前读 (例如:select * from t1 where id = 10 for update;),那么这两次当前读返回的是完全相同的记录 (记录数量一致,记录本身也一致),第二次的当前读,不会比第一次返回更多的记录 (幻象)。

引用自:http://hedengcheng.com/?p=771#_Toc374698320

那么幻读是不是不被允许的?

幻读其实简单说就是查询到了其他事务新增的数据。既然原理就这么简单,允许还是不允许就要看具体业务。幻读存在的情况下,同一个事务的先后两次查询,后一次查询会出现前一次没查到的数据。具体业务具体情况分析即可。

引申到 MySQL,rc 和 rr 因为有 MVCC 的存在,解决了大部分的幻读问题,没解决的部分是指下面的这个例子(delete 的情况)。但是 rc 和 rr 都还是存在幻读问题,并且 rc 还有不可重复读问题。真正的解决幻读只能用序列化的级别。(此处不对,用当前读是可以解决幻读问题问题的,并且要是 RR 下)

此处可以引用个资料(知乎回答):

如果实现snapshot(mysql下的repeatable read)隔离级别,读可以不加锁。保证无脏读,无更新丢失,无不可重复读(第二次读到变更的记录),排除绝大多数幻读

mysql读取数据是快照读,不加锁,那RR的防止脏读幻读是如何实现的

另外还有:

https://github.com/Yhzhtk/note/issues/42

另外 RR 下,如果第一次查询用 next-key lock 对数据加锁,那么就能在这个级别下就能实现避免幻读。但是会影响其他事务对索引的间隙内进行写入。

MVCC 与幻读的关系?

如上第二段描述。

看这个比较透彻:

https://tech.meituan.com/innodb-lock.html

幻读的描述:

REPEATABLE READ(可重复读)

REPEATABLE READ 解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上,可重复读隔离级别还是无法解决另外一个幻读(Phantom Read)的问题。所谓幻读,指的是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行(Phantom Row)。InnoDB 和 XtraDB 存储引擎通过多版本并发控制(MVCC, Multiversion Concurrency Control)解决了幻读的问题。

引用自《高性能MySQL 第三版》,侵删

MVCC 的描述:

MVCC 的实现,是通过保存数据在某个时间点的快照来实现的。也就是说,不管需要执行多长时间,每个事务看到的数据都是一致的。根据事务开始的时间不同,每个事务对同一张表,同一时刻看到的数据可能是不一样的。如果之前没有这方面的概念,这句话听起来就有点迷惑。熟悉了以后会发现,这句话其实还是很容易理解的。

前面说到不同存储引擎的 MVCC 实现是不同的,典型的有乐观(optimistic)并发控制和悲观(pessimistic)并发控制。下面我们通过 InnoDB 的简化版行为来说明 MVCC 是如何工作的。

InnoDB 的 MVCC,是通过在每行记录后面保存两个隐藏的列来实现的。这两个列,个保存了行的创建时间,一个保存行的过期时间(或删除时间)。当然存储的并不是实际的时间值,而是系统版本号(system version number)。每开始一个新的事务,系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号,用来和查询到的每行记录的版本号进行比较。下面看一下在 REPEATABLE READ 隔离级别下,MVCC 具体是如何操作的。

SELECT

  • InnoDB 会根据以下两个条件检查每行记录:

    • a. InnoDB 只查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号),这样可以确保事务读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或者修改过的。
    • b。行的删除版本要么未定义,要么大于当前事务版本号。这可以确保事务读取到的行,在事务开始之前未被删除。

    只有符合上述两个条件的记录,才能返回作为查询结果。

INSERT

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

保存这两个额外系统版本号,使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单,性能很好,并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间,需要做更多的行检查工作,以及一些额外的维护工作。

MVCC 只在 REPEATABLE READ 和 READ COMMITTED 两个隔离级别下工作。其他两个隔离级别都和 MVCC 不兼容注 4,因为 READ UNCOMMITTED 总是读取最新的数据行,而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

引用自《高性能MySQL 第三版》,侵删

2018 - 01 - 15 更新

事务的隔离性定义:

尽管多个事务可能并发执行,但系统保证,对于任何一对事务 T1和 T2,在 T1 看来,T2 或者在 T1 开始之前已经完成执行,或者在 T1 完成之后开始执行。因此,每个事务都感觉不到系统中有其他事务在并发地执行。

引用自《数据库系统概念》,侵删

要完全遵守上面的定义可能只有让系统每个瞬间只有一个事务在运行才行。如果要让事务能够并行执行,事务之间一定会存在冲突。一个事务内对数据的操作只有读取和写入两种。首先写入冲突(脏写)一定是不被允许的。两次同时运行的事务,如果都对一块数据进行写入,如果一个事务的写操作覆盖了另一个事务的写操作,那被覆盖的那个事务就会蒙圈。明明我事务还没提交,才刚写入成 A,怎么再执行查询就变成 B 了(其他事务改成了 B)。这是不被允许的。

然后就是对数据的读取。如果一个数据没有被改动,那么再多的事务同时对他进行读取都是不会有问题的,问题发生在不同事务同时对数据进行读取和写入。事务的隔离级别其实是通过控制事务内读操作可以选取的数据范围来满足事务的隔离性要求。

当对并发事务不做任何事务隔离机制,读取操作可以读取任何其他事务操作中的数据。这样会发生如下这些违反隔离性的问题:

dirty read

读取到其他事务未提交的数据。

nonRepeatable read

同一个事务内,先后读取过两次同一块数据。这块数据在两次读取之间被其他事务修改过(并提交),最终两次的读取结果不一致。

这种情况读到内容虽然不脏,都是数据实际有过的状态(不是事务未执行完时的中间状态),隔离性要求事务之间不得相互影响,其他事务的写入操作都影响到了本事务内的两次读取操作的结果,显然是不隔离的。

phantom read:

事务无法查询到其他事务提交的数据,而导致的写失败,具体如下:

users: id 主键

1、T1:select * from users where id = 1;

2、T2:insert into users(id, name`) values (1, ‘big cat’);

3、T1:insert into users(id, name`) values (1, ‘big cat’);

T1 :主事务,检测表中是否有 id 为 1 的记录,没有则插入,这是我们期望的正常业务逻辑。

T2 :干扰事务,目的在于扰乱 T1 的正常的事务执行。

在 RR 隔离级别下,1、2 是会正常执行的,3 则会报错主键冲突,对于 T1 的业务来说是执行失败的,这里 T1 就是发生了幻读,因为T1读取的数据状态并不能支持他的下一步的业务,见鬼了一样。

引自:https://www.zhihu.com/question/47007926

另外这个链接关于幻读的例子也比较有价值:http://www.cnblogs.com/zhoujinyi/p/3437475.html

以上两个例子其实是一样的,都是因为插入事先检查不存在的数据时,数据库反馈插入的数据已经存在了。「幻」体现在开始的查询返回没有,后来执行写入又说有,最初的查询像幻觉一样。

关于幻读,还有这个资料提到了:

Phantom reads do not occur if you’re simply doing a SELECT. They only occur if you do UPDATE or DELETE or SELECT FOR UPDATE

http://www.cnblogs.com/zuoxingyu/archive/2012/09/27/2705285.html

其他

脏读只需要限制事务只能读取到其他事务提交的结果就能解决了。

不可重复读需要在事务开始时给所操作数据定一个快照,事务内不管怎么读,都只能读取事务初定下的数据的快照版本,看不到后来快照时间戳之后发生的任何新增和更新。这个时候就不管怎么重复读,结果都是一致的。当然快照的版本不能在其他事务有未提交数据时选取。

1
这里有一个问题,快照机制如果说是对当前数据的一个快照,事务内对这块的数据查询的结果一直是一致的。按照这个思路,对数据的更新确实是可以一致,那新增数据呢。这个看起来是间隙锁干的事,是吗?

应该是的,见:http://www.cnblogs.com/zuoxingyu/archive/2012/09/27/2705285.html

幻读的话用序列化的隔离级别一定能解决。此外 MySQL 的话在可重复读级别下有个办法:

在默认的可重复读的隔离级别里,可以使用加锁读去查询最新的数据(提交读)

见这篇的最后:http://www.cnblogs.com/zhoujinyi/p/3437475.html

幻读的解决需要继续研究 …

最后就可以填下这个网上见了 N 次的表格了

隔离级别 Dirty read NonRepeatable read Phantom read
Read uncommitted 可能 可能 可能
Read committed 不可能 可能 可能
Repeatable read 不可能 不可能 可能
Serializable 不可能 不可能 不可能

MySQL InnoDB的可重复读并不保证避免幻读,需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-key locks

引自:http://www.cnblogs.com/zhoujinyi/p/3437475.html

Next-key locks => 2018-01-30-predicate-read-and-mysql-gaplock 有解释

幻读的 MySQL 官方文档解释:https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html

旧文:

read uncommited

这个级别中允许事务读取其他事务未提交的数据,那么如果提交的数据最终被回滚,前一次读取的数据其实最终是未持久化的数据,这就造成了问题,通常称为脏读。

read commited

这个级别中允许事务读取其他事务已经提交了的数据,也还是有问题的。事务的隔离要求事务间相互隔离,如果一个事务能读取到其他他事务commit的数据,就违反了隔离性。这个问题通常称为不可重复读。

下一个隔离级别就是为了解决不可重读读问题。他的思路是在针对一个事务,将这个事务能访问到的所有数据都静止在一个版本,其实就是事务开始的时候有一个时间戳,这个时间戳作为版本,所有其他数据维持在这个版本不变,直到当前事务的最终提交为止。就像建立了一个平行宇宙,真正的宇宙还在推进,但是事务内有一个平行的禁止的宇宙,只供给当前事务访问,怎么读都不会重复,因为是禁止的:)。平行宇宙的说法是网上看到的,写到这里顺便用上,想想还正式贴切:)。

repeatable read

数据库保证每个事务开始以后,在事务内读取到的数据都是在事务之初的那个版本,即在事务内多次读取同一块数据都是一样的。但是会有个问题是如果事务内有新增的数据,那么可能第一次没查询到数据,第二次又查询到。这是个反常现象,因为这违反了事务隔离性原则。事务隔离性原则要求并行的事务互相之间不影响。前面提到的反常现象通常称为「幻读」。

2017-04-04 update:

上面的描述是错误的:「那么可能第一次没查询到数据,第二次又查询到」这个是不会出现的。可重复读如字面意思,保证了在一个事务内,重复读取的结果是一致。虽然能保证读取一致,但是如果在第一个事务里执行了会影响第二个事务新增行的写入动作,这个时候数据其实是存在的。如果第二个事务新增了主键为1的数据行,第一个事务再去新增主键为1的数据的时候就会报错了。但是第一个事务去查询的时候却没有主键1的数据,这才是所谓的「幻读」[http://www.cnblogs.com/zhoujinyi/p/3437475.html][1]

2018-01-15 update:

「但是会有个问题是如果事务内有新增的数据,那么可能第一次没查询到数据,第二次又查询到。这是个反常现象,因为这违反了事务隔离性原则」这段有问题,第二次也不会查到这个数据的。但是如果其他事务新增的数据本身有约束条件(比如主键唯一),那么本事务虽然查不到数据,但是写入确会报错(违反主键约束),这个是 repeatable read 没有解决的问题,就是幻读。明明读数据的时候告诉我没有,写的时候告诉我已经存在,之前的读取是假的吗 …

serializable

序列化执行,但其实不是真正意义上的串行执行,如果事务是「可序列化」的(具体见《数据库系统概念》事务一章的描述),那么也是能够并行运行的,但是「可序列化」本身要求不低,不是所有种类的事务操作都支持,这个可以展开来继续研究。

另外

无论哪个级别,数据库都是不允许脏写的。两次并行事务同时对一块数据进行写入,必然会是后一个等待前一个写完。不会同时写入,其中一个丢失的。举个例子就是,比如 update sometable set column1=2 where version=1,数据库保证了如果有两个事务同时运行上面的语句,那么只会有一条语句返回1。

问题

《数据库系统概念》中,事务包含指令,并发执行事务如何协调这些指令的并行执行,其策略是什么。调度策略中的「可序列化」是什么。见:

数据库并发控制相关的一些问题和总结

[1]: http://www.cnblogs.com/zhoujinyi/p/3437475.html

数据库隔离级别和各个级别下的缺点

http://footmanff.com/2017/03/23/2017-03-23-db-transcation-isolate-level/

Author

张阿力

Posted on

2017-03-23

Updated on

2021-01-20

Licensed under