文档章节

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

DemonsI
 DemonsI
发布于 08/18 12:12
字数 1793
阅读 11
收藏 2

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

 

© 著作权归作者所有

DemonsI
粉丝 15
博文 248
码字总数 645695
作品 0
朝阳
程序员
mysql乱七八糟的可重复读隔离级别实现

mysql的隔离级别并非是按照标准实现的,作为从pg切过来的程序员还真是不太适应,这篇文章讨论mysql隔离级别实现的,希望对大家能有帮助。 什么是事务 事务是数据库一组读写操作的集合,事务具...

徐文韬
2017/09/11
0
0
MySQL 中隔离级别 RC 与 RR 的区别

数据库事务ACID特性 数据库事务的4个特性: 原子性(Atomic): 事务中的多个操作,不可分割,要么都成功,要么都失败; All or Nothing. 一致性(Consistency): 事务操作之后, 数据库所处的状态...

Ocean_K
08/17
0
0
MySQL事务隔离级别详解

SQL标准定义了4类隔离级别,包括了一些具体规则,用来限定事务内外的哪些改变是可见的,哪些是不可见的。低级别的隔离级一般支持更高的并发处理,并拥有更低的系统开销。 Read Uncommitted(...

风行韩国
2014/07/29
0
0
【20180613】MySQL innodb 引擎如何解决幻读

MySQL innodb 引擎如何解决幻度 MySQL innodb 引擎事务的隔离级别有四种,默认是可重复读(REPEATABLE READ) 未提交读(READ UNCOMMITTED)。俩个不同session,一个session正在显式的开启事务进...

liuhuang9496
06/13
0
0
MySQL InnoDB存储引擎的事务隔离级别

我们知道,在关系数据库标准中有四个事务隔离级别: 未提交读(Read Uncommitted):允许脏读,也就是可能读取到其他会话中未提交事务修改的数据 提交读(Read Committed):只能读取到已经提交的...

miyae
2014/03/25
0
0

没有更多内容

加载失败,请刷新页面

加载更多

CentOS7全局安装composer

1. 下载composer-setup.php到当前目录 php -r "copy('https://install.phpcomposer.com/installer', 'composer-setup.php');" 2. 安装 php composer-setup.php 3. 将composer设置成全局 mv c......

月夜中徘徊
28分钟前
1
0
20180920上课截图

小丑鱼00
34分钟前
1
0
基于TCP的远程服务调用

前言 上篇,分析了基于HTTP方式的RPC调用。本篇将在上篇的基础上,分析基于TCP方式的RPC调用。代码的整体思路是一致的,可以看作是在上篇功能上的扩展——即通信的方式。 代码:https://git...

MarvelCode
37分钟前
1
0
67:shell脚本介绍 | shell脚本结构 | 执行data命令用法 | shell脚本中变量

1、shell脚本介绍: shell是一种脚本语言和传统的开发语言相比,会比较简单: shell有自己语法,可以支持逻辑判断、循环等语法: 可以自定义函数,目的是减少重复的代码: shell是系统命令的集合...

芬野de博客
今天
1
0
json schema

json schema是用来验证和描述json对象结构的。 在线验证:https://www.jsonschemavalidator.net/ json schema 编辑器,推荐VSCode,写上"$schema": "https://raw.githubusercontent.com/jso......

谷永权
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部