Skip to content

Latest commit

 

History

History
98 lines (78 loc) · 5.02 KB

1.md

File metadata and controls

98 lines (78 loc) · 5.02 KB
Raw

insert-wait-lock-mode-x-insert-intention-vs-insert-wait-lock-mode-x-insert-intention-holds-lock-mode-x

死锁特征

  1. insert WAITING FOR lock_mode X insert intention waiting
  2. insert WAITING FOR lock_mode X insert intention waiting, HOLDS lock_mode X
  3. 隔离级别：RR

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-12-23 15:47:11 1f4c
*** (1) TRANSACTION:
TRANSACTION 19896526, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 17988, OS thread handle 0x17bc, query id 5701353 localhost 127.0.0.1 root update
insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition,  nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, 4, 181, 561)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49735 page no 4 n bits 72 index `UK_cagoa3q409gsukj51ltiokjoh` of   table `db`.`playerclub` trx id 19896526 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 19896542, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 17979, OS thread handle 0x1f4c, query id 5701360 localhost 127.0.0.1    root update
insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition,   nextClubId, account_id) values (0, '2014-12-23 15:47:11.611', 180, 4, 181, 563)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 49735 page no 4 n bits 72 index `UK_cagoa3q409gsukj51ltiokjoh` of   table `db`.`playerclub` trx id 19896542 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;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49735 page no 4 n bits 72 index `UK_cagoa3q409gsukj51ltiokjoh` of    table `db`.`playerclub` trx id 19896542 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

表结构

CREATE TABLE `PlayerClub` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `modifiedBy` bigint(20) DEFAULT NULL,
  `timeCreated` datetime NOT NULL,
  `account_id` bigint(20) DEFAULT NULL,
  `currentClubId` bigint(20) DEFAULT NULL,
  `endingLevelPosition` int(11) NOT NULL,
  `nextClubId` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_cagoa3q409gsukj51ltiokjoh` (`account_id`),
  KEY `FK_cagoa3q409gsukj51ltiokjoh` (`account_id`),
  CONSTRAINT `FK_cagoa3q409gsukj51ltiokjoh` FOREIGN KEY (`account_id`) REFERENCES   `PlayerAccount` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

重现步骤

Session 1 Session 2
delete from PlayerClub where account_id = 561;
delete from PlayerClub where account_id = 563;
insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, 4, 181, 561);
insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, 4, 181, 563);

分析

从死锁日志可以看出事务 2 HOLDS lock_mode X 锁（Next-key锁），并且这个锁锁在 supremum 这条记录上，这个锁不可能是 insert 语句加的，可以推测在 insert 语句前面肯定还有其他 sql 语句，譬如：

delete from PlayerClub where account_id = 561;

或者：

update PlayerClub set ... where account_id = 561;

这里有一个有意思的点，如果要删除的二级索引 account_id = 561 不存在，按理来说加的应该是 gap 锁（locks gap before rec），但是这里看到的日志却是 lock_mode X，也就是 Next-key 锁；而且更有意思的是，按理来说两个 Next-key 锁应该是冲突的，但是这里却两个事务同时在 supremum 这条记录上加了 Next-key 锁。我的理解是，这里是 gap 锁，而非 Next-key 锁，不能被日志误导，所以看到 supremum 的时候要特别注意（supremum 的 heap no = 1）。

另外，插入意向锁也和 gap 锁类似，如果在最大值处插入，日志显示 lock_mode X insert intention，如果在中间处插入，显示 lock_mode X locks gap before rec insert intention，也要注意。

参考

  1. MySQL InnoDB Deadlock For 2 simple insert queries
  2. Why am I getting deadlock in MySQL
  3. 一个线上死锁问题分析
  4. https://github.com/octachrome/innodb-locks
  5. 一个死锁问题