關於葉老師一個RR模式下UPDATE鎖範圍擴大案例的研究
原創轉載請註明出處有誤請指出
一、前言
葉金榮老師分享了一篇文章如下:
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範圍到底有多大模式
- MySQL:RR模式下死鎖一列MySql模式
- 記錄一下MySql update會鎖定哪些範圍的資料MySql
- 請問老師一個關於命令模式的問題模式
- MySQL 唯一索引範圍查詢鎖下一個記錄的理解MySql索引
- 請彭老師進來,切實解決一下我的一個困惑,關於本書
- MySQL:關於RR模式下insert..selcet sending data狀態說明MySql模式
- UPDATE查詢結果範圍內的資料
- MySQL：RR分析死鎖一列MySql
- bang老師請一定要看我的這個問題 關於main方法的AI
- 關於學習jdon框架cache的疑問，請banq老師指點一下。框架
- 一個老工程師給年輕人的十大忠告工程師
- 30個關於Shell指令碼的經典案例（下）指令碼
- 一個關於prototype模式的問題？模式
- 關於聯合索引，範圍查詢，時間列索引的幾個問題索引
- banq老師 我想問一個很笨的問題關於#會話#跟蹤#的會話
- “老師”的商業模式模式
- 一個關於wait/notify與鎖關係的探究AI
- RR與RC隔離級別下MySQL不同的加鎖解鎖方式MySql
- 關於RAC環境下鎖查詢的測試案例1-1
- 關於RAC環境下鎖查詢的測試案例1-2
- Linux下關於互斥鎖及同步的移植（一）Linux
- 關於SLG中人物可到達範圍計算的想法(轉)
- 微軟宣佈將擴大Windows 10 IoT Core裝置支援範圍微軟Windows
- 關於一個建立型模式的討論：模式
- 關於singleton模式的一個問題？模式
- 關於責任鏈模式的一個例子模式
- 一個“老”程式設計師的思考程式設計師
- 一個老師的理解,這就是OOPOOP
- MySQL:Innodb 一個死鎖案例MySql
- Linux下關於互斥鎖及同步的移植（一）薦Linux
- 如何通過rownum對錶的不同範圍進行批量更新update
- JOL探索synchronized鎖-子路老師synchronized
- 看了潘愛民老師的關於smartcache for webkit的paperWebKit
- 關於Chain of Responsibility模式的一個問題AI模式
- 一個簡單的MVP模式案例MVP模式
- 關於shutdown的幾個模式模式
- 如何提取oracle timestamp列一個範圍的資料Oracle