關於葉老師一個RR模式下UPDATE鎖範圍擴大案例的研究

gaopengtttt發表於2017-08-23

原創轉載請註明出處有誤請指出

一、前言

葉金榮老師分享了一篇文章如下:
https://mp.weixin.qq.com/s/09DJCyMq8kBn4mlezgzUgg
這裡只研究下鎖的模式,借用葉老師的表和語句

mysql> select * from t1;
+----+----+----+----+
| c1 | c2 | c3 | c4 |
+----+----+----+----+
|  0 |  0 |  0 |  0 |
|  1 |  1 |  1 |  0 |
|  3 |  3 |  3 |  0 |
|  4 |  2 |  2 |  0 |
|  6 |  8 |  5 |  0 |
|  7 |  6 |  6 | 10 |
| 10 | 10 |  4 |  0 |
+----+----+----+----+

CREATE TABLE `t1` (
  `c1` int(10) unsigned NOT NULL DEFAULT '0',
  `c2` int(10) unsigned NOT NULL DEFAULT '0',
  `c3` int(10) unsigned NOT NULL DEFAULT '0',
  `c4` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

二、RR模式下的鎖模式

我們先來看看下面兩個語句的執行計劃

mysql> desc  update t1 set c4=123 where c2>=8;
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
|  1 | UPDATE      | t1    | NULL       | range | c2            | c2   | 4       | const |    2 |   100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+

mysql> desc  update t1 set c4=123 where c2>=6;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
|  1 | UPDATE      | t1    | NULL       | index | c2            | PRIMARY | 4       | NULL |    7 |   100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+

下面兩個語句的執行計劃不一致，主要注意
type:index和range
key:PRIMARY和c2

我們先要清楚type:index和range的區別
這裡借用我以前寫的一篇文章
http://blog.itpub.net/7728585/viewspace-2139010/

type:index 不使用索引B+樹結構,只使用索引葉子結點連結串列結構進行掃描，我們知道在索引的葉子結點有一個葉子結點之間的雙向指標，
並且葉子結點的資料是排序好的。他和ALL的方式類似，訪問效率並不高，其主要的應用場景為用於避免order by使用using filesort
也就是避免排序。他是一種訪問資料的方式，和range、const、ref、eq_ref等一樣。
type:range 顯然用於範圍查詢比如> between 等，其訪問方式是考慮到索引的B+樹結構的，需要通過根結點-->分支節點-->葉子結點的順序訪問
其實const、ref、eq_ref等一樣也需要這樣的定位過程。

我大概畫一個圖，示意圖而已，但是足以解釋我的意思

1.jpg

剩下我們需要考慮RR模式下，如下語句有哪些所結構：

mysql> desc  update t1 set c4=123 where c2>=6;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
|  1 | UPDATE      | t1    | NULL       | index | c2            | PRIMARY | 4       | NULL |    7 |   100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+

RECORD LOCKS space id 532 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 348084 lock_mode X(LOCK_X) 
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000000; asc     ;;
 1: len 6; hex 000000054abd; asc     J ;;
 2: len 7; hex ba00000e180110; asc        ;;
 3: len 4; hex 00000000; asc     ;;
 4: len 4; hex 00000000; asc     ;;
 5: len 4; hex 00000000; asc     ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 000000054abd; asc     J ;;
 2: len 7; hex ba00000e18011d; asc        ;;
 3: len 4; hex 00000001; asc     ;;
 4: len 4; hex 00000001; asc     ;;
 5: len 4; hex 00000000; asc     ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 6; hex 000000054abd; asc     J ;;
 2: len 7; hex ba00000e18012a; asc       *;;
 3: len 4; hex 00000003; asc     ;;
 4: len 4; hex 00000003; asc     ;;
 5: len 4; hex 00000000; asc     ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000004; asc     ;;
 1: len 6; hex 000000054abd; asc     J ;;
 2: len 7; hex ba00000e180137; asc       7;;
 3: len 4; hex 00000002; asc     ;;
 4: len 4; hex 00000002; asc     ;;
 5: len 4; hex 00000000; asc     ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000006; asc     ;;
 1: len 6; hex 000000054fb4; asc     O ;;
 2: len 7; hex 3300000c430b49; asc 3   C I;;
 3: len 4; hex 00000008; asc     ;;
 4: len 4; hex 00000005; asc     ;;
 5: len 4; hex 0000007b; asc    {;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000007; asc     ;;
 1: len 6; hex 000000054fb4; asc     O ;;
 2: len 7; hex 3300000c430b6b; asc 3   C k;;
 3: len 4; hex 00000006; asc     ;;
 4: len 4; hex 00000006; asc     ;;
 5: len 4; hex 0000007b; asc    {;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 0000000a; asc     ;;
 1: len 6; hex 000000054fb4; asc     O ;;
 2: len 7; hex 3300000c430b8d; asc 3   C  ;;
 3: len 4; hex 0000000a; asc     ;;
 4: len 4; hex 00000004; asc     ;;
 5: len 4; hex 0000007b; asc    {;;

我們這裡先不考慮表級意向鎖，只考慮這裡列印出來的鎖結構
行鎖為：lock_mode X(LOCK_X)|LOCK_ORDINARY(next key lock)
同時我們注意到 0: len 8; hex 73757072656d756d; asc supremum
那麼我們用一張圖來表示

2.jpg

實際上我們從圖中可以看出這種情況下RR模式下是主鍵上所有的行都加上了NEXT_KEY LOCK,所以你其他任何DML操作都會鎖定

那麼如下語句的鎖結構呢？

mysql> desc  update t1 set c4=123 where c2>=8;
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
|  1 | UPDATE      | t1    | NULL       | range | c2            | c2   | 4       | const |    2 |   100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
1 row in set (0.01 sec)

如下：

-----TRX NO:348661 LOCK STRUCT(1)(Add by gaopeng)
TABLE LOCK table `test`.`t1` trx id 348661 lock mode IX
-----TRX NO:348661 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 532 page no 4 n bits 80 index c2 of table `test`.`t1` trx id 348661 lock_mode X(LOCK_X)
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000008; asc     ;;
 1: len 4; hex 00000006; asc     ;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 0000000a; asc     ;;
 1: len 4; hex 0000000a; asc     ;;

-----TRX NO:348661 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 532 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 348661 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000006; asc     ;;
 1: len 6; hex 0000000551f5; asc     Q ;;
 2: len 7; hex 71000002700ad1; asc q   p  ;;
 3: len 4; hex 00000008; asc     ;;
 4: len 4; hex 00000005; asc     ;;
 5: len 4; hex 0000007b; asc    {;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 0000000a; asc     ;;
 1: len 6; hex 0000000551f5; asc     Q ;;
 2: len 7; hex 71000002700af3; asc q   p  ;;
 3: len 4; hex 0000000a; asc     ;;
 4: len 4; hex 00000004; asc     ;;
 5: len 4; hex 0000007b; asc    {;;

我們可以清晰的觀察到INDEX c2上包含
lock_mode X(LOCK_X)|LOCK_ORDINARY(next key lock)
其行包含了 C2:8/C1:6 C2:10/C2:10 還包含 supremum
同時傳遞到了主鍵PRIMARY鎖結構為
lock_mode X(LOCK_X)|rec but not gap(LOCK_REC_NOT_GAP)
也就是主鍵上只是鎖定了C1:6 C1:10這兩行，並且不是gap lock，如果需要畫圖就是如下:

3.jpg

我們可以發現鎖定的範圍小了很多很多，這種情況如下語句：
select * from t1 where c1 = 7 for update;
(這裡葉老師寫的c2=7不知道是不是寫錯了)
是可以完成的，因為不會落到PRIMARY的鎖定範圍內。

三、RC模式下的鎖定模式

這裡只是看看RC模式的鎖定結構如下：

mysql> desc  update t1 set c4=123 where c2>=6;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
|  1 | UPDATE      | t1    | NULL       | index | c2            | PRIMARY | 4       | NULL |    7 |   100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
1 row in set (0.22 sec)

-----TRX NO:348596 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 532 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 348596 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000006; asc     ;;
 1: len 6; hex 0000000551b4; asc     Q ;;
 2: len 7; hex 3300000c430c03; asc 3   C  ;;
 3: len 4; hex 00000008; asc     ;;
 4: len 4; hex 00000005; asc     ;;
 5: len 4; hex 0000007b; asc    {;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000007; asc     ;;
 1: len 6; hex 0000000551b4; asc     Q ;;
 2: len 7; hex 3300000c430c25; asc 3   C %;;
 3: len 4; hex 00000006; asc     ;;
 4: len 4; hex 00000006; asc     ;;
 5: len 4; hex 0000007b; asc    {;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 0000000a; asc     ;;
 1: len 6; hex 0000000551b4; asc     Q ;;
 2: len 7; hex 3300000c430c47; asc 3   C G;;
 3: len 4; hex 0000000a; asc     ;;
 4: len 4; hex 00000004; asc     ;;
 5: len 4; hex 0000007b; asc    {;;

我們可以清晰的看到RC模式下不考慮隱含鎖的情況下只是鎖定了PRIMARY的相應的行：
lock_mode X(LOCK_X) locks|rec but not gap(LOCK_REC_NOT_GAP)
注意這裡NOT GAP

四、總結

就如葉老師所說，執行計劃居然影響了鎖的範圍，聽起來不可能但是他確實就是，所以大家RR模式下應該注意這一點，
同時這裡RC模式下

 update t1 set c4=123 where c2>=6;

不堵塞實際上是SEMI UPDATE的功勞，如果RR模式下開啟引數innodb_locks_unsafe_for_binlog也不會堵塞
但是至少大家應該如葉老師所說的那麼去做，特別是RR模式下。
這裡再次感謝葉金榮老師給我們帶來這麼有趣的案例。

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

RR模式下NEXT-KEY LOCK範圍到底有多大
2016-10-13
模式
記錄一下MySql update會鎖定哪些範圍的資料
2022-06-24
MySql
MySQL:RR模式下死鎖一列
2020-06-21
MySql模式
請問老師一個關於命令模式的問題
2007-10-10
模式
MySQL 唯一索引範圍查詢鎖下一個記錄的理解
2022-09-22
MySql索引
請彭老師進來,切實解決一下我的一個困惑,關於本書
2004-09-22
UPDATE查詢結果範圍內的資料
2013-09-23
MySQL:關於RR模式下insert..selcet sending data狀態說明
2020-06-21
MySql模式
MySQL：RR分析死鎖一列
2019-03-14
MySql
bang老師請一定要看我的這個問題關於main方法的
2010-12-15
AI
關於學習jdon框架cache的疑問，請banq老師指點一下。
2005-08-17
框架
一個老工程師給年輕人的十大忠告
2007-10-12
工程師
30個關於Shell指令碼的經典案例（下）
2019-09-14
指令碼
一個關於prototype模式的問題？
2002-11-26
模式
關於聯合索引，範圍查詢，時間列索引的幾個問題
2021-04-23
索引
banq老師我想問一個很笨的問題關於#會話#跟蹤#的
2007-01-17
會話
RR與RC隔離級別下MySQL不同的加鎖解鎖方式
2018-05-10
MySql
“老師”的商業模式
2017-06-25
模式
一個關於wait/notify與鎖關係的探究
2022-03-28
AI
關於RAC環境下鎖查詢的測試案例1-1
2009-01-07
關於RAC環境下鎖查詢的測試案例1-2
2009-01-07
Linux下關於互斥鎖及同步的移植（一）
2014-06-17
Linux
微軟宣佈將擴大Windows 10 IoT Core裝置支援範圍
2018-03-01
微軟Windows
關於SLG中人物可到達範圍計算的想法(轉)
2007-08-12
關於一個建立型模式的討論：
2003-05-16
模式
關於singleton模式的一個問題？
2002-12-04
模式
關於責任鏈模式的一個例子
2010-01-09
模式
MySQL:Innodb 一個死鎖案例
2018-09-25
MySql
一個“老”程式設計師的思考
2016-10-27
程式設計師
一個老師的理解,這就是OOP
2009-01-08
OOP
如何通過rownum對錶的不同範圍進行批量更新update
2012-11-10
JOL探索synchronized鎖-子路老師
2020-12-06
synchronized
Linux下關於互斥鎖及同步的移植（一）薦
2009-11-25
Linux
看了潘愛民老師的關於smartcache for webkit的paper
2012-04-11
WebKit
關於Chain of Responsibility模式的一個問題
2002-11-20
AI模式
一個簡單的MVP模式案例
2018-04-13
MVP模式
如何提取oracle timestamp列一個範圍的資料
2013-02-28
Oracle
[LeetCode] Search for a Range 搜尋一個範圍
2015-04-09
LeetCode