读5.7官网文档之14.7.3 Locks Set by Dif

并非翻译，是看了官网文档以后的读书笔记，如有错误，欢迎大家指出。

insert加的锁

insert会分两步加锁，先加一个insert intention lock，第二步会在插入行上面添加一个排他锁。如果插入的行上已经有了排他锁，导致获取不到排他锁就会转成请求插入行的share锁，继续等待。如果获取到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     ;;

然后我们再执行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);

同时在两个会话当中插入同一行数据。再来看看锁的等待情况。由于会话一没有提交，所以会话二是不会马上报重复值的错的,而是被阻塞住。

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

上面的日志中可以看到会话一在被插入行上加了排他锁，会话二由于插入的重复数据，所以变成了请求插入上的行共享锁，和文档描述一致。

总结

通过三个实验，终于看到官网文档所描述的锁了，先尝试加插入意向锁，再加上行的排他锁，如果行已经被加上了排他锁，会变成请求行共享锁继续等待。不过要吐槽的还是这个锁的查看机制，只能通过show engine innodb status才能看的清楚。要是通过表的话，感觉会方便好多。