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

96
文韬123456
2017.09.11 11:26* 字数 1739

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

什么是事务

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

事务的实现方式

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

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

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

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

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

七种隔离级别.png

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

  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;

幻读(P3/A3)和写偏斜(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.jpg

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

写偏斜

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

mysql write skew.png

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

mysql中可重复读的实现

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

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

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

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

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

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

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

postgresql中的可重复读

无幻读

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

第一个例子
pg无幻读1.jpg

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

第二个例子
pg无幻读2.jpg

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

写偏斜

pg write skew.png

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

参考文档

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

广告

最后,打个广告,如果对创业,分布式数据库和开源社区感兴趣,欢迎加入我们,实习和工作都很欢迎!
pingcap是国内为数不多的newsql方向的分布式数据库,维护国内最顶级的开源社区,关注度近万,做类f1+spanner架构,和多家公司有合作关系。
TiDB: https://github.com/pingcap/tidb
TiKV: https://github.com/pingcap/tikv
Email: xuwentao@pingcap.com
微信: fbisland

Gupao