mysql什么情况会加意向锁_MySQL中的锁4-插入意向锁和自增锁

插入意向锁(Insert Intention Lock)

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

普通的Gap Lock 不允许 在 (上一条记录，本记录) 范围内插入数据

插入意向锁Gap Lock 允许 在 (上一条记录，本记录) 范围内插入数据

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

插入的过程

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

找到 小于等于25的记录 ，这里是 10

找到 记录10的下一条记录 ，这里是 30

判断 下一条记录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。