Skip to content

Latest commit

 

History

History
83 lines (67 loc) · 3.63 KB

4.md

File metadata and controls

83 lines (67 loc) · 3.63 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
  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 S 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;

初始数据：

insert into test(id, a) values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

重现步骤

Session 1 Session 2
delete from test where a = 2;
delete from test where a = 2;
insert into test (id, a) values (10, 2);

分析

delete 语句正常情况下会对存在的记录加记录锁（lock_mode X locks rec but not gap），但是事务一的 delete 语句却在加 next-key 锁（lock_mode X），只有当这条记录已经被标记为删除时才会出现这种情况，我们从 info bits 32 也可以看出来这条记录已经被标记为删除了。

再从事务二的 undo log entries 2 可以推测在执行 insert 之前还执行了至少一条语句，从它 HOLDS 的锁是 lock_mode X locks rec but not gap，并且 page no 也是 923，大致可以猜出事务二先执行了 delete 语句。

另外，事务二正在执行 insert 语句，并尝试获取 lock mode S waiting 锁，这是为了检测唯一键是否重复，必须进行一次当前读，要加 S 锁。要注意的是，这里的 S 锁是 lock mode S，而不是 lock_mode S locks rec but not gap，，也就是说检测唯一键加的是 Next-Key 锁，而不是记录锁。

参考

  1. 记录一次Mysql死锁排查过程
  2. MySQL · 引擎特性 · InnoDB 事务锁系统简介