溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

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

發(fā)布時間：2021-11-18 14:53:40 來源：億速云 閱讀：153 作者：iii 欄目：MySQL數(shù)據(jù)庫

本篇內(nèi)容介紹了“mysql中一個RR模式下UPDATE鎖范圍擴大案例分析”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細閱讀，能夠?qū)W有所成！

一、前言

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

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模式下的鎖模式

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

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

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

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

  1. type:index 不使用索引B+樹結(jié)構(gòu),只使用索引葉子結(jié)點鏈表結(jié)構(gòu)進行掃描，我們知道在索引的葉子結(jié)點有一個葉子結(jié)點之間的雙向指針，
    并且葉子結(jié)點的數(shù)據(jù)是排序好的。他和ALL的方式類似，訪問效率并不高，其主要的應(yīng)用場景為用于避免order by使用using filesort
    也就是避免排序。他是一種訪問數(shù)據(jù)的方式，和range、const、ref、eq_ref等一樣。

  2. type:range 顯然用于范圍查詢比如> between 等，其訪問方式是考慮到索引的B+樹結(jié)構(gòu)的，需要通過根結(jié)點-->分支節(jié)點-->葉子結(jié)點的順序訪問
    其實const、ref、eq_ref等一樣也需要這樣的定位過程。

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

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

1.jpg

剩下我們需要考慮RR模式下，如下語句有哪些所結(jié)構(gòu)：

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    {;;

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

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

2.jpg

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

那么如下語句的鎖結(jié)構(gòu)呢？

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鎖結(jié)構(gòu)為
lock_mode X(LOCK_X)|rec but not gap(LOCK_REC_NOT_GAP)
也就是主鍵上只是鎖定了C1:6 C1:10這兩行，并且不是gap lock，如果需要畫圖就是如下:

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

3.jpg


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

三、RC模式下的鎖定模式

這里只是看看RC模式的鎖定結(jié)構(gòu)如下：

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的相應(yīng)的行：
lock_mode X(LOCK_X) locks|rec but not gap(LOCK_REC_NOT_GAP)
注意這里NOT GAP

“mysql中一個RR模式下UPDATE鎖范圍擴大案例分析”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！

向AI問一下細節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手