作者 ? 韓杰·沃趣科技MySQL數據庫工程師

出品? ?沃趣科技

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

MySQL5.7.21

數據準備

root@localhost : pxs 05:26:27> show create table dots\G

*************************** 1. row ***************************

Table: dots

Create Table: CREATE TABLE `dots` (

`id` int(11) NOT NULL,

`color` varchar(20) COLLATE utf8_bin NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

1 row in set (0.00 sec)

root@localhost : pxs 05:27:34> select * from dots;

+----+-------+

| id | color |

+----+-------+

| ?1 | black |

| ?2 | white |

| ?3 | black |

| ?4 | white |

+----+-------+

4 rows in set (0.00 sec)

root@localhost : pxs 01:57:02> show variables like 'innodb_locks_unsafe_for_binlog';

+--------------------------------+-------+

| Variable_name ? ? ? ? ? ? ? ? ?| Value |

+--------------------------------+-------+

| innodb_locks_unsafe_for_binlog | OFF ?|

+--------------------------------+-------+

1 row in set (0.00 sec)

1.RC隔離級別

確認隔離級別

root@localhost : pxs 05:27:35> show variables like '%iso%';

+-----------------------+----------------+

| Variable_name ? ? ? ?| Value ? ? ? ? ?|

+-----------------------+----------------+

| transaction_isolation | READ-COMMITTED |

| tx_isolation ? ? ? ? ?| READ-COMMITTED |

+-----------------------+----------------+

2 rows in set (0.01 sec)

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

2.RR隔離級別

確認隔離級別

root@localhost : pxs 05:24:41> show variables like '%iso%';

+-----------------------+-----------------+

| Variable_name ? ? ? ?| Value ? ? ? ? ?|

+-----------------------+-----------------+

| transaction_isolation | REPEATABLE-READ |

| tx_isolation ? ? ? ? ?| REPEATABLE-READ |

+-----------------------+-----------------+

2 rows in set (0.01 sec)

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

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會重新發起一次讀操作，此時會讀取行的最新版本(并加鎖)。

來看下面這個例子：

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;

INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);

COMMIT;

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

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

SET autocommit = 0;

UPDATE t SET b = 5 WHERE b = 3;

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

SET autocommit = 0;

UPDATE t SET b = 4 WHERE b = 2;

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

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

x-lock(1,2); retain x-lock

x-lock(2,3); update(2,3) to (2,5); retain x-lock

x-lock(3,2); retain x-lock

x-lock(4,3); update(4,3) to (4,5); retain x-lock

x-lock(5,2); retain x-lock

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

x-lock(1,2); block and wait for first UPDATE to commit or roll back

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

x-lock(1,2); unlock(1,2)

x-lock(2,3); update(2,3) to (2,5); retain x-lock

x-lock(3,2); unlock(3,2)

x-lock(4,3); update(4,3) to (4,5); retain x-lock

x-lock(5,2); unlock(5,2)

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

x-lock(1,2); update(1,2) to (1,4); retain x-lock

x-lock(2,3); unlock(2,3)

x-lock(3,2); update(3,2) to (3,4); retain x-lock

x-lock(4,3); unlock(4,3)

x-lock(5,2); update(5,2) to (5,4); retain x-lock

4.一開始的例子

4.1?RC隔離級別

session 1

session 1執行：

update dots set color = 'black' where color = 'white';

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

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

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

實際加鎖如下：

session 2

session 2執行：

update dots set color = 'white' where color = 'black';

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執行：

update dots set color = 'black' where color = 'white';

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

session 2

session 2執行：

update dots set color = 'white' where color = 'black';

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

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

環境準備

root@localhost : (none) 04:57:46> show ?variables like '%iso%';

+-----------------------+-----------------+

| Variable_name ? ? ? ?| Value ? ? ? ? ?|

+-----------------------+-----------------+

| transaction_isolation | REPEATABLE-READ |

| tx_isolation ? ? ? ? ?| REPEATABLE-READ |

+-----------------------+-----------------+

2 rows in set (0.01 sec)

root@localhost : (none) 04:55:25> show variables like 'innodb_locks_unsafe_for_binlog';

+--------------------------------+-------+

| Variable_name ? ? ? ? ? ? ? ? ?| Value |

+--------------------------------+-------+

| innodb_locks_unsafe_for_binlog | ON ? ?|

+--------------------------------+-------+

1 row in set (0.00 sec)

root@localhost : pxs 05:00:54> select * from dots;

+----+-------+

| id | color |

+----+-------+

| ?1 | black |

| ?2 | white |

| ?3 | black |

| ?4 | white |

+----+-------+

4 rows in set (0.00 sec)

開始操作

注：過程現象滿足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_binloghttp://hedengcheng.com/?p=771http://hedengcheng.com/?p=220

| ?作者簡介

韓杰 ?沃趣科技MySQL數據庫工程師

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

相關鏈接

更多干貨，歡迎來撩~

總結

以上是生活随笔為你收集整理的rc mysql common_RR与RC隔离级别下MySQL不同的加锁解锁方式的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。