并非翻译，是看了官网文档以后的读书笔记，如有错误，欢迎大家指出。
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才能看的清楚。要是通过表的话，感觉会方便好多。
网友评论