Skip to content

Latest commit

 

History

History
125 lines (100 loc) · 5.47 KB

20.md

File metadata and controls

125 lines (100 loc) · 5.47 KB
Raw

Multiple indexes used to get X lock lead to deadlock

数据库信息

  1. mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using EditLine wrapper
  2. RR(REPEATABLE-READ) Isolation level

死锁特征

  1. update WAITING FOR lock_mode X locks rec but not gap waiting
  2. update WAITING FOR lock_mode X locks rec but not gap waiting, hold lock_mode X locks rec but not gap

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-08-22 09:25:58 0x7f8d9b111700
*** (1) TRANSACTION:
TRANSACTION 121318803, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 7 lock struct(s), heap size 1136, 6 row lock(s)
MySQL thread id 3321668, OS thread handle 140246167299840, query id 145836743 localhost momo Creating sort index
SELECT `rank24h`.`id`, `rank24h`.`date`, `rank24h`.`amount`, `rank24h`.`reward`, `rank24h`.`symbol` FROM `rank24h` WHERE (`rank24h`.`date` = '2019-08-23' AND `rank24h`.`symbol` = 'GOLD') ORDER BY `rank24h`.`id` ASC  LIMIT 1 FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1127 page no 3 n bits 128 index PRIMARY of table `business`.`rank24h` trx id 121318803 lock_mode X locks rec but not gap waiting
Record lock, heap no 51 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 80000032; asc    2;;
 1: len 6; hex 0000073b2d5c; asc    ;-\;;
 2: len 7; hex 7e000001f72da0; asc ~    - ;;
 3: len 3; hex 8fc717; asc    ;;
 4: len 10; hex 80000000530000000000; asc     S     ;;
 5: len 10; hex 80000000140000000000; asc           ;;
 6: len 4; hex 56495441; asc SILVER;;

*** (2) TRANSACTION:
TRANSACTION 121318802, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
6 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 3321665, OS thread handle 140246168704768, query id 145836737 localhost momo Creating sort index
SELECT `rank24h`.`id`, `rank24h`.`date`, `rank24h`.`amount`, `rank24h`.`reward`, `rank24h`.`symbol` FROM `rank24h` WHERE (`rank24h`.`date` = '2019-08-23' AND `rank24h`.`symbol` = 'SILVER') ORDER BY `rank24h`.`id` ASC  LIMIT 1 FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1127 page no 3 n bits 128 index PRIMARY of table `business`.`rank24h` trx id 121318802 lock_mode X locks rec but not gap
Record lock, heap no 51 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 80000032; asc    2;;
 1: len 6; hex 0000073b2d5c; asc    ;-\;;
 2: len 7; hex 7e000001f72da0; asc ~    - ;;
 3: len 3; hex 8fc717; asc    ;;
 4: len 10; hex 80000000530000000000; asc     S     ;;
 5: len 10; hex 80000000140000000000; asc           ;;
 6: len 4; hex 56495441; asc SILVER;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1127 page no 4 n bits 128 index rank24h_date_8afc2781 of table `business`.`rank24h` trx id 121318802 lock_mode X locks rec but not gap waiting
Record lock, heap no 51 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 8fc717; asc    ;;
 1: len 4; hex 80000032; asc    2;;

*** WE ROLL BACK TRANSACTION (2)

表结构

DROP TABLE IF EXISTS `rank24h`;
CREATE TABLE `rank24h` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `amount` decimal(20,10) NOT NULL,
  `reward` decimal(20,10) NOT NULL,
  `symbol` varchar(8) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `rank24h_date_8afc2781` (`date`),
  KEY `rank24h_symbol_b5eff497` (`symbol`)
) ENGINE=InnoDB AUTO_INCREMENT=57 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

初始数据：

INSERT INTO `rank24h` VALUES ('2019-08-23', 100, 100, 'GOLD');
INSERT INTO `rank24h` VALUES ('2019-08-23', 100, 100, 'SILVER');

重现步骤

由于是多索引引起的死锁，暂时无法在数据库层面重现。参考下面的分析过程。

分析

  1. 事务二给 symbol 加锁 (非聚簇索引加锁)
  2. 事务二给 id=1 加锁 (聚簇索引加锁)
  3. 事务一给 symbol 加锁 (非聚簇索引加锁)
  4. 事务一给 id=2 加锁 (聚簇索引加锁)
  5. 事务二给 date 加锁 (非聚簇索引加锁)
  6. 事务二给 id=1,id=2 加锁，和事务一冲突，等待 (聚簇索引加锁)
  7. 事务一给 date加锁，和事务二冲突，导致死锁 (非聚簇索引加锁)

变种

在使用单一索引和多索引并发更新的时候也会出现死锁：

-- 单索引　事务－
SELECT `rank24h`.`id`, `rank24h`.`date`, `rank24h`.`amount`, `rank24h`.`reward`, `rank24h`.`symbol` FROM `rank24h` WHERE `rank24h`.`date` = '2019-08-23' FOR UPDATE 

-- 多索引　事务二
SELECT `rank24h`.`id`, `rank24h`.`date`, `rank24h`.`amount`, `rank24h`.`reward`, `rank24h`.`symbol` FROM `rank24h` WHERE (`rank24h`.`date` = '2019-08-23' AND `rank24h`.`symbol` = 'GOLD') ORDER BY `rank24h`.`id` ASC  LIMIT 1 FOR UPDATE
  1. 事务二给 symbol 加锁 (非聚簇索引加锁)
  2. 事务二给 id=1 加锁 (聚簇索引加锁)
  3. 事务一给 date 加锁 (非聚簇索引加锁)
  4. 事务一给 id=1,id=2 加锁，和事务二冲突，等待 (聚簇索引加锁)
  5. 事务二给 date 加锁，和事务一冲突，导致死锁 (非聚簇索引加锁)

参考

  1. 解决死锁之路（终结篇） - 再见死锁
  2. 史上最全的select加锁分析(Mysql)
  3. index merge 引起的死锁分析
  4. 一则由于索引导致的MySQL死锁分析