Skip to content

Latest commit

 

History

History
67 lines (53 loc) · 2.42 KB

15.md

File metadata and controls

67 lines (53 loc) · 2.42 KB
Raw

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

死锁特征

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

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-17 15:15:03 7f78eac15700
*** (1) TRANSACTION:
TRANSACTION 462308661, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 3796966, OS thread handle 0x7f78ead9d700, query id 781045166 localhost root update
insert into t7(id,a) values(30,10)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308661 lock mode S waiting
*** (2) TRANSACTION:
TRANSACTION 462308660, ACTIVE 43 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 3796960, OS thread handle 0x7f78eac15700, query id 781045192 localhost root update
insert into t7(id,a) values(40,9)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308660 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308660 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)

表结构

create table t7(
  id int not null primary key auto_increment,
  a int not null ,
  unique key ua(a)
) engine=innodb;

初始数据：

insert into t7(id,a) values(1,1),(5,4),(20,20),(25,12);

重现步骤

Session 1 Session 2
insert into t7(id,a) values(26,10);
insert into t7(id,a) values(30,10);
insert into t7(id,a) values(40,9);

分析

事务一在插入时由于跟事务二插入的记录唯一键冲突，所以对 a=10 这个唯一索引加 S 锁（Next-key）并处于锁等待，事务二再插入 a=9 这条记录，需要获取插入意向锁（lock_mode X locks gap before rec insert intention）和事务一持有的 Next-key 锁冲突，从而导致死锁。

参考

  1. 【MySQL】死锁案例之三