Skip to content

Latest commit

 

History

History
92 lines (75 loc) · 3.53 KB

18.md

File metadata and controls

92 lines (75 loc) · 3.53 KB
Raw

delete-wait-lock-mode-x-locks-rec-but-not-gap-vs-insert-wait-lock-mode-s-holds-lock-mode-x-locks-rec-but-not-gap

死锁特征

  1. delete WAITING FOR lock_mode X locks rec but not gap
  2. insert WAITING FOR lock mode S, HOLDS lock_mode X locks rec but not gap

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-04-26 23:52:06 0x7fcb04122700
*** (1) TRANSACTION:
TRANSACTION 2290, ACTIVE 0 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 5, OS thread handle 140509923120896, query id 861 localhost root updating
delete from t18 where id = 4
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 3 n bits 80 index PRIMARY of table `dldb`.`t18` trx id 2290 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 00000004; asc     ;;
 1: len 6; hex 0000000008f1; asc       ;;
 2: len 7; hex 7a000001ce01ca; asc z      ;;

*** (2) TRANSACTION:
TRANSACTION 2289, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 140509923387136, query id 862 localhost root update
insert into t18 (id) values (4)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 24 page no 3 n bits 80 index PRIMARY of table `dldb`.`t18` trx id 2289 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 00000004; asc     ;;
 1: len 6; hex 0000000008f1; asc       ;;
 2: len 7; hex 7a000001ce01ca; asc z      ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 3 n bits 80 index PRIMARY of table `dldb`.`t18` trx id 2289 lock mode S waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 00000004; asc     ;;
 1: len 6; hex 0000000008f1; asc       ;;
 2: len 7; hex 7a000001ce01ca; asc z      ;;

*** WE ROLL BACK TRANSACTION (1)

表结构

CREATE TABLE `t18` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

初始数据：

INSERT  INTO `t18`(`id`) VALUES (1);
INSERT  INTO `t18`(`id`) VALUES (2);
INSERT  INTO `t18`(`id`) VALUES (3);
INSERT  INTO `t18`(`id`) VALUES (4);
INSERT  INTO `t18`(`id`) VALUES (5);
INSERT  INTO `t18`(`id`) VALUES (6);
INSERT  INTO `t18`(`id`) VALUES (7);
INSERT  INTO `t18`(`id`) VALUES (8);

重现步骤

Session 1 Session 2
delete from t18 where id = 4;//ok, 0 rows affected
delete from t18 where id = 4; //wating,被阻塞
insert into t18 values(4);//Query OK, 1 row affected (0.01 sec)
ERROR 1213 (40001): Deadlock found when trying to get lock;

分析

  1. 事务一delete语句加记录锁（lock_mode X locks rec but not gap）
  2. 事务一delete语句等待记录锁（lock_mode X locks rec but not gap waiting）
  3. 事务一执行insert检查到 duplicate key（或者有一个被标记删除的duplicate key）加LOCK_S锁，且针对主键索引加LOCK_ORDINARY类型的记录锁（NEXT-KEY LOCK）；此时事务2已经在申请record lock X锁，在申请队列中了，事务1再加NEXT-KEY LOCK S锁则需要等待事务2提交，这就造成了相互等待。

参考

  1. 并发delete+insert duplicate-key冲突导致死锁
  2. InnoDB 事务锁系统简介