為什麼MySQL預設使用RR隔離等級?

2024.04.23

對於資料庫的預設隔離級別,Oracle預設的隔離級別是RC,而MySQL預設的隔離級別是RR。

那麼,你知道為什麼Oracle選擇RC作為預設級別,而MySQL要選擇RR作為預設的隔離級別嗎?

Oracle的隔離級別

Oracle支援ANSI/ISO SQL定義的Serializable和Read Committed兩種隔離級別,根據Oracle官方文件的介紹,Oracle的隔離級別包括Read Committed、Serializable和Read-Only。

圖片圖片

Read-Only的隔離等級類似於Serializable,然而僅允許唯讀交易進行資料檢索,不允許在交易中修改數據,除非使用者是SYS使用者。

在Oracle的這三種隔離級別中,顯而易見,Serializable和Read-Only都不適合作為預設隔離級別,因此唯一的選擇就是Read Committed了。

MySQL的隔離級別

與Oracle相比,MySQL提供的預設隔離等級範圍更加廣泛。

首先,我們排除了Serializable和Read Uncommitted這兩個級別,原因是一個隔離級別過高會影響並發度,另一個過低則有髒讀問題。

剩下的RR和RC兩種,如何選擇呢?

MySQL在設計之初就旨在提供一個穩定的關聯式資料庫。為解決MySQL單點故障問題,MySQL採取了主從複製機制。

所謂的主從複製,即透過建立MySQL集群,整體向外提供服務。叢集內的機器分為主伺服器(Master)和從伺服器(Slave),主伺服器負責提供寫入服務,而從伺服器提供讀取服務。

在MySQL主從複製過程中,資料的同步透過binlog進行。簡單來說,主伺服器將資料變更記錄到binlog中,然後將binlog同步傳輸給從伺服器。從伺服器接收到binlog後,將其中的資料還原到自己的資料庫儲存中。

那麼,binlog裡記錄的究竟是什麼內容呢?它的格式又是怎樣的呢?

MySQL的binlog主要支援三種格式,分別為statement、row和mixed。 MySQL從5.1.5版本開始支援row格式,在5.1.8版本開始支援mixed格式。

statement和row之間最重要的差異在於,當binlog的格式為statement時,binlog記錄的是SQL語句的原文。

由於MySQL早期僅支援statement這一種binlog格式,因此在使用提交讀取(Read Committed)和未提交讀取(Read Uncommitted)這兩種隔離等級時都可能會出現問題。

舉個例子,有一個資料庫表t1,表中有以下兩條記錄:

CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into t1 values(10,1);
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.

接著開始執行兩個事務的寫入操作:

第一節

第二節

設定會話事務隔離等級讀取已提交;


設定自動提交=0;

設定會話事務隔離等級讀取已提交;

開始;

開始;

從 t1 中刪除,其中 b < 100;



插入 t1 值(10,99);


犯罪;

犯罪;


以上兩個事務執行之後,資料庫裡面的記錄會只有一筆記錄(10,99),這個發生在主庫的資料變更大家都能理解。

即使Session 1 的刪除操作在Session 2 的插入操作之後提交,由於READ COMMITTED 的隔離級別,Session 2 的插入操作不會看到Session 1 的刪除操作,所以最後資料庫中仍然會留下Session 2 插入的記錄(10,99)。

這種行為是READ COMMITTED 隔禽層級的一種特性,它會在交易開始時建立一個快照。確保交易之間的隔離性,避免了資料不一致性的問題。

以上兩個事務執行之後,會在bin log中記錄兩筆記錄,因為事務2先提交,所以insert into t1 values(10,99);會被優先記錄,然後再記錄delete from t1 where b < 100; (再次提醒:statement格式的bin log記錄的是SQL語句的原文)

這樣bin log同步到備庫之後,SQL語句回放時,會先執行insert into t1 values(10,99);,再執行delete from t1 where b < 100;。

這時候,資料庫中的資料就會變成EMPTY SET,也就是沒有任何資料。這就導致主庫和備庫的資料不一致了! ! !

為了解決這種問題,MySQL將資料庫的預設隔離等級設定為Repeatable Read。在Repeatable Read隔離等級下,針對更新資料時會不僅對更新的行加行級鎖,還會增加GAP鎖定和next-key鎖定。在上述例子中,當事務2 執行時,由於事務1 增加了GAP鎖和next-key鎖,這將導致事務2 執行被阻塞,需要等待事務1 提交或回滾後才能繼續執行。

除了設定預設的隔離等級外,MySQL還禁止在使用statement格式的binlog的情況下,將交易隔離等級設定為READ COMMITTED。

一旦使用者主動修改隔離級別,嘗試更新時,會報錯:

ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
  • 1.

因此,我們現在明白了為什麼MySQL選擇Repeatable Read作為預設的資料庫隔離等級了,實際上是為了與歷史上那種statement格式的binlog保持相容性。