MySQL 乱七八糟的可重复读隔离级别实现

原创
2018/08/18 12:12
阅读数 220

MySQL 乱七八糟的可重复读隔离级别实现

 

摘要: 原文可阅读 http://www.iocoder.cn/Fight/MySQL-messy-implementation-of-repeatable-read-isolation-levels shimohq」欢迎转载,保留摘要,谢谢!

  • 什么是事务
  • 事务的实现方式
  • 不同机制下的不同隔离级别
  • 幻读(P3/A3)和写偏斜(A5B)
  • mysql中的可重复度
    • 幻读
    • 写偏斜
    • mysql中可重复读的实现
  • postgresql中的可重复读
    • 无幻读
    • 写偏斜
  • 参考文档

mysql的隔离级别并非是按照标准实现的,作为从pg切过来的程序员还真是不太适应,这篇文章讨论mysql隔离级别实现的,希望对大家能有帮助。

什么是事务

事务是数据库一组读写操作的集合,事务具有ACID四个特性,原子性,一致性,隔离性和持久性。
事务有四个隔离级别,分别是读未提交,读已提交,可重复读和串行化。
以上这些内容相信熟悉传统数据库的人,对这些都很熟悉,接下来讲的内容可能有些人就不太了解了。

事务的实现方式

数据库事务的实现方式主要有两种:

  1. 基于锁的;
  2. 基于时间戳的,现在主流的实现就是基于时间戳的方式的一种,就是大家熟悉的MVCC机制;

因为机制不同,所以事务的表现也不尽相同。

不同机制下的不同隔离级别

SQL标准定义了四种隔离级别,分别是读未提交,读已提交,可重复读,可串行化。很明显,越低隔离级别的事务并发行更好,但是一致性更低,严格来说,低隔离级别的事务是不符合AI的,常用的隔离级别多为读已提交和可重复度。
但是隔离级别的定义是基于锁并发控制实现的,基于MVCC机制实现的数据库事务表现行为会稍有不同。
jim gray曾经有一篇论文讨论不同机制实现的数据库隔离级别的不同表现,并将隔离级别扩展到7个。见下图:

七种隔离级别

基于此将常见的传统数据库隔离级别统计如下:

  1. SYBASE支持的隔离级别:degree 0(read uncommitted)、degree 1(read committed)、degree 2(repeatable read)、degree 3(serializable isolation);
  2. ORACLE支持的隔离级别:read committed(consistent read)、serializable(snapshot isolation);
  3. DB2支持的隔离级别:read uncommitted、cursor stability、read stability、repeatable read;
  4. Postgresql支持的隔离级别:read committed(consistent read)、repeatable read(snapshot isolation)、serializable isolation(Serialaizable Snapshot Isolation);
  5. SQL Server支持的隔离级别:read uncommitted、read committed snapshot 、read committed 、repeatable read、snapshot isolation、serializable isolation;
  6. MySQL支持的隔离级别:read uncommitted、read committed(consistent read)、repeatable read(snapshot isolation)、serializable isolation;

幻读(P3A3)和写偏斜(A5B

上图的各个字母都是数据库的各种不一致现象。如果把写操作记作w,读操作记作r,那么这些有害依赖可以表示为下图

identifier

query

phenomena

P0

w1[x]…w2[x]…((c1 or a1) and (c2 or a2) in any order)

Dirty Write

P1

w1[x]…r2[x]…((c1 or a1) and (c2 or a2) in any order)

Dirty Read

P2

r1[x]…w2[x]…((c1 or a1) and (c2 or a2) any order)

Fuzzy / Non-Repeatable Read

P3

r1[P]…w2[y in P]…((c1 or a1) and (c2 or a2) any order)

Phantom

P4

r1[x]…w2[x]…w1[x]…c1

Lost Update

P4C

rc1[x]…w2[x]…w1[x]…c1

Lost Update

A3

r1[P]…w2[y in P]…c2….r1[P]…c1

Phantom

A5A

r1[x]…w2[x]…w2[y]…c2…r1[y]…(c1 or a1)

Read Skew

A5B

r1[x]…r2[y]…w1[y]…w2[x]…(c1 and c2 occur)

Write Skew

mysql中的可重复度

幻读

mysql是支持MVCC机制实现的数据库,因此很多人(包括我)会想当然认为他的SI应该就是标准的实现,不会出现幻读(A3/P3)的现象。接下来,请看如下例子:

mysql幻读1-1

如上图所示,事务2insert发生在两次select之间,这两次select也如SI一样正确的显示了该看到的结果,但是update发生之后,一切就变了,MySQLRR隔离级别也会幻读!!!

写偏斜

也许有人会说,mysql同时也是使用锁的,因此发生幻读不奇怪,所以我们可以看接下来这个写偏斜的经典例子:

mysql write skew

显然,mysql也是会发生写偏斜的。

mysql中可重复读的实现

看源码可以发现,mysql中的读操作是使用MVCC机制实现,可以正确的查找到需要的行,但是写操作实现的时候有两点和我想的不太一样:

  1. 写操作永远读取已提交的数据,并没有走MVCC的逻辑;
  2. 写操作的并发是通过锁控制的,不检查更新行是否是对本事务可见的。

MVCC机制行的可见条件很简单,可以总结为两句话:

  1. 对不同事务,插入事务已提交,删除事务未提交(update可以看做先删除后插入);
  2. 对本事务,插入的statement发生在自己之前,删除的statement未发生或在自己之后;

套用幻读那个例子,本来事务1是不该看到新插入的行的(因为不符合可见条件1),但是update只读取最新的行,因此对新插入的行做了一次更新,导致该行符合可见条件2,再次select就可以查到这个行。

根据这个实现,我们可以推理出,mysql的可重复读同样会发生lost updateread skew,只要测试的事务中存在写操作。具体例子可见此处

mysql的可重复读是比SI更低的隔离级别,在发生幻读时,SI隔离级别事物的正确行为应该是后提交的事务回滚,而mysql两个事务都可以提交,显然,他的一致性更低,但是并发性更好(回滚率低),这是一次在用户使用习惯,性能和一致性之间的权衡,至于优劣,就见仁见智了,至少现在看来不坏。

postgresql中的可重复读

无幻读

pg实现的隔离级别是比较标准的,可重复度级别(实际是SI)没有幻读,这里举两个例子

第一个例子

pg无幻读1

类比mysql的第一个例子,和mysql不同,可以看到pg的事务update的时候只更新了两行,不包括新插入的行

第二个例子

pg无幻读2

当该行同时被两个可重复级别的事务更新时,后提交的事务会回滚,因为更新只能在最新的行上执行,否则就是丢失更新了。

写偏斜

pg write skew

可以看到,pg的可重复级别事务,还是存在写偏斜的,这是符合标准的。

参考文档

  1. 《A Critique of ANSI SQL Isolation Levels》
  2. mysql源码
  3. pg源码
  4. https://github.com/ept/hermitage/blob/master/mysql.md

 

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
2 收藏
0
分享
返回顶部
顶部