Skip to content

Latest commit

 

History

History
108 lines (86 loc) · 4.47 KB

8.md

File metadata and controls

108 lines (86 loc) · 4.47 KB
Raw

delete-wait-lock-mode-x-locks-rec-but-not-gap-vs-delete-wait-lock-mode-x-locks-rec-but-not-gap-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 locks rec but not gap, HOLDS lock_mode X locks rec but not gap

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-04-03 13:22:29 0xbd0
*** (1) TRANSACTION:
TRANSACTION 245852, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 91, OS thread handle 6964, query id 366044 localhost ::1 root updating
delete from t where id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 87 page no 3 n bits 72 index PRIMARY of table `sys`.`t` trx id 245852 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000003c05d; asc      ];;
 2: len 7; hex 70000001850bf6; asc p      ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000006; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 245853, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 93, OS thread handle 3024, query id 366045 localhost ::1 root updating
delete from t where id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 87 page no 3 n bits 72 index PRIMARY of table `sys`.`t` trx id 245853 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000003c05d; asc      ];;
 2: len 7; hex 70000001850bf6; asc p      ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000006; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 87 page no 3 n bits 72 index PRIMARY of table `sys`.`t` trx id 245853 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000003c05c; asc      \;;
 2: len 7; hex 6f0000015a1a7e; asc o   Z ~;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000002; asc     ;;
 5: len 4; hex 80000003; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

表结构

CREATE TABLE `t` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `a` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB

重现步骤

Session 1 Session 2
delete from t where id = 1
delete from t where id = 2
delete from t where id = 2
delete from t where id = 1

也可以通过下面的 mysqlslap 命令模拟死锁场景：

mysqlslap --create-schema sys -q "begin; delete from t where id = 1; delete from t where id = 2; rollback;" --number-of-queries=100000  -uroot -p
mysqlslap --create-schema sys -q "begin; delete from t where id = 2; delete from t where id = 1; rollback;" --number-of-queries=100000  -uroot -p

分析

死锁的根本原因是有两个或多个事务之间加锁顺序的不一致导致的，这个死锁案例其实是最经典的死锁场景。

首先，事务一获取 id = 1 的锁，事务二获取 id = 2 的锁；然后，事务一试图获取 id = 2 的锁，事务二试图获取 id = 1 的锁，相互等待导致死锁。

这个死锁并不难理解，不过根据这个死锁日志倒推出死锁场景却并不容易，因为有些死锁场景和这个几乎一模一样，譬如 delete-wait-lock-mode-x-locks-rec-but-not-gap-vs-delete-wait-lock-mode-x-locks-rec-but-not-gap-holds-lock-mode-x-locks-rec-but-not-gap，而且这种死锁很可能是三个或三个以上的事务导致的，譬如下面这样：

Session 1 Session 2 Session 3
delete from t where id = 1 delete from t where id = 2 delete from t where id = 3
delete from t where id = 1
delete from t where id = 2
delete from t where id = 3

这种场景的死锁日志和上述两个事务的死锁日志没有任何区别，所以具体情况还需要具体分析。

如果事务执行的两个 SQL 语句不属于同一个表，死锁情形是一样的，参考[1]。

参考

  1. MySQL死锁案例分析（一）