Ⅰ、隐式锁vs显示锁
session1：
(root@localhost) [test]> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)
(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test]> insert into l values (16,18,20,22);
Query OK, 1 row affected (0.00 sec)
(root@localhost) [test]> show engine innodb status\G
...
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421305875781456, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 31220665, ACTIVE 24 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 1185, OS thread handle 139830020065024, query id 7781 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`l` trx id 31220665 lock mode IX
...
会发现插入的这条记录上没有锁,只能看到一把意向锁
session2：
(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test]> select * from l where a = 16 for update;
hang~~~ ???
session1：
(root@localhost) [test]> show engine innodb status\G
...
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421305875783280, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 31220670, ACTIVE 18 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1184, OS thread handle 139830453040896, query id 7783 localhost root statistics
select * from l where a = 16 for update
------- TRX HAS BEEN WAITING 18 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1358 page no 3 n bits 80 index PRIMARY of table `test`.`l` trx id 31220670 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000010; asc ;;
1: len 6; hex 000001dc63b9; asc c ;;
2: len 7; hex b4000001a10110; asc ;;
3: len 4; hex 80000012; asc ;;
4: len 4; hex 80000014; asc ;;
5: len 4; hex 80000016; asc ;;
------------------
TABLE LOCK table `test`.`l` trx id 31220670 lock mode IX
RECORD LOCKS space id 1358 page no 3 n bits 80 index PRIMARY of table `test`.`l` trx id 31220670 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000010; asc ;;
1: len 6; hex 000001dc63b9; asc c ;;
2: len 7; hex b4000001a10110; asc ;;
3: len 4; hex 80000012; asc ;;
4: len 4; hex 80000014; asc ;;
5: len 4; hex 80000016; asc ;;
---TRANSACTION 31220665, ACTIVE 252 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 1185, OS thread handle 139830020065024, query id 7781 localhost root
TABLE LOCK table `test`.`l` trx id 31220665 lock mode IX
RECORD LOCKS space id 1358 page no 3 n bits 80 index PRIMARY of table `test`.`l` trx id 31220665 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000010; asc ;;
1: len 6; hex 000001dc63b9; asc c ;;
2: len 7; hex b4000001a10110; asc ;;
3: len 4; hex 80000012; asc ;;
4: len 4; hex 80000014; asc ;;
5: len 4; hex 80000016; asc ;;
...
这里可以发现这条记录上的锁又出来了,为什么？
原因：innodb做了优化,这个锁叫隐式锁,这条记录不需要加锁就知道上面有锁,因为这条记录对应的事务还在事务活跃列表中
- 显式锁(explicit-lock)
select * from t where rowd = xxx for update； - 隐式锁(implicit-lock)
不创建锁对象若没有锁冲突,发生等待则转化为显示锁,这样锁的开销就进一步下降了,几乎很少
小结：
insert操作一开始是隐式锁,不创建锁对象,发生等待的时候才转化为显式锁,查到a=16这条记录在活跃事务列表中,就是没提交,说明上面有锁,这时候创建锁对象,即延迟创建锁对象,如果在延迟过程中,没有对这条记录加锁,就不用创建锁对象,这样就节省内存了
Ⅱ、插入意向锁
session1：
(root@localhost) [test]> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
(root@localhost) [test]> select * from l;
+----+------+------+------+
| a | b | c | d |
+----+------+------+------+
| 2 | 4 | 6 | 8 |
| 4 | 6 | 8 | 10 |
| 6 | 8 | 10 | 12 |
| 8 | 10 | 12 | 14 |
| 10 | 12 | 14 | 16 |
| 20 | 22 | 24 | 26 |
+----+------+------+------+
6 rows in set (0.00 sec)
(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test]> select * from l where a < 20 for update;
+----+------+------+------+
| a | b | c | d |
+----+------+------+------+
| 2 | 4 | 6 | 8 |
| 4 | 6 | 8 | 10 |
| 6 | 8 | 10 | 12 |
| 8 | 10 | 12 | 14 |
| 10 | 12 | 14 | 16 |
+----+------+------+------+
5 rows in set (0.00 sec)
session2：
(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test]> insert into l values (14 ,16, 18, 20);
~~~
session3：
(root@localhost) [test]> show engine innodb status\G
...
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421305875783280, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 31220676, ACTIVE 27 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 1184, OS thread handle 139830453040896, query id 7811 localhost root update
insert into l values (14 ,16, 18, 20)
------- TRX HAS BEEN WAITING 27 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1358 page no 3 n bits 80 index PRIMARY of table `test`.`l` trx id 31220676 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 000001dc63c1; asc c ;;
2: len 7; hex ba000001970110; asc ;;
3: len 4; hex 80000016; asc ;;
4: len 4; hex 80000018; asc ;;
5: len 4; hex 8000001a; asc ;;
------------------
TABLE LOCK table `test`.`l` trx id 31220676 lock mode IX
RECORD LOCKS space id 1358 page no 3 n bits 80 index PRIMARY of table `test`.`l` trx id 31220676 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 000001dc63c1; asc c ;;
2: len 7; hex ba000001970110; asc ;;
3: len 4; hex 80000016; asc ;;
4: len 4; hex 80000018; asc ;;
5: len 4; hex 8000001a; asc ;;
---TRANSACTION 31220675, ACTIVE 75 sec
2 lock struct(s), heap size 1136, 6 row lock(s)
MySQL thread id 1185, OS thread handle 139830020065024, query id 7809 localhost root
TABLE LOCK table `test`.`l` trx id 31220675 lock mode IX
RECORD LOCKS space id 1358 page no 3 n bits 80 index PRIMARY of table `test`.`l` trx id 31220675 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000001c1b939; asc 9;;
2: len 7; hex e0000001a80110; asc ;;
3: len 4; hex 80000004; asc ;;
4: len 4; hex 80000006; asc ;;
5: len 4; hex 80000008; asc ;;
篇幅原因省略下面不相关记录锁
...
这时候能看到插入意向锁了
gap before rec insert intention waiting
session1：
(root@localhost) [test]> commit;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [test]> show engine innodb status\G
...
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421305875783280, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421305875782368, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 31220677, ACTIVE 17 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 1184, OS thread handle 139830453040896, query id 7815 localhost root
TABLE LOCK table `test`.`l` trx id 31220677 lock mode IX
RECORD LOCKS space id 1358 page no 3 n bits 80 index PRIMARY of table `test`.`l` trx id 31220677 lock_mode X locks gap before rec insert intention
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 000001dc63c1; asc c ;;
2: len 7; hex ba000001970110; asc ;;
3: len 4; hex 80000016; asc ;;
4: len 4; hex 80000018; asc ;;
5: len 4; hex 8000001a; asc ;;
...
可以看到,对20这条记录加了一个gap锁,但是是insert intention的
睁大眼睛啊
session1：
(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test]> insert into l values (15, 17, 19, 20);
Query OK, 1 row affected (0.00 sec)
哦嚯,插入成功,美鸡鸡
Ⅲ、捋一下为什么可以插？
a列：2 4 6 8 10 20
step1：
20这条记录上有一个X锁,next-key-locking,锁住xxx...(10,20]这几个范围
step2：
插入14这条记录,会对20这条记录加一个gap锁,即(14,20),但是这个gap锁有个insert intention的属性
step3：
第一个事务commit,事务2持有了上面这把(14,20)的insert intention的gap锁
这时候插15是能插入的,就因为insert intention
gap锁是用来阻塞的,之前的理解(14,20)之间是不能插入15的,但是有了上面说的这个特性,就表示插入非阻塞,即允许插入,意义在于提升了插入性能
如果没有insert intention,那插入14时(14,20)上面就是加一个gap锁,事务1提交则事务2获取这个gap锁,插入15,是插不了的,性能下降了
tips：
插入14地时候为什么这里会阻塞呢？因为14要在20上加一个gap锁,为什么要加gap锁来判断到底能不能插,一条记录能不能插就看它后面这条记录上有没有锁,这个锁是不是gap的,如果是那就不能插,只是一个record锁那就能插,而这个例子20这条记录上本身是有gap的所以就等待了
总结：
- insert intention用来判断当前事务能否插入,并不阻塞后面其他线程在这个范围的插入操作,提升了并发插入的性能
- gap insert intention互相之间本身是兼容的
- insert在等待的时候(被阻塞)才会加gap insert intention锁,不等待是没任何锁的
- rc没有next-key-lock锁,没有上面的情况,锁住20表示只锁住记录本身,没有锁住一个范围,14是可以直接插的