讀5.7官網文檔之14.7.3 Locks Set by Different SQL Statements in InnoDB 之insert篇

并非翻譯，是看了官網文檔以后的讀書筆記弦聂，如有錯誤，歡迎大家指出揭蜒。

insert加的鎖

insert會分兩步加鎖横浑，先加一個insert intention lock剔桨，第二步會在插入行上面添加一個排他鎖屉更。如果插入的行上已經有了排他鎖，導致獲取不到排他鎖就會轉成請求插入行的share鎖洒缀，繼續(xù)等待瑰谜。如果獲取到share鎖欺冀，再升級為排他鎖。接下來來驗證下是不是這樣的萨脑。

實驗相關背景條件

root@localhost>select @@version,@@transaction_isolation from dual;
+------------+-------------------------+
| @@version  | @@transaction_isolation |
+------------+-------------------------+
| 5.7.22-log | REPEATABLE-READ         |
+------------+-------------------------+

root@localhost [(none)] 14:49:58>show create table test.t;
+-------+-------------------------------------------------+
| Table | Create Table                                    |
+-------+-------------------------------------------------+
| t     | CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------+

實驗一

root@localhost>begin;
Query OK, 0 rows affected (0.00 sec)
root@localhost>insert into t() values(5,6);
Query OK, 1 row affected (0.01 sec)

在看show engine innodb status

---TRANSACTION 1551519, ACTIVE 47 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 24771, OS thread handle 140374083041024, query id 12402642 localhost root
TABLE LOCK table `test`.`t` trx id 1551519 lock mode IX

上面的語句根本看不到插入意向鎖和行的排他鎖隐轩，只能看到一個表級別的意向鎖。

實驗二

既然看不到渤早，就堵塞你职车，看看能不能看到。

先生成一個gap鎖鹊杖。

###會話一
root@localhost>begin;
Query OK, 0 rows affected (0.00 sec)

root@localhost>select * from t;
+----+------+
| a  | b    |
+----+------+
|  1 |    2 |
|  2 |    3 |
|  3 |    4 |
|  4 |    5 |
| 11 |   22 |
+----+------+
5 rows in set (0.01 sec)
root@localhost>update t set b = 10 where a = 5;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

在看show engine innodb status日志

2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 24771, OS thread handle 140374083041024, query id 12409482 localhost root
Trx read view will not see trx with id >= 1551536, sees < 1551536
TABLE LOCK table `test`.`t` trx id 1551536 lock mode IX
RECORD LOCKS space id 1483 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 1551536 lock_mode X locks gap before rec. 
###上面一行顯示在主鍵的某條記錄前面加上了一個排他的gap鎖
###下面這個告知了具體是哪一行
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;  ###獲取hex值的后面四位悴灵，然后轉換為10進制，可以得到值是11骂蓖，也就是說把主鍵值為11前面的gap鎖住了积瞒。這里多謝葉金榮老師
 1: len 6; hex 00000017a692; asc       ;;
 2: len 7; hex fe0000003b0137; asc     ; 7;;
 3: len 4; hex 80000016; asc     ;;

然后我們再執(zhí)行insert語句，照理來說insert語句會因為可重復讀被堵住登下。

###會話二
root@localhost [test] 15:18:38>begin;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test] 15:18:40>insert into t() values(5,6);

會話二如愿被阻塞了茫孔，再來看一下具體什么鎖在等待

---TRANSACTION 1551603, ACTIVE 58 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 24777, OS thread handle 140374067091200, query id 12416225 localhost root update
insert into t() values(5,6)
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1483 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 1551603 lock_mode X locks gap before rec insert intention waiting
##下面這行具體是指主鍵值是11的行，也就是說是在請求11和4之間的insert intention gap鎖
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;##表示主鍵值是11的行記錄被芳，把hex值的后四位即000b轉換為10進制可得值為11
 1: len 6; hex 00000017a692; asc       ;;
 2: len 7; hex fe0000003b0137; asc     ; 7;;
 3: len 4; hex 80000016; asc     ;;

我把會話一提交了缰贝，照理來說會話二應該接下來持有的是被插入行也就是（5，6）這一行的排他鎖畔濒，但是從innodb status當中沒有看到揩瞪，還是只能看到插入意向鎖。

實驗三

既然看不到行的排他鎖篓冲，只能在試試看別的方法了

###會話一
root@localhost >begin;
Query OK, 0 rows affected (0.00 sec)
root@localhost >insert into t() values(5,6);
###會話二
root@localhost >begin;
Query OK, 0 rows affected (0.00 sec)
root@localhost>insert into t() values(5,6);

同時在兩個會話當中插入同一行數(shù)據李破。再來看看鎖的等待情況。由于會話一沒有提交壹将，所以會話二是不會馬上報重復值的錯的,而是被阻塞住嗤攻。

---TRANSACTION 1551610, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 24771, OS thread handle 140374083041024, query id 12425387 localhost root update
insert into t() values(5,6)
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
##下面這個表示正在等待主鍵值為5的這一行的
RECORD LOCKS space id 1483 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 1551610 lock mode S waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000017acf3; asc       ;;
 2: len 7; hex f10000003a0110; asc     :  ;;
 3: len 4; hex 80000006; asc     ;;

---TRANSACTION 1551603, ACTIVE 1001 sec
3 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 24777, OS thread handle 140374067091200, query id 12419372 localhost root
TABLE LOCK table `test`.`t` trx id 1551603 lock mode IX
RECORD LOCKS space id 1483 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 1551603 lock_mode X locks gap before rec insert intention
##這里還是顯示11之前的gap被插入意向鎖占著呢
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 00000017a692; asc       ;;
 2: len 7; hex fe0000003b0137; asc     ; 7;;
 3: len 4; hex 80000016; asc     ;;
##下面表示主鍵值為5的這一行被加上了排他鎖
RECORD LOCKS space id 1483 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 1551603 lock_mode X locks rec but not gap
Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;; #這里的hex值0005轉換為10進制就是5
 1: len 6; hex 00000017acf3; asc       ;;
 2: len 7; hex f10000003a0110; asc     :  ;;
 3: len 4; hex 80000006; asc     ;;

上面的日志中可以看到會話一在被插入行上加了排他鎖，會話二由于插入的重復數(shù)據诽俯，所以變成了請求插入上的行共享鎖妇菱，和文檔描述一致。

總結

通過三個實驗暴区，終于看到官網文檔所描述的鎖了闯团，先嘗試加插入意向鎖，再加上行的排他鎖仙粱，如果行已經被加上了排他鎖房交，會變成請求行共享鎖繼續(xù)等待。不過要吐槽的還是這個鎖的查看機制伐割，只能通過show engine innodb status才能看的清楚候味。要是通過表的話刃唤，感覺會方便好多。