Skip to content

Latest commit

 

History

History
71 lines (57 loc) · 2.6 KB

12.md

File metadata and controls

71 lines (57 loc) · 2.6 KB
Raw

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

死锁特征

  1. delete WAITING FOR lock_mode X
  2. insert WAITING FOR lock_mode X locks gap before rec insert intention, HOLDS lock_mode X
  3. 隔离级别：RR

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-09 22:34:13 7f78eab82700
*** (1) TRANSACTION:
TRANSACTION 462308399, ACTIVE 33 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 3525577, OS thread handle 0x7f896cc4b700, query id 780039657 localhost root updating
delete from ty where a=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308399 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 462308398, ACTIVE 61 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 2
MySQL thread id 3525490, OS thread handle 0x7f78eab82700, query id 780039714 localhost root update
insert into ty(a,b) values(2,10)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)

表结构

CREATE TABLE `ty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idxa` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 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); .

分析

这个死锁和 insert-wait-lock-mode-x-insert-intention-vs-insert-wait-lock-mode-x-insert-intention-holds-lock-mode-x 有异曲同工之妙，都是插入意向锁和 Gap 锁冲突导致的。案例一为插入意向锁和 Gap 锁冲突，两个 insert 互相等待导致死锁；而这个案例为插入意向锁和 Next-key 锁冲突，而 Next-key 锁和 Next-key 锁也冲突导致了死锁。

参考

  1. 【MySQL】如何阅读死锁日志
  2. 【MySQL】死锁案例之一