Skip to content

Latest commit

 

History

History
77 lines (63 loc) · 3.46 KB

5.md

File metadata and controls

77 lines (63 loc) · 3.46 KB
Raw

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

死锁特征

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

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
170219 13:31:31
*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 00000002; asc     ;;
*** (2) TRANSACTION:
TRANSACTION 2A8BC, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
insert into test (id,a) values (10,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 00000002; asc     ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 00000002; asc     ;;
*** WE ROLL BACK TRANSACTION (1)

表结构

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

重现步骤

Session 1 Session 2
delete from test where a = 2;
insert into test (id, a) values (10, 2); insert第1阶段：事务2申请S锁进行duplicate key进行检查。检查成功。
delete from test where a = 2;
insert into test (id, a) values (10, 2); insert第2阶段：事务2开始插入数据，S锁升级为X锁，类型为insert intention。同理，X锁进入队列排队，形成循环等待，死锁产生。

分析

这个场景和 delete-wait-lock-mode-x-vs-insert-wait-lock-mode-s-holds-lock-mode-x-locks-rec-but-not-gap 一模一样，但是没办法手动重现，只能在高并发情况下可能会出现。

这个死锁的原因在于 insert 加锁分几个阶段：先检查唯一键约束，加 S 锁，再加插入意向锁，最后插入成功时升级为 X 锁。

delete-wait-lock-mode-x-vs-insert-wait-lock-mode-s-holds-lock-mode-x-locks-rec-but-not-gap 中，insert 加 S 锁时冲突导致死锁，而这里是加插入意向锁时冲突导致死锁。

参考

  1. 记录一次Mysql死锁排查过程