Skip to content

Latest commit

 

History

History
114 lines (89 loc) · 5.12 KB

17.md

File metadata and controls

114 lines (89 loc) · 5.12 KB

update-wait-lock-mode-x-locks-gap-before-rec-insert-intention-vs-update-wait-lock-mode-x-locks-gap-before-rec-insert-intention-holds-lock-mode-x

死锁特征

  1. update WAITING FOR lock_mode X locks gap before rec insert intention
  2. update WAITING FOR lock_mode X locks gap before rec insert intention, HOLDS lock_mode X

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-03-31 02:50:16 0x7f6d1817a700
*** (1) TRANSACTION:
TRANSACTION 399960, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 8 row lock(s), undo log entries 1
MySQL thread id 29, OS thread handle 140106531567360, query id 596255 localhost root updating
update t16 set xid = 3, valid = 1 where xid = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 4 n bits 80 index xid_valid of table `dldb`.`t16` trx id 399960 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000001; asc     ;;
 2: len 4; hex 80000006; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 399959, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 8 row lock(s), undo log entries 2
MySQL thread id 27, OS thread handle 140106532366080, query id 596254 localhost root updating
update t16 set xid = 3, valid = 0 where xid = 3
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 23 page no 4 n bits 80 index xid_valid of table `dldb`.`t16` trx id 399959 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000001; asc     ;;
 2: len 4; hex 80000003; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000001; asc     ;;
 2: len 4; hex 80000006; asc     ;;

Record lock, heap no 10 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000000; asc     ;;
 2: len 4; hex 80000009; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 4 n bits 80 index xid_valid of table `dldb`.`t16` trx id 399959 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 10 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000000; asc     ;;
 2: len 4; hex 80000009; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

表结构

CREATE TABLE `t16` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `xid` int(11) DEFAULT NULL,
  `valid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `xid_valid` (`xid`,`valid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

初始数据：

INSERT INTO t16(id, xid, valid) VALUES(1, 1, 0);
INSERT INTO t16(id, xid, valid) VALUES(2, 2, 1);
INSERT INTO t16(id, xid, valid) VALUES(3, 3, 1);
INSERT INTO t16(id, xid, valid) VALUES(4, 1, 0);
INSERT INTO t16(id, xid, valid) VALUES(5, 2, 0);
INSERT INTO t16(id, xid, valid) VALUES(6, 3, 1);
INSERT INTO t16(id, xid, valid) VALUES(7, 1, 1);
INSERT INTO t16(id, xid, valid) VALUES(8, 2, 1);
INSERT INTO t16(id, xid, valid) VALUES(9, 3, 0);
INSERT INTO t16(id, xid, valid) VALUES(10, 1, 1);

重现步骤

Session 1 Session 2
update t16 set xid = 3, valid = 1 where xid = 2; update t16 set xid = 3, valid = 0 where xid = 3;

分析

首先要明白 UPDATE 的加锁顺序：在 InnoDB 中，通过二级索引更新记录，首先会在 WHERE 条件使用到的二级索引上加 Next-Key 类型的X锁，以防止查找记录期间的其它插入/删除记录，然后通过二级索引找到 primary key 并在 primary key 上加 Record 类型的X锁，之后更新记录并检查更新字段是否是其它索引中的某列，如果存在这样的索引，通过 update 的旧值到二级索引中删除相应的 entry，此时x锁类型为 Record。

这个死锁和案例 16 的场景一模一样。

上例中，对二级索引 xid_valid 的更新过程如下所示：

在事务 2 中， update t16 set xid = 3, valid = 0 where xid = 3 首先会对 xid = 3 的三条记录加上 Next-Key 锁，并在 supremum 上加 Gap 锁，然后开始更新二级索引，由于更新的时候会将新纪录插入到新的位置，所以和 INSERT 加锁流程类似，需要加插入意向锁，如果该位置有 Gap 锁，则会阻塞。

同理，在事务 1 中，update t16 set xid = 3, valid = 1 where xid = 2 首先会对 xid = 2 的三条记录加上 Next-Key 锁，并在 2-1|8 和 3-0|9 之间加上 Gap 锁，很显然，上面的事务 2 会往 2-1|8 和 3-0|9 之间插入新记录，所以事务 2 阻塞。同时，事务 1 在处理插入新记录时，和事务 2 的几个 Next-Key 锁冲突，从而导致死锁。

参考

  1. InnoDB inplace-update加锁流程分析 | Learn and live.