记一次插入意向锁和NextKey引起的死锁

共计 7042 个字符，预计需要花费 18 分钟才能阅读完成。

说明：

（1）为方便测试，以下测试均使用 test 测试表，其表结构为：

              Table: test
Create Table: CREATE TABLE `test` (`id` bigint(20) NOT NULL,
  `oid` bigint(20) NOT NULL,
  `status` tinyint(2) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_oid` (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

（2）以下事物隔离级别均为 RR

官方介绍：

Insert Intention Locks

插入若干数据如下

mysql> select * from test;
+----+-----+--------+
| id | oid | status |
+----+-----+--------+
|  1 |   1 |      0 |
|  2 |   2 |      0 |
|  5 |   5 |      0 |
| 10 |  10 |      0 |
+----+-----+--------+

（1）按时序分别执行两个事物：

time	trx_a	trx_b
time_1	start transaction;
time_2		start transaction;
time_3	select * from test where oid = 5 for update ;
time_4		insert into test set id = 3, oid = 3;（锁等待，插入意向锁）

（2）show engine innodb status :

------------
TRANSACTIONS
------------
Trx id counter 57450
Purge done for trx's n:o < 57448 undo n:o < 0 state: running but idle
History list length 716
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479621176896, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 57449, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 123145316274176, query id 277 localhost root update
insert into test set id = 3, oid = 3
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1089 page no 4 n bits 80 index idx_oid of table `test`.`test` trx id 57449 lock_mode X locks gap before rec insert intention waiting
// 插入意向锁


Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000005; asc         ;;
 1: len 8; hex 8000000000000004; asc         ;;

------------------
---TRANSACTION 57448, ACTIVE 8 sec
4 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 4, OS thread handle 123145316552704, query id 276 localhost root cleaning up

其中 gap before rec insert intention waiting 即为插入意向锁

论 MySql InnoDB 如何通过插入意向锁控制并发插入介绍了插入意向锁的主要作用：提升并发插入能力。

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

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

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

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

但是需要注意：需要强调的是，虽然 插入意向锁 中含有 意向锁 三个字，但是它并不属于 意向锁 而属于 间隙锁 ，因为 意向锁 是表锁而插入意向锁 是行锁。

Mysql 锁详解（行锁、表锁、意向锁、Gap 锁、插入意向锁）给出了锁介绍及锁兼容情况，及常见的插入意向锁冲突。

注 1：兼容性和 加锁顺序有关系，因此兼容性表格不是对称的

注 2：此表格阅读方式为 按列读；

是否兼容	gap	insert intention	record	next-key
gap	是	是	是	是
insert intention	否	是	是	否
record	是	是	否	否
next-key	是	是	否	否

从图中可以看出，如果前一个事务 持有 gap 锁，或者 next-key 锁的时候，后一个事务如果想要持有 insert intention 锁的时候会不兼容，出现锁等待。

从 1.3 节可以看出，关于 insert intention 的锁等待有两种情况

case1: 事务 a 获得 gap lock；事务 b insert intention 等待

case2: 事务 a 获得 next-key lock; 事务 b insert intention 等待;

最近线上出现了死锁，原因就是基于 case2, 和 mysql 并发 insert 死锁问题——gap、插入意向锁冲突有一点类似，但是本人遇到是的，update 和 insert 操作在两个事务并发的时候死锁，具体如下：

事务开始前，数据情况如下：

mysql> select * from test;
+----+-----+--------+
| id | oid | status |
+----+-----+--------+
|  1 |   1 |      0 |
|  2 |   2 |      0 |
|  5 |   5 |      0 |
| 10 |  10 |      0 |
+----+-----+--------+

按照时序进行操作，出现死锁，如下：

time	trx_a	trx_b
time_1	start transaction;
time_2		start transaction;
time_3	update test set status = 1 where oid = 5;
time_4		update test set status = 1 where oid = 5; (锁等待)
time_5	insert into test set id = 4, oid = 5;	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

此时出现死锁，截图如下：

deadlock1.png

执行 show engine innodb status , 找到对应的 LATEST DETECTED DEADLOCK 如下（附部分解析）：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-07-08 19:55:23 0x700000d95000
*** (1) TRANSACTION:
TRANSACTION 57432, ACTIVE 44 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 3, OS thread handle 123145316274176, query id 176 localhost root updating

update test set status = 1 where oid = 5
// 事务 b 操作语句

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1089 page no 4 n bits 72 index idx_oid of table `test`.`test` trx id 57432 lock_mode X waiting
//RECORD LOCKS 表示记录锁,space id 为 1089,page 号 4，n bits 72 表示这个聚集索引记录锁结构上留有 72 个 Bit 位
// 表示事务 1 正在等待表 test 上的 idx_oid 的 X 锁; 本案例中其实是 Next-Key lock

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000005; asc         ;;
 1: len 8; hex 8000000000000005; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 57431, ACTIVE 59 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 4, OS thread handle 123145316552704, query id 177 localhost root update

insert into test set id = 4, oid = 5
// 事务 a 的操作 sql

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 1089 page no 4 n bits 72 index idx_oid of table `test`.`test` trx id 57431 lock_mode X
// 显示了事务 2 insert into test set id = 4, oid = 5 持有了 a =5 的 Lock mode X |LOCK_GAP 


Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000005; asc         ;;
 1: len 8; hex 8000000000000005; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1089 page no 4 n bits 72 index idx_oid of table `test`.`test` trx id 57431 lock_mode X locks gap before rec insert intention waiting
// 表示事务 2 的 insert 语句正在等待插入意向锁 lock_mode X locks gap before rec insert intention waiting (LOCK_X + LOCK_REC_GAP)
这里需要各位注意的是锁组合，类似 lock_mode X waiting ,lock_mode X,lock_mode X locks gap before rec insert intention waiting 是我们分析死锁的核心重点。Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000005; asc         ;;
 1: len 8; hex 8000000000000005; asc         ;;

*** WE ROLL BACK TRANSACTION (1)

如何阅读死锁日志有较为详细的介绍了如何解读死锁日志；关键解析如下：

(1)事务 b 解析

update test set status = 1 where oid = 5
// 事务 b 操作语句

* (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1089 page no 4 n bits 72 index idx_oid of table test.test trx id 57432 lock_mode X waiting
//RECORD LOCKS 表示记录锁,space id 为 1089,page 号 4，n bits 72 表示这个聚集索引记录锁结构上留有 72 个 Bit 位
// 表示事务 b 正在等待表 test 上的 idx_oid 的 X 锁; 本案例中其实是 Next-Key lock

(2)事务 a 解析

insert into test set id = 4, oid = 5
// 事务 a 的操作 sql

* (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 1089 page no 4 n bits 72 index idx_oid of table test.test trx id 57431 lock_mode X
// 显示了事务 2 insert into test set id = 4, oid = 5 持有了 a =5 的 Lock mode X |LOCK_GAP

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8000000000000005; asc ;;
1: len 8; hex 8000000000000005; asc ;;

* (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1089 page no 4 n bits 72 index idx_oid of table test.test trx id 57431 lock_mode X locks gap before rec insert intention waiting
// 表示事务 2 的 insert 语句正在等待插入意向锁 lock_mode X locks gap before rec insert intention waiting (LOCK_X + LOCK_REC_GAP)
这里需要各位注意的是锁组合，类似 lock_mode X waiting ,lock_mode X,lock_mode X locks gap before rec insert intention waiting 是我们分析死锁的核心重点。

具体分析如下：

time	trx_a	trx_b
time_1	start transaction;
time_2		start transaction;
time_3	update test set status = 1 where oid = 5; 由于 oid=5 的数据存在，申请获得 next-key lock，申请成功；
time_4		update test set status = 1 where oid = 5; (锁等待) 由于 oid=5 的数据存在，申请获取 next-key lock，但是此时已经有 next-key lock 因此等待中；
time_5	insert into test set id = 4, oid = 5; 申请插入意向锁，但是已经有个 next-key lock 存在，因此等待中；详询 1.3 中的兼容性	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 因此互相等待，行成环形等待

上述时序的两个并发操作并不一定死锁。

如：

事务开始前，数据情况如下：

mysql> select * from test;
+----+-----+--------+
| id | oid | status |
+----+-----+--------+
|  1 |   1 |      0 |
|  2 |   2 |      0 |
|  5 |   5 |      0 |
| 10 |  10 |      0 |
+----+-----+--------+

按照时序进行操作，不会出现死锁，如下：

time	trx_a	trx_b
time_1	start transaction;
time_2		start transaction;
time_3	update test set status = 1 where oid = 5;
time_4		update test set status = 1 where oid = 5; (锁等待)
time_5	insert into test set id = 6, oid = 5; //(注意，此时 id = 6 , 大于 5)	Query OK, 1 row affected

原因，插入的主键 id 大于 oid 对应的主键 id;

（以下属于个人理解）由于 next-key lock 中的 gap lock 可以分解为两个部分，（pre, 5）和（5，next）, pre 和 next 分别为 oid 索引的数据为 5 时的前一条数据和后一条数据；等待上锁的时候，先等待给（pre，5）上锁，其对应的主键 id 小于 oid = 5 时的数据的主键 id 5; 所以当事务 1 插入主键 id 大于 5 的数据时，不会造成锁等待；另插入意向锁实则为 gap 锁，当主键 id 大于 5 的数据时，此时的 gap 区间为（5，next）, 而事务 b 此时在等待给（pre, 5）上锁，因此不会产生锁等待，故执行成功。

发生死锁的时候是个很好的提升数据库能力的时候，总结解决思路：

（1）根据 show engine innodb status , 找出对应的死锁 sql，进而找到对应的事务完整执行情况；

（2）尤其注意当已存在 gap / next-key 时，申请 insert intention 产生的锁等待；

（3）借助 INNODB_LOCKS、INNODB_LOCK_WAITS 等表数据进一步分析。

[1] 如何阅读死锁日志

[2] mysql 并发 insert 死锁问题——gap、插入意向锁冲突

[3] Mysql 锁详解（行锁、表锁、意向锁、Gap 锁、插入意向锁）

一、插入意向锁

1.1 认识插入意向锁

1.2 插入意向锁的特性与作用

1.3 插入意向锁和其他锁的兼容性

二、死锁分析

2.1 死锁复现

2.2 注意点

三、小结

四、参考

Just My Socks（注册教程内含优惠码）