两条insert 语句出现的死锁

2024-09-07 22:12:37

字体：大中小

来源：转载

供稿：网友

查看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)，据我所知一般是根据二级索引扫描进行记录更新导致的。

一个简单但不完全相同的重现步骤：
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:

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)

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

*** (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)
------------

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

（编辑：武林网）

上一篇：服务器事务日志已满处理方法

下一篇：innodb_data_file_path和innodb_data_home_dir