当前位置：首页 > news >正文

springboot如何保证几个insert语句在同一个事务里_dele锁te amp; insert 死

news 来源：原创 2024/5/19 23:11:50

delete & insert 死锁

一. 死锁信息

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-09-01 15:18:41 7ef63524f700
*** (1) TRANSACTION:
TRANSACTION 83329139031, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 9863052, OS thread handle 0x7ef640864700, query id 19582985062 172.16.158.198 merchant1 update
INSERT INTO mht_user_fund_quty_XXXX
       (id, gmt_create) values

            (19751548, now()), (18972066, now()), (19559993, now()), (20743625, now()), (19796843, now()), (19033019, now()), (19636160, now())
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2538 page no 4 n bits 568 index `PRIMARY` of table `merchant`.`mht_user_fund_quty_XXXX` trx id 83329139031 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 325 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 8; hex 8000000001217db1; asc      !} ;;
1: len 6; hex 001366cc94d0; asc   f   ;;
2: len 7; hex 35000008d515af; asc 5      ;;
3: len 5; hex 99a742f4a3; asc   B  ;;

*** (2) TRANSACTION:
TRANSACTION 83329139028, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1184, 343 row lock(s), undo log entries 13
MySQL thread id 9862919, OS thread handle 0x7ef63524f700, query id 19582985053 172.16.158.178 merchant1 updating
DELETE FROM mht_user_fund_quty_XXXX
        where id in
         (
19323879
         ,
19528620
         ,
19892711
         ,
20532275
         ,
19797795
         ,
19363049
			.....

*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 2538 page no 4 n bits 568 index `PRIMARY` of table `merchant`.`mht_user_fund_quty_reduce_lock` trx id 83329139028 lock_mode X			

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2538 page no 4 n bits 568 index `PRIMARY` of table `merchant`.`mht_user_fund_quty_XXXX` trx id 83329139028 lock_mode X waiting
Record lock, heap no 438 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 80000000012d627c; asc      -b|;;
1: len 6; hex 001366cdc157; asc   f  W;;
2: len 7; hex f8002000230084; asc     #  ;;
3: len 5; hex 99a742f4a9; asc   B  ;;

二. 死锁分析

和开发确认了下，两个事物分别都只有一条语句，就是说 delete 和 insert 都是独立事务。

通常情况，一个事物里既有 delete 又有 insert ，并发时很容易死锁。但是像这种单语句的死锁非常少见，而且从加锁的逻辑来说，是不可能出现死锁的。

先看下两条语句在统计信息中持有和等待锁的情况：

T1：

INSERT INTO mht_user_fund_quty_XXXX (id, gmt_create) values (19751548, now()).....

等待锁：primary 上 lock_mode X locks gap before rec insert intention

T2：

DELETE FROM mht_user_fund_quty_XXXX where id in (19323879,......)

等待锁：primary 上 lock_mode X

持有锁：primary 上 lock_mode X

能得到的有用信息只有以上这些，想分析出死锁的成因，还需要弄明白以下几个点：

T1 事务的插入意向锁被什么阻塞了？
T2 事务为什么会有阻塞 T1 事务？
T1 事务死锁时持有什么锁？

三. 加锁分析

1. 插入意向锁被什么阻塞了？

1.1 插入意向锁简介

Insert Intention Locks 插入意向锁是一种特殊的间隙锁，这个锁表示插入的意向，只有在 INSERT 的时候才会有这个锁。插入意向锁和插入意向锁之间互不冲突，所以可以在同一个间隙中有多个事务同时插入不同索引的记录。比如有索引键值分别是 3 和 7 ，不同的事务分别插入 4 和 5，每个事务都会产生一个加在 3-7 之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

1.2 插入意向锁作用

Q：
再来看下 mysql 是如何解决幻读的？
A：
是用过间隙锁。
Q：
那间隙锁是如何防止幻读的？
A：
执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。

1.3 行锁兼容矩阵：

第一行表示已有的锁，第一列表示要加的锁。

这里只对插入意向锁做个总结：

插入意向锁不影响其他事务加其他任何锁。也就是说，一个事务已经获取了插入意向锁，对其他事务是没有任何影响的；
插入意向锁与间隙锁和 Next-key 锁冲突。也就是说，一个事务想要获取插入意向锁，如果有其他事务已经加了间隙锁或 Next-key 锁，则会阻塞。

所以插入意向锁只会和间隙锁或 Next-key 锁冲突。

1.4 总结

回到上面死锁，insert 事务等待插入意向锁，也就能说明，delete 事务持有相对应间隙锁或者 Next-key 锁阻塞了 insert 事务的插入意向锁。

2. T2 事务为什么会有阻塞 T1 事务？

T2 事务 delete 的执行条件是主键id，先来看下等值查询针对不同索引类型的加锁情况把。

2.1 不同索引下不同的加锁方式

网上找了些图，方便理解：

聚簇索引，查询命中，UPDATE students SET score = 100 WHERE id = 15：
RC 和 RR 隔离级别下加锁情况一样，都是对这个聚簇索引加 X 锁
聚簇索引，查询未命中，UPDATE students SET score = 100 WHERE id = 16：
RC 隔离级别，不加锁 RR 隔离级别，在该记录区间加 GAP 锁

以上两个执行条件是主键，和本文死锁 delete 语句执行条件一样，后面的列出来的仅作为补充。

二级唯一索引，查询命中，UPDATE students SET score = 100 WHERE no = 'S0003'：
RC 和 RR 隔离级别下加锁情况一样，二级索引加 X 锁，且对应的主键索引也会加上 X 锁
二级唯一索引，查询未命中，UPDATE students SET score = 100 WHERE no = 'S0008'：
RC 隔离级别，不加锁 RR 隔离级别，在二级索引上加 GAP 锁，主键索引不加锁
二级非唯一索引，查询命中，UPDATE students SET score = 100 WHERE name = 'Tom'：
RC 隔离级别，二级非唯一索引加 X 锁，且对应的主键索引也会加上 X 锁 RR 隔离级别，二级非唯一索引加 X 锁，记录前后加 GAP 锁，且对应的主键索引也会加上 X 锁
二级非唯一索引，查询未命中，UPDATE students SET score = 100 WHERE name = 'John'：
RC 隔离级别，不加锁 RR 隔离级别，在二级非唯一索引上加 GAP 锁，主键索引不加锁
无索引：
RC 隔离级别，所有聚簇索引加锁 RR 隔离级别，所有聚簇索引和聚簇索引之间还会加上 GAP 锁

上面我们只需要看前两个，聚簇索引相关的就行了，因为 delete 操作走的主键id。聚簇索引只有当没命中条件时，才会上 GAP 锁。这里做个实验来看下：

2.2 模拟delete加锁

2.2.1 数据准备

mysql> desc mht_user_fund_quty_xxxx;
+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| id         | bigint(20) | NO   | PRI | NULL    |       |
| gmt_create | datetime   | YES  |     | NULL    |       |
+------------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from mht_user_fund_quty_xxxx;
+----+---------------------+
| id | gmt_create          |
+----+---------------------+
|  1 | 2020-09-02 13:21:03 |
|  4 | 2020-09-02 13:21:03 |
|  8 | 2020-09-02 13:21:03 |
| 11 | 2020-09-02 13:21:03 |
+----+---------------------+
4 rows in set (0.01 sec)

2.2.2 delete 存在的数据

mysql> begin;delete from mht_user_fund_quty_xxxx where id in (1,4,8);
Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.01 sec)

查看 innodb engine 信息：

---TRANSACTION 2022, ACTIVE 2 sec
2 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 3
MySQL thread id 85, OS thread handle 47577018615552, query id 329 localhost root
TABLE LOCK table `gdb`.`mht_user_fund_quty_xxxx` trx id 2022 lock mode IX
RECORD LOCKS space id 27 page no 3 n bits 80 index PRIMARY of table `gdb`.`mht_user_fund_quty_xxxx` trx id 2022 lock_mode X locks rec but not gap

RECORD LOCKS lock_mode X locks rec but not gap 表示 delete 操作对记录加了 X 锁

2.2.3 delete 不存在的数据

删除存在的id=1，删除不存在的id=5

mysql> begin;delete from mht_user_fund_quty_xxxx where id in (1,5);
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

查看 innodb engine 信息：

---TRANSACTION 329051471564040, not started
0 lock struct(s), heap size 1160, 0 row lock(s)
---TRANSACTION 2028, ACTIVE 2 sec
3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
MySQL thread id 87, OS thread handle 47577019156224, query id 334 localhost root
TABLE LOCK table `gdb`.`mht_user_fund_quty_xxxx` trx id 2028 lock mode IX
RECORD LOCKS space id 27 page no 3 n bits 80 index PRIMARY of table `gdb`.`mht_user_fund_quty_xxxx` trx id 2028 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 8; hex 8000000000000001; asc         ;;
1: len 6; hex 0000000007ec; asc       ;;
2: len 7; hex 3800000146093b; asc 8   F ;;;
3: len 5; hex 99a744d543; asc   D C;;

RECORD LOCKS space id 27 page no 3 n bits 80 index PRIMARY of table `gdb`.`mht_user_fund_quty_xxxx` trx id 2028 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 8000000000000008; asc         ;;
1: len 6; hex 000000000790; asc       ;;
2: len 7; hex e0000001700130; asc     p 0;;
3: len 5; hex 99a744d543; asc   D C;;

其中一个锁是 lock_mode X locks rec but not gap ，这个就是 id=1 这条记录，因为记录存在，所以对该记录上了一个 X 锁。

另一个锁是 lock_mode X locks gap before rec ，这个是删除 id=5 时，发现记录不存在，于是对 (4,8)这个区间上了 GAP锁。

2.3 GAP 锁与插入意向锁互斥的测试

事务1：
mysql> begin;delete from mht_user_fund_quty_xxxx where id in (6);
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

事务2：
mysql> begin;insert into mht_user_fund_quty_xxxx values (5,now());
Query OK, 0 rows affected (0.00 sec)
此时事务处于等待状态

---TRANSACTION 2031, ACTIVE 9 sec inserting #insert事务
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1160, 1 row lock(s)
MySQL thread id 92, OS thread handle 47577019426560, query id 341 localhost root update
insert into mht_user_fund_quty_xxxx values (5,now())
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED: # 等待下面这个锁
RECORD LOCKS space id 27 page no 3 n bits 80 index PRIMARY of table `gdb`.`mht_user_fund_quty_xxxx` trx id 2031 lock_mode X locks gap before rec insert intention waiting # 等待锁的类型为插入意向锁
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

------------------
TABLE LOCK table `gdb`.`mht_user_fund_quty_xxxx` trx id 2031 lock mode IX
RECORD LOCKS space id 27 page no 3 n bits 80 index PRIMARY of table `gdb`.`mht_user_fund_quty_xxxx` trx id 2031 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

---TRANSACTION 2030, ACTIVE 13 sec # delete事务
2 lock struct(s), heap size 1160, 1 row lock(s)
MySQL thread id 91, OS thread handle 47577019156224, query id 339 localhost root
TABLE LOCK table `gdb`.`mht_user_fund_quty_xxxx` trx id 2030 lock mode IX
RECORD LOCKS space id 27 page no 3 n bits 80 index PRIMARY of table `gdb`.`mht_user_fund_quty_xxxx` trx id 2030 lock_mode X locks gap before rec # delete事务持有的锁为 GAP 锁
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

删除不存在的记录 6，会上一个(4,8)的 GAP 锁，这时候 insert 插入记录 5，会发现这个区间已经有了 GAP 锁，所以插入意向锁会处于等待状态。

2.4 总结

通过倒推，我们知道了 delete 删了不存在的记录后加了 GAP 锁，而 GAP 锁导致了 insert 的插入意向锁等待。

3. T1 insert 事务持有什么锁导致了死锁？

通过上文，理清了 T1 的插入意向锁在等待 T2 的 GAP 锁，那要相互形成死锁，T2 也必须在等待 T1。从死锁日志来看 T2 在等待记录 X 锁，那来理一理 T1 到底加了什么锁？

3.1 行锁兼容矩阵：

第一行表示已有的锁，第一列表示要加的锁。

再来看下这个行锁兼容矩阵，会让记录锁发生等待的，只有记录锁和 next-key 锁。

3.2 insert 加锁流程：

简单画了下 insert 加锁的流程图：

3.3 总结

因为 T1 insert 事务肯定拿到了记录锁(根据加锁流程，insert不会上 next-key 锁)，也就是说 insert 语句已经插入了记录并上了 X 锁，delete 事务就是在等待这个 X 锁。

四. 模拟死锁

image

手工绘的图就不放上来了，之后用工具画一个。

结合上面的加锁推断，只能想出这种可能形成死锁的方式，目前这种方式不能复现，只有在高并发的环境偶然发生。

这不一定是生产环境形成死锁的确切原因，只是一种可能，后续继续看看还有没有其他可能以及有没有其他方式去复现。

后记

在测试过程中发现个问题，即使删除存在的数据，也会阻塞insert。之后针对5.7和8.0版本都做了测试，具体实验结果如下：

模拟测试

针对 5.7 及 8.0 版本。

5.7 版本

事务1：

mysql> select * from mht_user_fund_quty_xxxx;
+----+---------------------+
| id | gmt_create          |
+----+---------------------+
|  1 | 2020-09-02 13:21:03 |
|  4 | 2020-09-02 13:21:03 |
|  8 | 2020-09-02 13:21:03 |
| 11 | 2020-09-02 13:21:03 |
+----+---------------------+
4 rows in set (0.00 sec)

mysql> begin;delete from mht_user_fund_quty_xxxx where id in (1,4,8,11);
Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.00 sec)

事务2：

mysql> begin;insert into mht_user_fund_quty_xxxx values (5,now());
Query OK, 0 rows affected (0.01 sec)

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

innodb日志：

---TRANSACTION 2131, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1160, 1 row lock(s)
MySQL thread id 106, OS thread handle 47577019426560, query id 416 localhost root update
insert into mht_user_fund_quty_xxxx values (5,now())
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27 page no 3 n bits 80 index PRIMARY of table `gdb`.`mht_user_fund_quty_xxxx` trx id 2131 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 8; hex 8000000000000008; asc         ;;
 1: len 6; hex 00000000084e; asc      N;;
 2: len 7; hex 50000001910164; asc P     d;;
 3: len 5; hex 99a744d543; asc   D C;;

------------------
TABLE LOCK table `gdb`.`mht_user_fund_quty_xxxx` trx id 2131 lock mode IX
RECORD LOCKS space id 27 page no 3 n bits 80 index PRIMARY of table `gdb`.`mht_user_fund_quty_xxxx` trx id 2131 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 8; hex 8000000000000008; asc         ;;
 1: len 6; hex 00000000084e; asc      N;;
 2: len 7; hex 50000001910164; asc P     d;;
 3: len 5; hex 99a744d543; asc   D C;;

---TRANSACTION 2126, ACTIVE 8 sec
2 lock struct(s), heap size 1160, 5 row lock(s), undo log entries 4
MySQL thread id 105, OS thread handle 47577018615552, query id 414 localhost root
TABLE LOCK table `gdb`.`mht_user_fund_quty_xxxx` trx id 2126 lock mode IX
RECORD LOCKS space id 27 page no 3 n bits 80 index PRIMARY of table `gdb`.`mht_user_fund_quty_xxxx` trx id 2126 lock_mode X

这里是不是颠覆了认知。。明明删除的都是存在的数据，日志里也显示加的锁是 lock_mode X ，没有 GAP 锁，但怎么会阻塞 insert 的插入意向锁？

为了更好地展示加锁的信息，改用 8.0 版本做下测试(performance_schema.data_locks能展示哪条数据加了哪个锁)

8.0 版本

事务1：

root@localhost 21:58:  [gdb]> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)

root@localhost 21:58:  [gdb]> select * from mht_user_fund_quty_xxxx;
+----+---------------------+
| id | gmt_create          |
+----+---------------------+
|  1 | 2020-09-03 21:28:17 |
|  4 | 2020-09-03 21:28:17 |
|  8 | 2020-09-03 21:28:17 |
| 11 | 2020-09-03 21:28:17 |
+----+---------------------+
4 rows in set (0.00 sec)

root@localhost 21:58:  [gdb]> begin;delete from mht_user_fund_quty_xxxx where id in (1,4,8,11);
Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.00 sec)

事务2：

root@localhost 21:58:  [gdb]> begin;insert into mht_user_fund_quty_xxxx values (5,now());
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

居然插入成功了。。看下具体的加锁信息：

表级的 IX 锁不看，4条记录都加了 X,REC_NOT_GAP 锁，insert 没有相关的锁信息(隐式锁下次再写个文章讨论)。

总结

在 5.7 下测了几次，假设 delete 的数据是肯定存在的，总结如下：

当 delete 中的值与 insert 中的值没有相同的时候：
- 删除条件是 in 全表所有数据的时候，会阻塞其他事务的insert；
- 删除非全表数据的时候，不会阻塞其他事务的insert；
当 delete 中的值与 insert 中的值存在相同的时候：
- insert 里相同的那条数据会被 delete 的 X 锁阻塞；

8.0 以上的版本不存在这种情况，然而 8.0 更新文档也未找到相关的优化。暂时就把这个当做低版本的 BUG 把。

后后记

此类出现 GAP 锁的情况，最好的办法就是把 RR 隔离级别换成 RC 隔离级别。除此以外，在思考解决方式的时候，想到执行计划是否在这种小数据量场景下起到了作用。

模拟生产数据场景

# 建表
CREATE TABLE `xxxxxxxx` (
  `id` bigint(20) NOT NULL COMMENT '被锁定的份额ID',
  `gmt_create` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户份额锁定表';

# 插入数据
insert into xxxxxxxx values ( 20260757 ,now()),( 20631771 ,now()),( 20904732 ,now()),( 20754779 ,now()),( 20965078 ,now()),( 20714000 ,now()),( 20506996 ,now()),( 20850800 ,now()),( 20930191 ,now()),( 20591883 ,now()),( 21049204 ,now()),( 20985271 ,now()),( 20675267 ,now()),( 20540769 ,now()),( 21005308 ,now()),( 20694790 ,now()),( 20803523  ,now());

mysql> select * from xxxxxxxx;
+----------+---------------------+
| id       | gmt_create          |
+----------+---------------------+
| 20260757 | 2020-09-08 10:03:29 |
| 20506996 | 2020-09-08 10:03:29 |
| 20540769 | 2020-09-08 10:03:29 |
| 20591883 | 2020-09-08 10:03:29 |
| 20631771 | 2020-09-08 10:03:29 |
| 20675267 | 2020-09-08 10:03:29 |
| 20694790 | 2020-09-08 10:03:29 |
| 20714000 | 2020-09-08 10:03:29 |
| 20754779 | 2020-09-08 10:03:29 |
| 20803523 | 2020-09-08 10:03:29 |
| 20850800 | 2020-09-08 10:03:29 |
| 20904732 | 2020-09-08 10:03:29 |
| 20930191 | 2020-09-08 10:03:29 |
| 20965078 | 2020-09-08 10:03:29 |
| 20985271 | 2020-09-08 10:03:29 |
| 21005308 | 2020-09-08 10:03:29 |
| 21049204 | 2020-09-08 10:03:29 |
+----------+---------------------+
17 rows in set (0.00 sec)

开启 delete 事务：

mysql> begin; DELETE FROM xxxxxxxx where id in ( 20260757 , 20631771 , 20904732 , 20754779 , 20965078 , 20714000 , 20506996 , 20850800 , 20930191 , 20591883 , 21049204  , 20985271 , 206752677 , 20540769 , 21005308 , 20694790 , 20803523 );
Query OK, 0 rows affected (0.01 sec)

Query OK, 17 rows affected (0.00 sec)

同时开始 insert 事务：

mysql> begin;INSERT INTO xxxxxxxx (id, gmt_create) values (21049126, now()), (20262335, now());
Query OK, 0 rows affected (0.00 sec)
# 发生阻塞

查看锁信息：

确实出现了 GAP 锁，锁着的行是 id= 21049204，接下来就是办法解决这个 GAP 锁。

变更执行计划

常规情况下的执行计划：

mysql> explain DELETE FROM xxxxxxxx where id in ( 20260757 , 20631771 , 20904732 , 20754779 , 20965078 , 20714000 , 20506996 , 20850800 , 20930191 , 20591883 , 21049204  , 20985271 , 20675267 , 20540769 , 21005308 , 20694790 , 20803523 );
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | DELETE      | xxxxxxxx | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.01 sec)

key 这列的值是 NULL ，也就是说没有用到主键索引。

在 xxxxxxxx 尾部填充10万数据，存储过程如下：

mysql> drop procedure idata1;

Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;;
mysql> create procedure idata1()
    -> begin
    ->   declare i int;
    ->   set i=2147383647;
    ->   while(i<=2147483647)do
    ->     insert into xxxxxxxx values (i,now());
    ->     set i=i+1;
    ->   end while;
    ->
    -> end;;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call idata1();

填充完成后，在查看执行计划：

mysql> select count(*) from xxxxxxxx;
+----------+
| count(*) |
+----------+
|   100018 |
+----------+
1 row in set (0.48 sec)

mysql> explain DELETE FROM xxxxxxxx where id in ( 20260757 , 20631771 , 20904732 , 20754779 , 20965078 , 20714000 , 20506996 , 20850800 , 20930191 , 20591883 , 21049204 , 20985271 , 20675267 , 20540769 , 21005308 , 20694790 , 20803523 );
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | DELETE      | xxxxxxxx | NULL       | range | PRIMARY       | PRIMARY | 8       | const |   17 |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (0.01 sec)

执行计划中的 key 一项变为了 PRIMARY。

新执行计划下的测试

开启 delete 事务：

mysql> begin;DELETE FROM xxxxxxxx where id in ( 20260757 , 20631771 , 20904732 , 20754779 , 20965078 , 20714000 , 20506996 , 20850800 , 20930191 , 20591883 , 21049204 , 20985271 , 20675267 , 20540769 , 21005308 , 20694790 , 20803523 );
Query OK, 0 rows affected (0.00 sec)

Query OK, 17 rows affected (0.01 sec)

开启 insert 事务：

mysql> begin;INSERT INTO xxxxxxxx (id, gmt_create) values (21049126, now()), (20262335, now());
Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

成功插入数据，没有出现插入意向锁等待的情况！

除了把事务隔离级别从 repeatable-read 换成 read-committed，生产可以用这种填充数据改变执行计划的方式，看看死锁还会不会复现。

注意事项

如果填充数据是插入在头部的，，比如下面的 data_ahead 表：

mysql> select count(*) from data_ahead;
+----------+
| count(*) |
+----------+
|   100017 |
+----------+
1 row in set (0.54 sec)

mysql> select * from mht_xxxxxx order by id desc limit 20;
+----------+---------------------+
| id       | gmt_create          |
+----------+---------------------+
| 21049204 | 2020-09-08 09:43:02 |
| 21005308 | 2020-09-08 09:43:02 |
| 20985271 | 2020-09-08 09:43:02 |
| 20965078 | 2020-09-08 09:43:02 |
| 20930191 | 2020-09-08 09:43:02 |
| 20904732 | 2020-09-08 09:43:02 |
| 20850800 | 2020-09-08 09:43:02 |
| 20803523 | 2020-09-08 09:43:02 |
| 20754779 | 2020-09-08 09:43:02 |
| 20714000 | 2020-09-08 09:43:02 |
| 20694790 | 2020-09-08 09:43:02 |
| 20675267 | 2020-09-08 09:43:02 |
| 20631771 | 2020-09-08 09:43:02 |
| 20591883 | 2020-09-08 09:43:02 |
| 20540769 | 2020-09-08 09:43:02 |
| 20506996 | 2020-09-08 09:43:02 |
| 20260757 | 2020-09-08 09:43:02 |
|   100000 | 2020-09-08 09:55:02 |
|    99999 | 2020-09-08 09:55:02 |
|    99998 | 2020-09-08 09:55:02 |
+----------+---------------------+
20 rows in set (0.01 sec)
填充数据集中在 1-100000 间

开启 delete 事务：

mysql> begin;DELETE FROM data_ahead where id in ( 20260757 , 20631771 , 20904732 , 20754779 , 20965078 , 20714000 , 20506996 , 20850800 , 20930191 , 20591883 , 21049204 , 20985271 , 20675267 , 20540769 , 21005308 , 20694790 , 20803523 );

Query OK, 0 rows affected (0.01 sec)

Query OK, 17 rows affected (0.00 sec)

开启 insert 事务：

mysql> begin;INSERT INTO data_ahead (id, gmt_create) values (21049126, now()), (21262335, now());

Query OK, 0 rows affected (0.00 sec)
# 处于等待状态

此时锁的状态：

可以看到 supremum pseudo-record 。如果插入的值超过索引中最大值，锁类型变为X锁，被锁的最大行为supremum pseudo-record，它具有大于所有索引的值，不是真正的索引记录。此时，锁的范围扩大到正无穷。

只要执行计划用到主键，就不会出现这个问题。或者可以插入一条 id=99999999999 的值，填充记录中的最大值。

出国申请PS注意事项

python分析彩票_重庆青少年编程Python

Windows Embedded CE 6.0开发初体验（六）平台定制

python做游戏用什么库_Python库之游戏开发及虚拟现实

纪晓岚

浙江大学pta答案python第七章_Pta mooc“Python编程浙江大学”拼图题集第7章问答,PTAMOOCPython,程序设计,拼题,题目,第七章,及,代码,答案...

从要大褂还是内裤看软件设计与开发过程

python高维数据存储_使用python实现多维数据降维操作

代码，梦

python求两个时间相差的天数_Python计算两个日期相差天数的方法示例

python 序列化压缩成字符串_python 序列化模块之 json 和 pickle

2005年10月--至今,开发过的项目

红黑树的原理_Linux内核-红黑树的实现原理及应用

python整型数据源码分析_Python 源码剖析（二）【整数对象】

如何选择适合自己的 Linux 发行版

[译] React v16.8：含有Hooks的版本

《深入 React 技术栈》

avalon2.2的VM生成过程

css的样式优先级

Django 博客开发教程 8 - 博客文章详情页

flask接收请求并推入栈

Hexo+码云+git快速搭建免费的静态Blog

java B2B2C 源码多租户电子商城系统-Kafka基本使用介绍

Js实现点击查看全文(类似今日头条、知乎日报效果)

js中的正则表达式入门

Mithril.js 入门介绍

OpenStack安装流程（juno版）- 添加网络服务（neutron）- controller节点

spring cloud gateway 源码解析（4）跨域问题处理

TiDB 源码阅读系列文章（十）Chunk 和执行框架简介

webpack+react项目初体验——记录我的webpack环境配置

阿里中间件开源组件：Sentinel 0.2.0正式发布

分类模型——Logistics Regression

浮动相关

观察者模式实现非直接耦合

基于Javascript， Springboot的管理系统报表查询页面代码设计

开发了一款写作软件(OSX,Windows)，附带Electron开发指南

来，膜拜下android roadmap，强大的执行力

前端面试之CSS3新特性

手机端车牌号码键盘的vue组件

在 Chrome DevTools 中调试 JavaScript 入门

自制字幕遮挡器

Linux权限管理(week1_day5)--技术流ken

京东物流联手山西图灵打造智能供应链，让阅读更有趣 ...

数据库巡检项

软考-高级-系统架构设计师教程（清华第2版）【第1章-绪论-思维导图】

# .NET Framework中使用命名管道进行进程间通信

#周末课堂# 【Linux + JVM + Mysql高级性能优化班】（火热报名中~~~）

(02)Cartographer源码无死角解析-(03) 新数据运行与地图保存、加载地图启动仅定位模式

(NO.00004)iOS实现打砖块游戏(九):游戏中小球与反弹棒的碰撞

(阿里云万网)-域名注册购买实名流程

（顶刊）一个基于分类代理模型的超多目标优化算法

（二）JAVA使用POI操作excel

（附源码）SSM环卫人员管理平台计算机毕设36412

（四）图像的%2线性拉伸

(一) storm的集群安装与配置

delete & insert 死锁

一. 死锁信息

二. 死锁分析

三. 加锁分析

1. 插入意向锁被什么阻塞了？

1.1 插入意向锁简介

1.2 插入意向锁作用

1.3 行锁兼容矩阵：

1.4 总结

2. T2 事务为什么会有阻塞 T1 事务？

2.1 不同索引下不同的加锁方式

2.2 模拟delete加锁

2.2.1 数据准备

2.2.2 delete 存在的数据

2.2.3 delete 不存在的数据

2.3 GAP 锁与插入意向锁互斥的测试

2.4 总结

3. T1 insert 事务持有什么锁导致了死锁？

3.1 行锁兼容矩阵：

3.2 insert 加锁流程：

3.3 总结

四. 模拟死锁

后记

模拟测试

5.7 版本

8.0 版本

总结

后后记

模拟生产数据场景

变更执行计划

新执行计划下的测试

注意事项

相关文章：