Skip to content

Latest commit

 

History

History
91 lines (72 loc) · 4.25 KB

6.md

File metadata and controls

91 lines (72 loc) · 4.25 KB
Raw

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

死锁特征

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

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
140122 18:11:58
*** (1) TRANSACTION:
TRANSACTION 930F9, ACTIVE 0 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 2096, OS thread handle 0x7f3570976700, query id 1485879 localhost 127.0.0.1 rj updating
delete from dltask where a = 'b' and b = 'b' and c = 'a'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 12713 n bits 96 index `uniq_a_b_c` of table `dltst`.`dltask` trx id 930F9 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 930F3, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 2101, OS thread handle 0x7f3573d88700, query id 1485872 localhost 127.0.0.1 rj updating
delete from dltask where a = 'b' and b = 'b' and c = 'a'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 12713 n bits 96 index `uniq_a_b_c` of table `dltst`.`dltask` trx id 930F3 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 12713 n bits 80 index `uniq_a_b_c` of table `dltst`.`dltask` trx id 930F3 lock mode X waiting
*** WE ROLL BACK TRANSACTION (1)

表结构

CREATE TABLE dltask (
  id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT ‘auto id’,
  a varchar(30) NOT NULL COMMENT ‘uniq.a’,
  b varchar(30) NOT NULL COMMENT ‘uniq.b’,
  c varchar(30) NOT NULL COMMENT ‘uniq.c’,
  x varchar(30) NOT NULL COMMENT ‘data’,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_a_b_c (a, b, c)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’deadlock test’;

重现步骤

Session 1 Session 2 Session 3
delete from dltask where a=’a’ and b=’b’ and c=’c’; delete from dltask where a=’a’ and b=’b’ and c=’c’; delete from dltask where a=’a’ and b=’b’ and c=’c’;

分析

delete 语句的加锁有三种情况：

  1. 记录存在且有效：加记录X锁（lock_mode X locks rec but not gap）；
  2. 记录存在但无效（被标记为删除）：加 Next-key 锁（lock_mode X）；
  3. 记录不存在：加 gap 锁（locks gap before rec）；

从事务二 HOLDS THE LOCK lock_mode X locks rec but not gap 可以看出事务二已经成功删除了这条记录，并且给这条记录打上了删除标记，所以事务一在删除的时候 WAIT 的锁是 Next-key 锁（lock_mode X）。但是，为什么事务二都已经成功删除了记录，却还要等待 Next-key 锁呢？

其实这个地方我们有点先入为主了，持有记录锁（locks rec but not gap）的事务不一定就是成功删除记录的事务，因为从获取记录锁到把记录标记为删除，这中间还有一个时间间隙，所以在大并发下，可能会出现两个事务同时争取记录锁。

Session 1 Session 2
获取记录锁（locks rec but not gap）
获取记录锁（locks rec but not gap），和事务一冲突，等待
将记录标记为删除，并提交事务
记录锁获取成功

这个时候虽然事务二也获取了记录锁，但是这条记录已经发生了变动，MySQL 会重新对该记录加锁，但是原来加的记录锁并不会释放，如果此时再有一个事务发起 delete 操作，就有可能发生死锁。

Session 2 Session 3
记录变动，需重新加锁，但记录锁保持不变
发起删除操作，由于记录标记为删除，所以获取 Next-key 锁，和事务二的记录锁冲突
重新加锁时获取 Next-key 锁，和事务三冲突，发生死锁 .

关于这个死锁，还有另一个形式：delete-wait-lock-mode-x-locks-rec-but-not-gap-vs-delete-wait-lock-mode-x-holds-lock-mode-x-locks-rec-but-not-gap

更多的分析可以阅读后面的参考链接。

参考

  1. 一个最不可思议的MySQL死锁分析
  2. http://hedengcheng.com/?p=771#comment-5549