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

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

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 158,233评论 4 360
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 67,013评论 1 291
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 108,030评论 0 241
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 43,827评论 0 204
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 52,221评论 3 286
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 40,542评论 1 216
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,814评论 2 312
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 30,513评论 0 198
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 34,225评论 1 241
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,497评论 2 244
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,998评论 1 258
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 28,342评论 2 253
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,986评论 3 235
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,055评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,812评论 0 194
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 35,560评论 2 271
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 35,461评论 2 266

推荐阅读更多精彩内容