Skip to content

Latest commit

 

History

History
72 lines (57 loc) · 2.46 KB

13.md

File metadata and controls

72 lines (57 loc) · 2.46 KB
Raw

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

死锁特征

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

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-10 00:03:31 7f78ea936700
*** (1) TRANSACTION:
TRANSACTION 462308445, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 3526009, OS thread handle 0x7f896cc4b700, query id 780047877 localhost root updating
delete from t2 where a=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 221 page no 4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308445 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 462308444, ACTIVE 17 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 3526051, OS thread handle 0x7f78ea936700, query id 780047890 localhost root update
insert t2(a,b) values(5,10)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 221 page no 4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308444 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 221 page no 4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308444 lock mode S waiting
*** WE ROLL BACK TRANSACTION (1)

表结构

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  unique KEY `idxa` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

初始数据：

insert into ty(a,b) values(2,3),(5,4),(6,7);

重现步骤

Session 1 Session 2
delete from ty where a=5;
delete from ty where a=5;
insert into ty(a,b) values(2,10); .

分析

这个死锁和案例 12 的表结构几乎完全一样，只是这里的索引 a 为唯一索引。要分析这个死锁，只要记住两点就很好理解了：

  1. 一般情况下根据唯一索引来 delete，加的是记录锁（rec but not gap），但是如果该记录已经被标记为删除，加的就是 Next-key 锁（lock_mode X）;
  2. insert 在插入唯一索引时需要加 S 锁进行唯一性检查；

参考

  1. 【MySQL】死锁案例之一