Skip to content

Latest commit

 

History

History
74 lines (61 loc) · 3.63 KB

2.md

File metadata and controls

74 lines (61 loc) · 3.63 KB
Raw

insert-wait-lock-mode-x-insert-intention-vs-insert-wait-lock-mode-x-insert-intention-holds-lock-mode-s

死锁特征

  1. insert WAITING FOR lock_mode X insert intention waiting
  2. insert WAITING FOR lock_mode X insert intention waiting, HOLDS lock_mode S
  3. 隔离级别：RR、RC

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
130701 20:47:57
*** (1) TRANSACTION:
TRANSACTION 4F3D6D24, ACTIVE 13 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 18124702, OS thread handle 0x7fe706fdf700, query id 1435659684 localhost root update
insert into lingluo values(100214,215,215,312)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6D24 lock_mode X insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 4F3D6F33, ACTIVE 11 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 18124715, OS thread handle 0x7fea34912700, query id 1435660081 localhost root update
insert into lingluo values(100215,215,215,312)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6F33 lock mode S
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3351 page no 4 n bits 80 index `uk_bc` of table `test`.`lingluo` trx id 4F3D6F33 lock_mode X insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

表结构

CREATE TABLE `lingluo` (
  `a` int(11) NOT NULL DEFAULT '0',
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `uk_bc` (`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk

重现步骤

Session 1 Session 2 Session 3
insert into lingluo values(100213,215,215,312);
insert into lingluo values(100214,215,215,312);
insert into lingluo values(100215,215,215,312);
rollback;
deadlock

分析

这个死锁的分析分三步：

  1. 三个事务依次执行 insert 语句，由于 (b,c) 是唯一索引，所以后两个事务会出现唯一键冲突。但此时要注意的是事务一还没有提交，所以并不会立即报错。insert 语句本来加的是隐式锁，在出现唯一键冲突时，事务一的隐式锁升级为显式锁（LOCK_REC_NOT_GAP | LOCK_REC | LOCK_X），事务二和事务三为了判断是否出现唯一键冲突，必须进行一次当前读，加的锁是 Next-Key 锁，所以进入锁等待（LOCK_GAP | LOCK_REC | LOCK_S | LOCK_WAIT）。要注意的是，就算在 RC 隔离级别下，一样会加 Next-Key 锁，所以说出现 GAP 锁不一定就是 RR 隔离级别;
  2. 事务一回滚，此时事务二和事务三成功获取记录上的 S 锁（LOCK_GAP | LOCK_REC | LOCK_S）；
  3. 事务二和事务三继续执行插入操作，需要依次请求记录上的插入意向锁（LOCK_INSERT_INTENTION | LOCK_GAP | LOCK_X），插入意向锁和 GAP 锁冲突，所以事务二等待事务三，事务三等待事务二，形成死锁。

参考

  1. Bug #43210 Deadlock detected on concurrent insert into same table (InnoDB)
  2. 有趣的insert死锁
  3. 【MySQL】死锁案例之四
  4. Locks Set by Different SQL Statements in InnoDB