【Mysql】两条insert语句产生的死锁

背景：查看status日志发现两条insert 出现了死锁

RECORD LOCKS space id 388 page no 27032 n bits 616 index `idx_svcorderserviceitem_workorderid_quantity` of table `ecejservice`.`svc_order_service_item` trx id 596252578 lock_mode X insert intention waiting

可以确定，这个x锁不是由于INSERT产生的，因为 INSERT可能产生的锁包括检查dup key时的s锁，隐式锁转换为显式锁（not gap，要在二级索引上产生lock_mode为X的LOCK_ORDINARY类型的锁(包括记录及记录前面的gap)，据我所知一般是根据二级索引扫描进行记录更新导致的。

3.根据
LOCK WAIT 14 lock struct(s), heap size 2936, 7 row lock(s), undo log entries 7
有7个undo entires，而单纯的INSERT一条记录只有一个undo entry，因此可以推断除了INSERT，必然还有别的操作

基于以上，事务除了INSERT，可能还存在DELETE/UPDATE，并且这些操作是走的二级索引来查找更新记录。

一个简单但不完全相同的重现步骤：

DROP TABLE t1;

CREATE TABLE `t1` (

`a` int(11) NOT NULL AUTO_INCREMENT,

`b` int(11) DEFAULT NULL,

`c` int(11) DEFAULT NULL,

PRIMARY KEY (`a`),

KEY `b` (`b`)

) ENGINE=InnoDB ;

insert into t1(a, b,c) values(1,2,3),(5,4,6),(8, 7,9),(12,12,19),(15,15,11);

session1:

成都创新互联专注为客户提供全方位的互联网综合服务，包含不限于网站建设、成都做网站、卧龙网络推广、成都小程序开发、卧龙网络营销、卧龙企业策划、卧龙品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等，从售前售中售后，我们都将竭诚为您服务，您的肯定，是我们最大的嘉奖；成都创新互联为所有大学生创业者提供卧龙建站搭建服务，24小时服务热线：18982081108，官方网址：www.cdcxhl.com

begin;

delete from t1 where b = 12;

//二级索引上lock_mode X、lock_mode X locks gap before rec以及主键上的lock_mode X locks rec but not gap

二级索引：heap_no=5, type_mode=3 （12上的LOCK_ORDINARY类型锁，包括记录和记录前的GAP）

聚集索引：heap_no=5，type_mode=1027

二级索引：heap_no=6，type_mode=547(15上的GAP锁)

session2:

begin;

delete from t1 where b = 7;

//二级索引上lock_mode X、lock_mode X locks gap before rec以及主键上的lock_mode X locks rec but not gap

二级索引：heap_no=4，type_mode=3 （7上的LOCK_ORDINARY类型锁，包括记录和记录前的GAP）

聚集索引：heap_no=4，type_mode=1027

二级索引：heap_no=5，type_mode=547 (记录12上的GAP锁)

session1:

insert into t1 values (NULL, 6,10);

//新插入记录聚集索引无冲突插入成功，二级索引等待插入意向锁（lock_mode X locks gap before rec insert intention waiting）

二级索引,heap_no=4, type_mode=2819 （请求记录7上面的插入意向锁LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION, 需要等待session2

session2:

insert into t1 values (NULL, 7,10);

二级索引:heap_no=5, type_mode=2819 (请求记录12上的插入意向锁LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION，需要等待session1)

互相等待，导致发生死锁

从打印的死锁信息来看，基本和线上发生的死锁现象是一致的。

再举一个例子

MySQL> select * from test01;

+----+-----+

| id | app |

+----+-----+

| 1 | 01 |

| 2 | 02 |

| 5 | 03 |

| 10 | 03 |

| 6 | 04 |

| 7 | 05 |

| 8 | 06 |

| 9 | 06 |

| 11 | 06 |

| 12 | 07 |

| 13 | 08 |

| 14 | 09 |

| 15 | 09 |

+----+-----+

13 rows in set (0.00 sec)

session1：

mysql> select now();start TRANSACTION;

+---------------------+

| now() |

+---------------------+

| 2018-01-25 16:08:46 |

+---------------------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test01 where app='05' for update; ---第1步锁住【6.04】-【7.05】以及【7.05】-【8.06】两段区间

+----+-----+

| id | app |

+----+-----+

| 7 | 05 |

+----+-----+

1 row in set (0.00 sec)

mysql> insert into test01(app) values ('07'); --第三步等待第二步释放

Query OK, 1 row affected (23.24 sec)

session2：

mysql> select * from test01 where app='08' for update; --第二步锁住【12,07】-【13，08】以及【13,08】-【14,09】两段区间

+----+-----+

| id | app |

+----+-----+

| 13 | 08 |

+----+-----+

1 row in set (0.00 sec)

mysql> insert into test01(app) values ('04'); ----第四步等待第一步释放，，于是死锁

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

查看死锁日志:

LATEST DETECTED DEADLOCK
------------------------
2018-01-25 16:09:54 0x7f07d23ff700
*** (1) TRANSACTION:
TRANSACTION 5375, ACTIVE 51 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 2294, OS thread handle 139671567841024, query id 42463 localhost root update
insert into test01(app) values ('07')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 64 page no 4 n bits 80 index idx_app of table `devops`.`test01` trx id 5375 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 2; hex 3038; asc 08;;
1: len 4; hex 0000000d; asc ;;
*** (2) TRANSACTION:
TRANSACTION 5376, ACTIVE 38 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 2293, OS thread handle 139671568905984, query id 42464 localhost root update
insert into test01(app) values ('04')

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

RECORD LOCKS space id 64 page no 4 n bits 80 index idx_app of table `devops`.`test01` trx id 5376 lock_mode X
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 2; hex 3038; asc 08;;
1: len 4; hex 0000000d; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 64 page no 4 n bits 80 index idx_app of table `devops`.`test01` trx id 5376 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 2; hex 3035; asc 05;;
1: len 4; hex 00000007; asc ;;
*** WE ROLL BACK TRANSACTION (2)
------------

死锁日志是不是和上面的一样？

参考：
http://blog.itpub.net/22664653/viewspace-2145068/ ----杨奇龙
http://www.sohu.com/a/169663059_610509 ---insert ..select 语句产生死锁
http://blog.itpub.net/7728585/viewspace-2146183/ ---insert ..select 语句产生死锁--八怪
分享标题：【Mysql】两条insert语句产生的死锁
网页链接：http://dzwzjz.com/article/gpdcdc.html

大橙子网站建设，新征程启航

【Mysql】两条insert语句产生的死锁

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

其他资讯