MySQL中的鎖3-行鎖的實現(xiàn)

行鎖的實現(xiàn)

在Innodb存儲引擎中刑峡，行鎖有3種實現(xiàn)方式斜纪，分別是：

Record Lock
Gap Lock
Next-Key Lock

Record Lock鎖住單個記錄乳蓄，并且Record Lock總是會去鎖主索引記錄眶诈，如果建表時沒有設置任何索引褂微，則Innodb會隱式的創(chuàng)建一個主鍵并進行鎖定；
Gap Lock鎖住的是一個范圍嗤堰，但是不包含記錄本身戴质；
Next-Key Lock=Gap Lock+Record Lock，鎖定包含記錄本身的范圍踢匣，所以Next-Key Lock是一種特殊的Gap Lock告匠。

例如，a表中只有記錄 2离唬、 5 后专、 9，且在記錄5上加鎖输莺∑莅ィ看下針對上面行鎖三種不同的實現(xiàn)方式是如何具體加鎖的。

Record Lock只會鎖住記錄5本身嫂用；
Gap Lock鎖住范圍(2,5)型凳，不包括邊界，依然可以對記錄2和5進行修改或刪除尸折，但是不能插入3啰脚、4等在鎖住范圍的值；
Next-Key Lock鎖住(2,5]范圍实夹，不包括記錄2但是包括記錄5本身橄浓。不能插入在2-5之間的數(shù)，可以刪除或修改記錄2亮航，但是記錄5不能修改和刪除荸实。

Next-Key Lock主要是用來防止幻讀。在Innodb中缴淋，Repeatable-Read隔離級別大量使用了Next-Key Lock和Gap Lock准给，而Read-Committed隔離級別使用的行鎖基本都是Record Lock泄朴。

在查詢的列是唯一索引且查詢條件是等值查詢時，Next-Key Lock會降級為Record Lock露氮。

簡單SQL語句的加鎖分析

首先要清楚無法單純就一條SQL語句分析出其加鎖情況胎许，必須還要知道該SQL的一些前提條件葛虐，這些前提條件包括：（insert\delete\update操作都會隱式地包含一個查詢操作窄刘，所以這里我把SQL語句中where條件中的列稱之為查詢列）

當前事務的隔離級別是什么拘泞；
查詢列是不是主鍵；
查詢列上是否有二級索引叁扫；
查詢列上有二級索引的話三妈，該索引是否唯一。

基于上面的前提條件莫绣，我們來分析如下情況：

RC隔離級別下畴蒲，查詢列是主鍵；
RC隔離級別下对室，查詢列是二級唯一索引模燥；
RC隔離級別下，查詢列是二級非唯一索引软驰；
RC隔離級別下涧窒，查詢列上沒有索引；
RR隔離級別下锭亏，查詢列是主鍵；
RR隔離級別下硬鞍，查詢列是二級唯一索引慧瘤；
RR隔離級別下，查詢列是二級非唯一索引固该；
RR隔離級別下锅减，查詢列上沒有索引；

針對上面列舉的情況伐坏，我們通過查詢語句+for update來演示怔匣，每種情況下演示分別等值查詢和范圍查詢。

數(shù)據(jù)準備

mysql> show create table t3\G;
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `b` (`b`),
  KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

mysql> select * from t3;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 |    3 |    5 |    7 |
| 3 |    5 |    7 |    9 |
| 5 |    7 |    9 |   11 |
| 7 |    9 |   11 |   13 |
+---+------+------+------+

1. RC隔離級別下桦沉，查詢列是主鍵

select * from t3 where a =3 for update;
此種情況會在a=3的記錄上加record lock

---TRANSACTION 4393, ACTIVE 21 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 2, OS thread handle 140018686076672, query id 162 localhost root
TABLE LOCK table `test`.`t3` trx id 4393 lock mode IX
RECORD LOCKS space id 39 page no 3 n bits 80 index PRIMARY of table `test`.`t3` trx id 4393 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;; --第一個字段是主鍵3每瞒，占用4個字節(jié)，被鎖住了
 1: len 6; hex 000000000f70; asc      p;; --該字段為6個字節(jié)的事務id纯露，這個id表示最近一次被更新的事務id
 2: len 7; hex cc00000157011c; asc     W  ;; --該字段為7個字節(jié)的回滾指針剿骨，用于mvcc
 3: len 4; hex 80000005; asc     ;; --該字段表示的是此記錄的第二個字段5
 4: len 4; hex 80000007; asc     ;; --該字段表示的是此記錄的第三個字段7
 5: len 4; hex 80000009; asc     ;; --該字段表示的是此記錄的第四個字段9

select * from t3 where a <=3 for update;
此情況下，會加兩個record lock鎖住a=1和a=3的記錄埠褪，具體如下：

---TRANSACTION 4394, ACTIVE 5 sec
2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 2, OS thread handle 140018686076672, query id 166 localhost root
TABLE LOCK table `test`.`t3` trx id 4394 lock mode IX
RECORD LOCKS space id 39 page no 3 n bits 80 index PRIMARY of table `test`.`t3` trx id 4394 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc000001570110; asc     W  ;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000007; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc00000157011c; asc     W  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;

2. RC隔離級別下浓利，查詢列是二級唯一索引

select * from t3 where b =5 for update;
此情況會有2把鎖挤庇，b=5的二級索引上，a=3(b=5)的聚集索引上贷掖，具體如下：

---TRANSACTION 4400, ACTIVE 7 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 2, OS thread handle 140018686076672, query id 174 localhost root
TABLE LOCK table `test`.`t3` trx id 4400 lock mode IX
RECORD LOCKS space id 39 page no 4 n bits 80 index b of table `test`.`t3` trx id 4400 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;; -- 二級索引  key=5
 1: len 4; hex 80000003; asc     ;; -- value = 3（主鍵）

RECORD LOCKS space id 39 page no 3 n bits 80 index PRIMARY of table `test`.`t3` trx id 4400 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc00000157011c; asc     W  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;

select * from t3 where b <=5 for update;
此情況下會產(chǎn)生5把record lock嫡秕，二級索引（b=3、b=5苹威、b=7）3把昆咽，聚集索引（a=1，a=3）2把屠升，具體如下：

---TRANSACTION 4401, ACTIVE 6 sec
3 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 2, OS thread handle 140018686076672, query id 180 localhost root
TABLE LOCK table `test`.`t3` trx id 4401 lock mode IX
RECORD LOCKS space id 39 page no 4 n bits 80 index b of table `test`.`t3` trx id 4401 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000003; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 39 page no 3 n bits 80 index PRIMARY of table `test`.`t3` trx id 4401 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc000001570110; asc     W  ;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000007; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc00000157011c; asc     W  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;

3. RC隔離級別下潮改，查詢列是二級非唯一索引

select * from t3 where c = 7 for update;
此情況下，有兩把record lock腹暖，c=7對應的二級索引上和a=3對應的聚集索引上汇在。

---TRANSACTION 4402, ACTIVE 109 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 2, OS thread handle 140018686076672, query id 184 localhost root
TABLE LOCK table `test`.`t3` trx id 4402 lock mode IX
RECORD LOCKS space id 39 page no 5 n bits 80 index c of table `test`.`t3` trx id 4402 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000003; asc     ;;

RECORD LOCKS space id 39 page no 3 n bits 80 index PRIMARY of table `test`.`t3` trx id 4402 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc00000157011c; asc     W  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;

select * from t3 where c <= 7 for update;
此情況下，有5把record lock脏答。二級索引（c=5糕殉、c=7、c=9）3把殖告，聚集索引（a=1阿蝶，a=3）2把

---TRANSACTION 4403, ACTIVE 3 sec
3 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 2, OS thread handle 140018686076672, query id 189 localhost root
TABLE LOCK table `test`.`t3` trx id 4403 lock mode IX
RECORD LOCKS space id 39 page no 5 n bits 80 index c of table `test`.`t3` trx id 4403 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000003; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 39 page no 3 n bits 80 index PRIMARY of table `test`.`t3` trx id 4403 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc000001570110; asc     W  ;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000007; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc00000157011c; asc     W  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;

4. RC隔離級別下，查詢列上沒有索引

select * from t3 where d = 9 for update;
此情況下黄绩，由于d列上沒有索引羡洁，而鎖是加在索引上的，所以只會在a=3的聚集索引上加1把record lock爽丹，具體如下：

---TRANSACTION 4405, ACTIVE 5 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 2, OS thread handle 140018686076672, query id 194 localhost root
TABLE LOCK table `test`.`t3` trx id 4405 lock mode IX
RECORD LOCKS space id 39 page no 3 n bits 80 index PRIMARY of table `test`.`t3` trx id 4405 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc00000157011c; asc     W  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;

select * from t3 where d <= 9 for update;
此情況下筑煮，加2把record lock，鎖加載a=1(d=7)和a=3(d=9)對應的聚集索引上粤蝎，具體如下：

---TRANSACTION 4407, ACTIVE 2 sec
2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 2, OS thread handle 140018686076672, query id 200 localhost root
TABLE LOCK table `test`.`t3` trx id 4407 lock mode IX
RECORD LOCKS space id 39 page no 3 n bits 80 index PRIMARY of table `test`.`t3` trx id 4407 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc000001570110; asc     W  ;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000007; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc00000157011c; asc     W  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;

5. RR隔離級別下真仲，查詢列是主鍵

select * from t3 where a =3 for update;
加a=3的記錄上加記錄鎖（lock_mode X locks rec but not gap），具體如下：

2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 2, OS thread handle 140018686076672, query id 61 localhost root
TABLE LOCK table `test`.`t3` trx id 4365 lock mode IX
RECORD LOCKS space id 39 page no 3 n bits 72 index PRIMARY of table `test`.`t3` trx id 4365 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc00000157011c; asc     W  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;

select * from t3 where a <= 5 for update;
會加多個范圍區(qū)間的next-key lock(lock_mode X)初澎，具體如下：

---TRANSACTION 4367, ACTIVE 41 sec
2 lock struct(s), heap size 1136, 4 row lock(s)
RECORD LOCKS space id 39 page no 3 n bits 72 index PRIMARY of table `test`.`t3` trx id 4367 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc000001570110; asc     W  ;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000007; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc00000157011c; asc     W  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000000f75; asc      u;;
 2: len 7; hex cf0000015a0110; asc     Z  ;;
 3: len 4; hex 80000007; asc     ;;
 4: len 4; hex 80000009; asc     ;;
 5: len 4; hex 8000000b; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 6; hex 000000000f75; asc      u;;
 2: len 7; hex cf0000015a011c; asc     Z  ;;
 3: len 4; hex 80000009; asc     ;;
 4: len 4; hex 8000000b; asc     ;;
 5: len 4; hex 8000000d; asc     ;;

根據(jù)上面鎖的詳情可以看出來秸应，其加鎖情況為：
(-∞,1],(1,3],(3,5],(5,7]
鎖住(5,7]是因為，在RR隔離級別下碑宴，<=5將從表的第一條記錄（a=1）開始比對软啼，一直到出現(xiàn)第一個大于記錄5（a=7）為止。

6. RR隔離級別下墓懂，查詢列是二級唯一索引

select * from t3 where b =5 for update;
會加兩個record lock(lock_mode X locks rec but not gap)：二級索引（b=5）和二級索引對應的聚集索引（a=3）上加鎖焰宣，具體如下：

---TRANSACTION 4368, ACTIVE 39 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 2, OS thread handle 140018686076672, query id 84 localhost root
TABLE LOCK table `test`.`t3` trx id 4368 lock mode IX
RECORD LOCKS space id 39 page no 4 n bits 72 index b of table `test`.`t3` trx id 4368 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000003; asc     ;;

RECORD LOCKS space id 39 page no 3 n bits 72 index PRIMARY of table `test`.`t3` trx id 4368 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc00000157011c; asc     W  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;

select * from t3 where b <=5 for update;
該情況會加5把鎖，3把next-key lock捕仔，鎖住區(qū)間(-∞,3],(3,5],(5,7]匕积。2把record lock盈罐，鎖住的是b=3對應的主鍵(a=1)和b=5對應的主鍵a=3。具體如下：

---TRANSACTION 4369, ACTIVE 6 sec
3 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 2, OS thread handle 140018686076672, query id 90 localhost root
TABLE LOCK table `test`.`t3` trx id 4369 lock mode IX
RECORD LOCKS space id 39 page no 4 n bits 72 index b of table `test`.`t3` trx id 4369 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000003; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 39 page no 3 n bits 72 index PRIMARY of table `test`.`t3` trx id 4369 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc000001570110; asc     W  ;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000007; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc00000157011c; asc     W  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;

7. RR隔離級別下闪唆，查詢列是二級非唯一索引

select * from t3 where c =7 for update;
該情況會上3把鎖盅粪，c=7對應的主鍵索引a=3上一把record lock，c的取值范圍(5,7]之間上一把next-key lock悄蕾，c的取值范圍(7,9)之間上一把gap lock票顾，具體如下

---TRANSACTION 4370, ACTIVE 5 sec
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 2, OS thread handle 140018686076672, query id 94 localhost root
TABLE LOCK table `test`.`t3` trx id 4370 lock mode IX
RECORD LOCKS space id 39 page no 5 n bits 72 index c of table `test`.`t3` trx id 4370 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000003; asc     ;;

RECORD LOCKS space id 39 page no 3 n bits 72 index PRIMARY of table `test`.`t3` trx id 4370 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc00000157011c; asc     W  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;

RECORD LOCKS space id 39 page no 5 n bits 72 index c of table `test`.`t3` trx id 4370 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000005; asc     ;;

select * from t3 where c <=7 for update;
該情況上5把鎖，3把next-key lock c的取值范圍(-∞,5],(5,7],(7,9]帆调；2把record lock奠骄，對應c=5主鍵a=1時的記錄和c=7主鍵a=3的記錄。具體如下：

---TRANSACTION 4371, ACTIVE 5 sec
3 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 2, OS thread handle 140018686076672, query id 98 localhost root
TABLE LOCK table `test`.`t3` trx id 4371 lock mode IX
RECORD LOCKS space id 39 page no 5 n bits 72 index c of table `test`.`t3` trx id 4371 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000003; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 39 page no 3 n bits 72 index PRIMARY of table `test`.`t3` trx id 4371 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc000001570110; asc     W  ;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000007; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc00000157011c; asc     W  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;

8. RR隔離級別下番刊，查詢列上沒有索引

select * from t3 where d =9 for update;
該情況下含鳞，由于沒有索引，所以其next-key lock的鎖住范圍(d的取值)為：(-∞,7],(7,9],(9,11],(11,13],(13,+∞] 相當于鎖住了整張表芹务。

---TRANSACTION 4386, ACTIVE 10 sec
2 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 2, OS thread handle 140018686076672, query id 125 localhost root
TABLE LOCK table `test`.`t3` trx id 4386 lock mode IX
RECORD LOCKS space id 39 page no 3 n bits 80 index PRIMARY of table `test`.`t3` trx id 4386 lock_mode 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 80000001; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc000001570110; asc     W  ;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000007; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000f70; asc      p;;
 2: len 7; hex cc00000157011c; asc     W  ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000000f75; asc      u;;
 2: len 7; hex cf0000015a0110; asc     Z  ;;
 3: len 4; hex 80000007; asc     ;;
 4: len 4; hex 80000009; asc     ;;
 5: len 4; hex 8000000b; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 6; hex 000000000f75; asc      u;;
 2: len 7; hex cf0000015a011c; asc     Z  ;;
 3: len 4; hex 80000009; asc     ;;
 4: len 4; hex 8000000b; asc     ;;
 5: len 4; hex 8000000d; asc     ;;

這里有一個明顯不一樣的是：

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

supremum 值得是頁里面的最后一條記錄(偽記錄蝉绷，通過select查不到的,并不是真實的記錄)，heap no=1 , Infimum 表示的是頁里面的第一個記錄（偽記錄）

可以簡單的認為：
supremum 為upper bounds枣抱，正去窮大
Infimum 為Minimal bounds熔吗，負無窮大
select * from t3 where d <=9 for update;和等值查詢情況一樣。

二級索引鎖住的范圍

二級索引鎖住的不僅僅是二級索引的Key本身佳晶，還有對應的value桅狠，也就是主鍵。
假如某條語句產(chǎn)生以下鎖定情況
(1, 3], （3轿秧， 6）以及主鍵索引只鎖住了a=5的這條記錄 [5, 5]
那么會有以下情況

參考
MySQL 加鎖處理分析
《MySQL技術內(nèi)幕：InnoDB存儲引擎》.[姜承堯]

?著作權歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末垂攘，一起剝皮案震驚了整個濱河市，隨后出現(xiàn)的幾起案子淤刃，更是在濱河造成了極大的恐慌，老刑警劉巖吱型，帶你破解...
沈念sama閱讀 222,590評論 6贊 517
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件逸贾，死亡現(xiàn)場離奇詭異，居然都是意外死亡津滞，警方通過查閱死者的電腦和手機铝侵，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 95,157評論 3贊 399
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來触徐，“玉大人咪鲜，你說我怎么就攤上這事∽拆模” “怎么了疟丙？”我有些...
開封第一講書人閱讀 169,301評論 0贊 362
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵颖侄，是天一觀的道長。經(jīng)常有香客問我享郊，道長览祖，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 60,078評論 1贊 300
?港島之戀（遺憾婚禮）
正文為了忘掉前任炊琉，我火速辦了婚禮展蒂，結果婚禮上，老公的妹妹穿的比我還像新娘苔咪。我一直安慰自己锰悼，他們只是感情好，可當我...
茶點故事閱讀 69,082評論 6贊 398
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布团赏。她就那樣靜靜地躺著箕般，像睡著了一般。火紅的嫁衣襯著肌膚如雪馆里。梳的紋絲不亂的頭發(fā)上隘世，一...
開封第一講書人閱讀 52,682評論 1贊 312
城市分裂傳說
那天，我揣著相機與錄音鸠踪，去河邊找鬼丙者。笑死，一個胖子當著我的面吹牛营密，可吹牛的內(nèi)容都是我干的械媒。我是一名探鬼主播，決...
沈念sama閱讀 41,155評論 3贊 422
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼评汰，長吁一口氣：“原來是場噩夢啊……” “哼纷捞！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起被去，我...
開封第一講書人閱讀 40,098評論 0贊 277
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤主儡，失蹤者是張志新（化名）和其女友劉穎，沒想到半個月后惨缆，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體糜值，經(jīng)...
沈念sama閱讀 46,638評論 1贊 319
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 38,701評論 3贊 342
?白月光啟示錄
正文我和宋清朗相戀三年坯墨，在試婚紗的時候發(fā)現(xiàn)自己被綠了寂汇。大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
茶點故事閱讀 40,852評論 1贊 353
活死人
序言：一個原本活蹦亂跳的男人離奇死亡捣染，死狀恐怖骄瓣，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情耍攘，我是刑警寧澤榕栏，帶...
沈念sama閱讀 36,520評論 5贊 351
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布畔勤，位于F島的核電站，受9級特大地震影響臼膏，放射性物質(zhì)發(fā)生泄漏硼被。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點故事閱讀 42,181評論 3贊 335
男人毒藥：我在死后第九天來索命
文/蒙蒙一渗磅、第九天我趴在偏房一處隱蔽的房頂上張望嚷硫。院中可真熱鬧，春花似錦始鱼、人聲如沸仔掸。這莊子的主人今日做“春日...
開封第一講書人閱讀 32,674評論 0贊 25
一樁弒父案医清，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽起暮。三九已至，卻和暖如春会烙，著一層夾襖步出監(jiān)牢的瞬間负懦，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 33,788評論 1贊 274
情欲美人皮
我被黑心中介騙來泰國打工柏腻，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留纸厉，地道東北人。一個月前我還...
沈念sama閱讀 49,279評論 3贊 379
代替公主和親
正文我出身青樓五嫂，卻偏偏與公主長得像颗品，于是被迫代替她去往敵國和親。傳聞我的和親對象是個殘疾皇子沃缘，可洞房花燭夜當晚...
茶點故事閱讀 45,851評論 2贊 361