RR與RC隔離級別下MySQL不同的加鎖解鎖方式

最新 05-07

作者韓傑·沃趣科技MySQL資料庫工程師

出品沃趣科技

|RC與RR隔離級別下MySQL不同的加鎖解鎖方式

MySQL5.7.21

數據準備

確認隔離級別

同時開啟兩個會話，按下圖的流程開始操作。

確認隔離級別

同時開啟兩個會話，按下圖的流程開始操作。

3.1半一致讀發生條件

RC隔離級別

RR隔離級別，且innodb_locks_unsafe_for_binlog=true

3.2innodb_locks_unsafe_for_binlog

innodb_locks_unsafe_for_binlog默認為off。

如果設置為1，會禁用gap鎖，但對於外鍵衝突檢測（foreign-key constraint checking）或者重複鍵檢測（duplicate-key checking）還是會用到gap鎖。

啟用innodb_locks_unsafe_for_binlog產生的影響等同於將隔離級別設置為RC，不同之處是：

1）innodb_locks_unsafe_for_binlog是全局參數，影響所有session；但隔離級別可以是全局也可以是會話級別。

2）innodb_locks_unsafe_for_binlog只能在資料庫啟動的時候設置；但隔離級別可以隨時更改。

基於上述原因，RC相比於innodb_locks_unsafe_for_binlog會更好更靈活。

啟用innodb_locks_unsafe_for_binlog還有以下作用：

對於update或者delete語句，InnoDB只會持有匹配條件的記錄的鎖。在MySQL Server過濾where條件，發現不滿足後，會把不滿足條件的記錄釋放鎖。這可以大幅降低死鎖發生的概率。

簡單來說，semi-consistent read是read committed與consistent read兩者的結合。一個update語句，如果讀到一行已經加鎖的記錄，此時InnoDB返回記錄最近提交的版本，由MySQL上層判斷此版本是否滿足update的where條件。若滿足(需要更新)，則MySQL會重新發起一次讀操作，此時會讀取行的最新版本(並加鎖)。

來看下面這個例子：

這個例子中，表上沒有索引，所以對於記錄鎖會用到隱藏主鍵。

假設某個client開啟了一個update：

假設另一個client緊接著也開啟一個update：

每當InnoDB發起update，會先對每一行記錄加上排它鎖，然後再決定記錄是否滿足條件。如果不匹配，則innodb_locks_unsafe_for_binlog開啟，InnoDB就會把記錄上的鎖釋放掉。否則，InnoDB會一直持有鎖直到事務結束。具體如下：

如果innodb_locks_unsafe_for_binlog沒有開啟，第一個update會一直持有x鎖

第二個update會阻塞住直到第一個update提交或者回滾

x-lock(1,2); block and wait for firstUPDATEtocommitorroll back

如果innodb_locks_unsafe_for_binlog開啟，第一個update先持有x鎖，然後會釋放不匹配的記錄上面的x鎖

對於第二個update，InnoDB會開啟半一致讀，此時InnoDB返回記錄最近提交的版本，由MySQL上層判斷此版本是否滿足update的where條件。

4.1RC隔離級別

session 1

session 1執行：

由於color列無索引，因此只能走聚簇索引，進行全部掃描。加鎖如下：

註：如果一個條件無法通過索引快速過濾，那麼存儲引擎層面就會將所有記錄加鎖後返回，然後由MySQL Server層進行過濾。因此也就把所有的記錄，都鎖上了。

但在實際中，MySQL做了優化，如同前面作用1所提到的。在MySQL Server過濾條件，發現不滿足後，會調用unlock_row方法，把不滿足條件的記錄放鎖 (違背了2PL的約束)。這樣做，保證了最後只會持有滿足條件記錄上的鎖，但是每條記錄的加鎖操作還是不能省略的。

實際加鎖如下：

session 2

session 2執行：

session 2嘗試加鎖的時候，發現行上已經存在鎖，InnoDB會開啟semi-consistent read，返回最新的committed版本(1,black),(2，white),(3,black),(4,white)。MySQL會重新發起一次讀操作，此時會讀取行的最新版本(並加鎖)。如同前面作用2所提到的。

加鎖如下：

MySQL優化後實際加鎖如下：