Skip to content

Latest commit

 

History

History
87 lines (72 loc) · 4.64 KB

14.md

File metadata and controls

87 lines (72 loc) · 4.64 KB
Raw

insert-wait-lock-mode-x-locks-gap-before-rec-insert-intention-vs-insert-wait-lock-mode-x-locks-gap-before-rec-insert-intention-holds-lock-mode-x-locks-gap-before-rec

死锁特征

  1. insert WAITING FOR lock_mode X locks gap before rec insert intention
  2. insert WAITING FOR lock_mode X locks gap before rec insert intention, HOLDS lock_mode X locks gap before rec

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-11 14:51:03 7f78eaf25700
*** (1) TRANSACTION:
TRANSACTION 462308535, ACTIVE 20 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 3584515, OS thread handle 0x7f78ea5f5700, query id 780258123 localhost root update
insert into t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
VALUES('18', '2', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 225 page no 4 n bits 72 index `uniq_kid_aid_biz_rid` of table `test`.`t4` trx id 462308535 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 462308534, ACTIVE 29 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 3584572, OS thread handle 0x7f78eaf25700, query id 780258153 localhost root update
INSERT INTO t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
VALUES ('15', '1', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 225 page no 4 n bits 72 index `uniq_kid_aid_biz_rid` of table `test`.`t4` trx id 462308534 lock_mode X locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 225 page no 4 n bits 72 index `uniq_kid_aid_biz_rid` of table `test`.`t4` trx id 462308534 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

表结构

CREATE TABLE `t4` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT ,
  `kdt_id` int(11) unsigned NOT NULL ,
  `admin_id` int(11) unsigned NOT NULL ,
  `biz` varchar(20) NOT NULL DEFAULT '1' ,
  `role_id` int(11) unsigned NOT NULL ,
  `shop_id` int(11) unsigned NOT NULL DEFAULT '0' ,
  `operator` varchar(20) NOT NULL DEFAULT '0' ,
  `operator_id` int(11) NOT NULL DEFAULT '0' ,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_kid_aid_biz_rid` (`kdt_id`,`admin_id`,`role_id`,`biz`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

初始数据：

INSERT INTO `t4` (`id`, `kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
VALUES
 (1,10,1,'retail',1,0,'0',0,'2017-05-09 15:55:26','2017-05-09 15:55:26'),
 (2,20,1,'retail',1,0,'0',0,'2017-05-09 15:55:40','2017-05-09 15:55:40'),
 (3,30,1,'retail',1,0,'0',0,'2017-05-09 15:55:55','2017-05-09 15:55:55'),
 (4,40,1,'retail',1,0,'0',0,'2017-05-09 15:56:06','2017-05-09 15:56:06'),
 (5,50,1,'retail',1,0,'0',0,'2017-05-09 15:56:16','2017-05-09 15:56:16');

重现步骤

Session 1 Session 2
delete from t4 where kdt_id = 15 and admin_id = 1 and biz = 'retail' and role_id = '1';
delete from t4 where kdt_id = 18 and admin_id = 2 and biz = 'retail' and role_id = '1';
insert into t4(kdt_id, admin_id, biz, role_id, shop_id, operator, operator_id, create_time, update_time) VALUES('18', '2', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);
INSERT INTO t4(kdt_id, admin_id, biz, role_id, shop_id, operator, operator_id, create_time, update_time) VALUES ('15', '1', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); .

分析

这个死锁和案例 1 一模一样，都是插入意向锁和 Gap 锁冲突导致的死锁。不过案例 1 插入的位置在 supremum record 上，这里插入的位置就是一个普通的 gap。

当在 supremum record 上加锁时，locks gap before rec 会省略掉，所以这里的 insert WAITING FOR lock_mode X locks gap before rec insert intention, HOLDS lock_mode X locks gap before rec 和 案例 1 里的 insert WAITING FOR lock_mode X insert intention waiting, HOLDS lock_mode X 没任何区别，对这个死锁的分析参见案例 1 或参考链接，这里就不赘述了。

参考

  1. 【MySQL】死锁案例之二