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

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

---

• MySQL5.7.21

• 資料準備

點選(此處)摺疊或開啟

1. root@localhost : pxs 05:26:27> show create table dots\G
   
2. *************************** 1. row ***************************
   
3.   Table: dots
   
4. Create Table: CREATE TABLE `dots` ( `id` int(11) NOT NULL, `color` varchar(20) COLLATE utf8_bin NOT NULL,
   
5. PRIMARY KEY (`id`)
   
6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin 1 row in set (0.00 sec)
   
7. root@localhost : pxs 05:27:34> select * from dots;
   
8. +----+-------+ | id | color |
   
9. +----+-------+
   
10. | 1 | black | | 2 | white |
    
11. | 3 | black | | 4 | white |
    
12. +----+-------+
    
13. 4 rows in set (0.00 sec)
    
14. root@localhost : pxs 01:57:02> show variables like 'innodb_locks_unsafe_for_binlog';
    
15. +--------------------------------+-------+
    
16. | Variable_name | Value | +--------------------------------+-------+ | innodb_locks_unsafe_for_binlog | OFF |
    
17. +--------------------------------+-------+
    
18. 1 row in set (0.00 sec)

1.RC隔離級別

• 確認隔離級別

  點選(此處)摺疊或開啟

  1. root@localhost : pxs 05:27:35> show variables like '%iso%';
     
  2. +-----------------------+----------------+ | Variable_name | Value |
     
  3. +-----------------------+----------------+
     
  4. | transaction_isolation | READ-COMMITTED | | tx_isolation | READ-COMMITTED |
     
  5. +-----------------------+----------------+
     
  6. 2 rows in set (0.01 sec)

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

2.RR隔離級別

• 確認隔離級別

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

3.半一致讀semi-consistent read

3.1 半一致讀發生條件

• RC隔離級別

• RR隔離級別，且innodb_locks_unsafe_for_binlog=true

3.2 innodb_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提交或者回滾

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

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

4.一開始的例子

4.1 RC隔離級別

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優化後實際加鎖如下： 

4.2 RR隔離級別

session 1

session 1執行：

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

session 2

session 2執行：

更新被阻塞。 
等session 1提交commit之後，session 2update才會成功。

引申：RR隔離級別，且開啟innodb_locks_unsafe_for_binlog=ON

• 環境準備

• 開始操作

注：過程現象滿足RR隔離級別，也符合設定innodb_locks_unsafe_for_binlog=ON的情況。因為前面所講的啟用innodb_locks_unsafe_for_binlog會產生作用1與作用2，所以整個加鎖與解鎖情況與RC隔離級別類似。

參考：

《資料庫事務處理的藝術：事務管理與併發控制》 
https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog 
http://hedengcheng.com/?p=771 
http://hedengcheng.com/?p=220

|  作者簡介

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

熟悉mysql體系架構、主從複製，熟悉問題定位與解決。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2154284/，如需轉載，請註明出處，否則將追究法律責任。