點選上方“芋道原始碼”，選擇“置頂公眾號”

技術文章第一時間送達！

原始碼精品專欄

摘要: 原文可閱讀 http://www.iocoder.cn/Fight/MySQL-messy-implementation-of-repeatable-read-isolation-levels 「shimohq」歡迎轉載，保留摘要，謝謝！

什麼是事務
事務的實現方式
不同機制下的不同隔離級別
幻讀（P3／A3）和寫偏斜（A5B）
mysql中的可重覆度

幻讀
寫偏斜
mysql中可重覆讀的實現

postgresql中的可重覆讀

無幻讀
寫偏斜

參考檔案

mysql的隔離級別並非是按照標準實現的，作為從pg切過來的程式員還真是不太適應，這篇文章討論mysql隔離級別實現的，希望對大家能有幫助。

什麼是事務

事務是資料庫一組讀寫操作的集合，事務具有ACID四個特性，原子性，一致性，隔離性和永續性。
事務有四個隔離級別，分別是讀未提交，讀已提交，可重覆讀和序列化。
以上這些內容相信熟悉傳統資料庫的人，對這些都很熟悉，接下來講的內容可能有些人就不太瞭解了。

事務的實現方式

資料庫事務的實現方式主要有兩種：

基於鎖的；
基於時間戳的，現在主流的實現就是基於時間戳的方式的一種，就是大家熟悉的MVCC機制；

因為機制不同，所以事務的表現也不盡相同。

不同機制下的不同隔離級別

SQL標準定義了四種隔離級別，分別是讀未提交，讀已提交，可重覆讀，可序列化。很明顯，越低隔離級別的事務併發行更好，但是一致性更低，嚴格來說，低隔離級別的事務是不符合A和I的，常用的隔離級別多為讀已提交和可重覆度。
但是隔離級別的定義是基於鎖併發控制實現的，基於MVCC機制實現的資料庫事務表現行為會稍有不同。
jim gray曾經有一篇論文討論不同機制實現的資料庫隔離級別的不同表現，並將隔離級別擴充套件到7個。見下圖：

基於此將常見的傳統資料庫隔離級別統計如下：

SYBASE支援的隔離級別：degree 0（read uncommitted）、degree 1（read committed）、degree 2（repeatable read）、degree 3（serializable isolation）；
ORACLE支援的隔離級別：read committed（consistent read）、serializable(snapshot isolation)；
DB2支援的隔離級別：read uncommitted、cursor stability、read stability、repeatable read；
Postgresql支援的隔離級別：read committed（consistent read）、repeatable read(snapshot isolation)、serializable isolation(Serialaizable Snapshot Isolation)；
SQL Server支援的隔離級別：read uncommitted、read committed snapshot 、read committed 、repeatable read、snapshot isolation、serializable isolation；
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)的現象。接下來，請看如下例子：

如上圖所示，事務2的insert發生在兩次select之間，這兩次select也如SI一樣正確的顯示了該看到的結果，但是update發生之後，一切就變了，MySQL的RR隔離級別也會幻讀！！！

寫偏斜

也許有人會說，mysql同時也是使用鎖的，因此發生幻讀不奇怪，所以我們可以看接下來這個寫偏斜的經典例子：

顯然，mysql也是會發生寫偏斜的。

mysql中可重覆讀的實現

看原始碼可以發現，mysql中的讀操作是使用MVCC機制實現，可以正確的查詢到需要的行，但是寫操作實現的時候有兩點和我想的不太一樣：

寫操作永遠讀取已提交的資料，並沒有走MVCC的邏輯；
寫操作的併發是透過鎖控制的，不檢查更新行是否是對本事務可見的。

MVCC機制行的可見條件很簡單，可以總結為兩句話：

對不同事務，插入事務已提交，刪除事務未提交(update可以看做先刪除後插入)；
對本事務，插入的statement發生在自己之前，刪除的statement未發生或在自己之後；

套用幻讀那個例子，本來事務1是不該看到新插入的行的（因為不符合可見條件1），但是update只讀取最新的行，因此對新插入的行做了一次更新，導致該行符合可見條件2，再次select就可以查到這個行。

根據這個實現，我們可以推理出，mysql的可重覆讀同樣會發生lost update和read skew，只要測試的事務中存在寫操作。具體例子可見此處

mysql的可重覆讀是比SI更低的隔離級別，在發生幻讀時，SI隔離級別事物的正確行為應該是後提交的事務回滾，而mysql兩個事務都可以提交，顯然，他的一致性更低，但是併發性更好（回滾率低），這是一次在使用者使用習慣，效能和一致性之間的權衡，至於優劣，就見仁見智了，至少現在看來不壞。

postgresql中的可重覆讀

無幻讀

pg實現的隔離級別是比較標準的，可重覆度級別（實際是SI）沒有幻讀，這裡舉兩個例子

第一個例子

類比mysql的第一個例子，和mysql不同，可以看到pg的事務update的時候只更新了兩行，不包括新插入的行

第二個例子

當該行同時被兩個可重覆級別的事務更新時，後提交的事務會回滾，因為更新只能在最新的行上執行，否則就是丟失更新了。

寫偏斜

可以看到，pg的可重覆級別事務，還是存在寫偏斜的，這是符合標準的。

參考檔案

《A Critique of ANSI SQL Isolation Levels》
mysql原始碼
pg原始碼
https://github.com/ept/hermitage/blob/master/mysql.md

如果你對 Dubbo 感興趣，歡迎加入我的知識星球一起交流。

知識星球

目前在知識星球（https://t.zsxq.com/2VbiaEu）更新瞭如下 Dubbo 原始碼解析如下：

01. 除錯環境搭建
02. 專案結構一覽
03. 配置 Configuration
04. 核心流程一覽

05. 拓展機制 SPI

06. 執行緒池

07. 服務暴露 Export

08. 服務取用 Refer

09. 註冊中心 Registry

10. 動態編譯 Compile

11. 動態代理 Proxy

12. 服務呼叫 Invoke

13. 呼叫特性

14. 過濾器 Filter

15. NIO 伺服器

16. P2P 伺服器

17. HTTP 伺服器

18. 序列化 Serialization

19. 叢集容錯 Cluster

20. 優雅停機

21. 日誌適配

22. 狀態檢查

23. 監控中心 Monitor

24. 管理中心 Admin

25. 運維命令 QOS

26. 鏈路追蹤 Tracing

…
一共 60 篇++

原始碼不易↓↓↓↓↓

點贊支援老艿艿↓↓

MySQL 亂七八糟的可重覆讀隔離級別實現