乐趣区

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

说明:

(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

一、插入意向锁

1.1 认识插入意向锁

官方介绍:

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 即为插入意向锁

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

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

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

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

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

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

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

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 等待;

2.1 死锁复现

最近线上出现了死锁,原因就是基于 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

因此互相等待,行成环形等待

2.2 注意点

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

如:

事务开始前,数据情况如下:

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 锁、插入意向锁)

退出移动版