Skip to content

Latest commit

 

History

History
79 lines (64 loc) · 3.52 KB

7.md

File metadata and controls

79 lines (64 loc) · 3.52 KB
Raw

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. delete WAITING FOR lock_mode X locks rec but not gap
  2. delete WAITING FOR lock mode X, HOLDS lock_mode X locks rec but not gap
  3. 隔离级别：RR

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-01-22 20:48:08 7f4248516700
*** (1) TRANSACTION:
TRANSACTION 2268, 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 11, OS thread handle 0x7f4248494700, query id 1207 localhost 127.0.0.1 rj updating
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 4 n bits 96 index `uniq_a_b_c` of table `dltst`.`dltask` trx id 2268 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 2271, 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 9, OS thread handle 0x7f4248516700, query id 1208 localhost 127.0.0.1 rj updating
delete from dltask where a=’b’ and b=’a’ and c=’c’
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6 page no 4 n bits 96 index `uniq_a_b_c` of table `dltst`.`dltask` trx id 2271 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 4 n bits 96 index `uniq_a_b_c` of table `dltst`.`dltask` trx id 2271 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 Session 4
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 from dltask where a=’a’ and b=’b’ and c=’c’;

分析

这个死锁和 delete-wait-lock-mode-x-vs-delete-wait-lock-mode-x-holds-lock-mode-x-locks-rec-but-not-gap 场景是一样的，只是稍微要复杂一点。我推测的结果如下（不一定正确，如有不足，欢迎斧正）：

Session 1 Session 2 Session 3 Session 4
获取记录锁（locks rec but not gap）
获取记录锁（locks rec but not gap），和事务一冲突，等待
获取记录锁（locks rec but not gap），和事务二冲突，等待
将记录标记为删除，并提交事务
记录锁获取成功
记录变动，需重新加锁，但记录锁保持不变
发起删除操作，由于记录标记为删除，所以获取 Next-key 锁，和事务三的记录锁冲突
重新加锁时获取 Next-key 锁，和事务四冲突，发生死锁 .

至此，事务三等待事务二，事务四等待事务三，事务二等待事务四，三个事务相互等待导致死锁。

注意死锁日志中只会显示两个事务，在遇到多个事务导致死锁时就不好分析。

参考

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