mysql中一個RR模式下UPDATE鎖范圍擴大案例分析

141次閱讀

共計 10014 個字符，預計需要花費 26 分鐘才能閱讀完成。

本篇內容介紹了“mysql 中一個 RR 模式下 UPDATE 鎖范圍擴大案例分析”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

一、前言

這里只研究下鎖的模式, 借用葉老師的表和語句

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 
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
| 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 
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| 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 
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| 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 
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
| 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 
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| 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

“mysql 中一個 RR 模式下 UPDATE 鎖范圍擴大案例分析”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！

正文完