插入意向锁(Insert Intention Lock)

插入意向锁本质上可以看成是一个Gap Lock

• 普通的Gap Lock 不允许 在 （上一条记录，本记录） 范围内插入数据
• 插入意向锁Gap Lock 允许 在 （上一条记录，本记录） 范围内插入数据

插入意向锁的作用是为了提高并发插入的性能， 多个事务 同时写入 不同数据 至同一索引范围（区间）内，并不需要等待其他事务完成，不会发生锁等待。

插入的过程

假设现在有记录 10， 30， 50， 70 ；且为主键 ，需要插入记录 25 。

1. 找到 小于等于25的记录 ，这里是 10
2. 找到 记录10的下一条记录 ，这里是 30
3. 判断 下一条记录30 上是否有锁
   3.1 判断 30 上面如果 没有锁 ，则可以插入
   3.2 判断 30 上面如果有Record Lock，则可以插入
   3.3 判断 30 上面如果有Gap Lock/Next-Key Lock，则无法插入，因为锁的范围是 (10, 30) /（10, 30] ；在30上增加insert intention lock（ 此时处于waiting状态），当 Gap Lock / Next-Key Lock 释放时，等待的事物（ transaction）将被 唤醒 ，此时 记录30 上才能获得 insert intention lock ，然后再插入 记录25

注意：一个事物 insert 25 且没有提交，另一个事物 delete 25 时，记录25上会有 Record Lock

插入意向锁演示

数据准备

mysql> desc a;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| b     | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from a;
+----+
| b  |
+----+
| 10 |
| 11 |
| 13 |
| 20 |
+----+
4 rows in set (0.00 sec)

开启两个会话，两个会话事务的隔离级别都设置为REPEATABLE-READ

Time	会话A	会话B
1	begin	begin
2	select * from a where a<=13 for update
3		insert into a values (12) -- waiting...... （被阻塞了，在这里等待）

此时执行show engine innodb status\G语句会看到以下结果

---TRANSACTION 4424, ACTIVE 7 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 3, OS thread handle 140018685810432, query id 240 localhost root update
--等待插入的SQL
insert into a values(12)
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
--插入记录12的事物等待中（被终端会话A中的事物阻塞了），等待获得插入意向锁(lock_mode X locks gap before rec insert intention waiting)
RECORD LOCKS space id 37 page no 3 n bits 72 index PRIMARY of table `test`.`a` trx id 4424 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 000000001140; asc      @;;
 2: len 7; hex b400000128011c; asc     (  ;;

------------------
TABLE LOCK table `test`.`a` trx id 4424 lock mode IX
RECORD LOCKS space id 37 page no 3 n bits 72 index PRIMARY of table `test`.`a` trx id 4424 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 000000001140; asc      @;;
 2: len 7; hex b400000128011c; asc     (  ;;

---TRANSACTION 4423, ACTIVE 55 sec
2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 2, OS thread handle 140018686076672, query id 241 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`a` trx id 4423 lock mode IX
RECORD LOCKS space id 37 page no 3 n bits 72 index PRIMARY of table `test`.`a` trx id 4423 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000113f; asc      ?;;
 2: len 7; hex b3000001270110; asc     '  ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 000000001140; asc      @;;
 2: len 7; hex b4000001280110; asc     (  ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 000000001140; asc      @;;
 2: len 7; hex b400000128011c; asc     (  ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000001145; asc      E;;
 2: len 7; hex b70000012b0110; asc     +  ;;

Time	会话A	会话B
1	begin	begin
2	select * from a where a<=13 for update
3		insert into a values (12) -- waiting...... （被阻塞了，在这里等待）
4	commit
5		输出：Query OK, 1 row affected (17.40 sec) 前提条件是insert操作的锁没有超时

此时事务B插入成功但是还未commit，再执行show engine innodb status\G语句，会有以下输出：

---TRANSACTION 4425, ACTIVE 26 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 140018685810432, query id 247 localhost root
TABLE LOCK table `test`.`a` trx id 4425 lock mode IX
RECORD LOCKS space id 37 page no 3 n bits 72 index PRIMARY of table `test`.`a` trx id 4425 lock_mode X locks gap before rec insert intention
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 000000001140; asc      @;;
 2: len 7; hex b400000128011c; asc     (  ;;

从上面的输出可以看到在记录13上面加了一把插入意图锁（lock_mode X locks gap before rec insert intention）。
获得插入意图锁之后，我们就可以在11-13之间并发插入记录，而不需要一个事物等待另一事物，当所有相关的插入的事物都提交后， 13上的插入意向锁 便会释放。

自增锁(AUTO-INC Locks)

在InnoDB中，每个含有自增列的表都有一个自增长计数器。当对含有自增长计数器的表进行插入时，首先会执行select max(auto_inc_col) from t for update来得到计数器的值，然后再将这个值加1赋予自增长列。我们将这种方式称之为AUTO_INC Lock。

AUTO_INC Lock是一种特殊的表锁，它在完成对自增长值插入的SQL语句后立即释放，所以性能会比事务完成后释放锁要高。由于是表级别的锁，所以在并发环境下其依然存在性能问题。

从MySQL 5.1.22开始，InnoDB中提供了一种轻量级互斥量的自增长实现机制，同时InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式，进而提高自增长值插入的性能。innodb_autoinc_lock_mode和插入类型有关，在介绍它之前，我们先来看看都有哪些插入类型

• “INSERT-like” statements

  泛指所有的插入语句, 它包括 “simple-inserts”, “bulk-inserts”, 和 “mixed-mode inserts”.

• “Simple inserts”

  插入的记录行数是确定的：比如：insert into values，replace
  但是不包括： INSERT ... ON DUPLICATE KEY UPDATE.

• “Bulk inserts”

  插入的记录行数不能马上确定的，比如： INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA

• “Mixed-mode inserts”

  这些都是simple-insert，但是部分auto increment值给定或者不给定. 例子如下(where c1 is an AUTO_INCREMENT column of table t1):

  INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
  

  另外一种 “mixed-mode insert” 就是 INSERT ... ON DUPLICATE KEY UPDATE

介绍完插入类型之后，我们再来看看锁模式，即innodb_autoinc_lock_mode。