為什麼MySQL預設使用RR隔離等級?
對於資料庫的預設隔離級別,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保持相容性。