Skip to content

Latest commit

 

History

History
105 lines (84 loc) · 4.66 KB

9.md

File metadata and controls

105 lines (84 loc) · 4.66 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 09:50:13 0x2bec
*** (1) TRANSACTION:
TRANSACTION 239662, 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)
MySQL thread id 87, OS thread handle 7632, query id 356196 localhost ::1 root updating
delete from t where a = 4
*** (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 239662 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 00000003a82d; asc      -;;
 2: len 7; hex 57000001a82e44; asc W    .D;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000006; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 239661, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 4999
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 89, OS thread handle 11244, query id 356194 localhost ::1 root updating
delete from t where b = 5
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 87 page no 3 n bits 72 index PRIMARY of table `sys`.`t` trx id 239661 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 00000003a82d; asc      -;;
 2: len 7; hex 57000001a82e44; asc W    .D;;
 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 4 n bits 72 index idx_a_b of table `sys`.`t` trx id 239661 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 4; hex 80000005; asc     ;;
 2: len 4; hex 80000002; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

表结构

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

重现步骤

Session 1 Session 2
delete from t where a = 4 delete from t where b = 5

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

mysqlslap --create-schema sys -q "begin; delete from t where a = 4; rollback;" --number-of-queries=100000  -uroot -p
mysqlslap --create-schema sys -q "begin; delete from t where b = 5; rollback;" --number-of-queries=100000  -uroot -p

分析

这个死锁现象和 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 几乎是一模一样的，区别在于锁的索引不一样，前者正在等待索引 idx_a_b 上的锁，而后者正在等待 PRIMARY 上的锁。

这个其实也是典型的由于两个事务之间加锁顺序不一致导致的死锁场景，虽然两个事务都只有一条 SQL 语句，但是这两条语句加锁的顺序是有区别的，如下：

事务一 delete from t where a = 4 走 idx_a_b 索引，首先会对 a = 4 对应的 idx_a_b 二级索引加锁，然后对主键索引 id = 2 加锁，最后会给 a = 4 对应的 idx_b 二级索引加锁；事务二 delete from t where b = 5 走的是 idx_b 索引，首先对 b = 5 对应的 idx_b 二级索引加锁，然后对主键索引 id = 2 加锁，最后会给 b = 5 对应的 idx_a_b 二级索引加锁，如下：

Session 1 Session 2
对 idx_b 索引 b = 5 加锁
对主键 id = 2 加锁
对 idx_a_b 索引 a = 4 加锁
对主键 id = 2 加锁，等待
对 idx_a_b 索引 a = 4 加锁，等待，死锁

这里的例子是 delete 语句，通过二级索引删除记录的加锁顺序为：二级索引（WHERE使用到二级索引）–> 主键索引 –> 所有其它二级索引；如果是 update 语句，加锁顺序也类似，通过二级索引更新记录的加锁顺序为：二级索引（WHERE条件使用二级索引）–> 主键索引 –> 包含更新字段的其它二级索引。

参考

  1. InnoDB inplace-update加锁流程分析