Aurora MySQL V2 RDS中my_tbl表偶发死锁原因排查

涉及的表结构 #

CREATE TABLE `my_tbl` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` datetime DEFAULT NULL,
  `col2` varchar(20) NOT NULL,
  `col3` int(11) NOT NULL,
  `col4` int(11) NOT NULL DEFAULT '0',
  `col5` int(1) NOT NULL DEFAULT '2',
  `col6` varchar(1100) CHARACTER SET utf8mb4 DEFAULT NULL,
  `col7` decimal(5,2) NOT NULL DEFAULT '0.00',
  `col8` datetime DEFAULT NULL,
  `col9` datetime DEFAULT NULL,
  `col10` int(11) DEFAULT '1',
  PRIMARY KEY (`id`,`col3`),
  KEY `col2` (`col2`),
  KEY `col4` (`col4`),
  KEY `col3_index` (`col3`,`col2`),
  KEY `idx_my_tbl_col3_col4` (`col3`,`col4`),
  KEY `idx_my_tbl_col2_col3_col4` (`col2`,`col3`,`col4`),
  KEY `idx_my_tbl_col8_col3_col4` (`col8`,`col3`,`col4`),
  KEY `idx_my_tbl_col8_col4` (`col8`,`col4`),
  KEY `col8Index` (`col8`),
  KEY `idx_my_tbl` (`col3`,`col2`,`col4`,`col10`)
) ENGINE=InnoDB AUTO_INCREMENT=154215634 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (col3)
PARTITIONS 10 */

该表基于col3哈希分为10个分区。

死锁日志（SHOW ENGINE INNODB STATUS输出） #

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-07-12 18:51:12 0x2b79ae229700
*** (1) TRANSACTION:
TRANSACTION 36372591069, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 196, OS thread handle 47777392129792, query id 244407 10.1.0.188 admin Searching rows for update
UPDATE my_tbl USE INDEX (idx_my_tbl_col2_col3_col4) SET col4 = 1, col8 = '2023/07/13 00:21:12' WHERE col3 = 16681539 AND col2 = '94790038425' AND col4 = 0
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 297 page no 344041 n bits 0 index idx_my_tbl_col2_col3_col4 of table `mydb`.`my_tbl` /* Partition `p9` */ trx id 36372591069 lock_mode X waiting
Record lock, heap no 375 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len=11; bufptr=0x2b75e7c12abf; hex= 3934373930303338343235; asc 94790038425;;
 1: len=4; bufptr=0x2b75e7c12aca; hex= 80fe8a43; asc    C;;
 2: len=4; bufptr=0x2b75e7c12ace; hex= 80000000; asc     ;; 
 3: len=4; bufptr=0x2b75e7c12ad2; hex= 88be96ec; asc     ;; 

*** (2) TRANSACTION:
TRANSACTION 36372591067, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 195, OS thread handle 47777424361216, query id 244405 10.1.0.143 admin updating
UPDATE my_tbl USE INDEX (idx_my_tbl_col2_col3_col4) SET col4 = 1, col8 = '2023/07/13 00:21:12' WHERE col3 = 16681529 AND col2 = '94790038425' AND col4 = 0
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 297 page no 344041 n bits 0 index idx_my_tbl_col2_col3_col4 of table `mydb`.`my_tbl` /* Partition `p9` */ trx id 36372591067 lock_mode X
Record lock, heap no 374 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len=11; bufptr=0x2b75e7c12aa2; hex= 3934373930303338343235; asc 94790038425;;
 1: len=4; bufptr=0x2b75e7c12aad; hex= 80fe8a39; asc    9;;
 2: len=4; bufptr=0x2b75e7c12ab1; hex= 80000000; asc     ;; 
 3: len=4; bufptr=0x2b75e7c12ab5; hex= 88bc168c; asc     ;; 

[bitmap of 256 bytes in hex: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 c0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ]
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 297 page no 344041 n bits 0 index idx_my_tbl_col2_col3_col4 of table `mydb`.`my_tbl` /* Partition `p9` */ trx id 36372591067 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 375 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len=11; bufptr=0x2b75e7c12abf; hex= 3934373930303338343235; asc 94790038425;;
 1: len=4; bufptr=0x2b75e7c12aca; hex= 80fe8a43; asc    C;;
 2: len=4; bufptr=0x2b75e7c12ace; hex= 80000000; asc     ;; 
 3: len=4; bufptr=0x2b75e7c12ad2; hex= 88be96ec; asc     ;; 

*** WE ROLL BACK TRANSACTION (1)
------------

死锁原因分析 #

1. 索引结构与记录位置
   两条UPDATE语句强制使用idx_my_tbl_col2_col3_col4索引，该索引顺序为col2→col3→col4。两条语句的col2和col4值完全相同（94790038425和0），仅col3不同，因此对应的两条记录在索引B+树中处于相邻位置（heap no 374和375），且因哈希分区规则，都落在p9分区的同一索引页上。

2. InnoDB锁机制与循环等待
   在默认的可重复读隔离级别下，InnoDB会通过间隙锁防止幻读：

   • 事务2先执行，给heap no 374的记录加排他锁（X锁），同时锁住该记录与heap no 375之间的间隙。
   • 事务1随后执行，尝试给heap no 375的记录加X锁，但该记录被事务2的间隙锁覆盖，进入等待状态。
   • 事务2更新完heap no 374的记录后，因col4从0改为1，需要修改其在idx_my_tbl_col2_col3_col4索引中的位置（删除旧索引项并插入新项），此时需要获取heap no 375前的间隙插入意向锁，但该间隙已被事务1的X锁请求占用，事务2也进入等待。

   最终形成循环等待链：事务1等待事务2释放heap no 375的锁，事务2等待事务1释放间隙锁，触发InnoDB死锁检测，系统回滚了事务1以打破死锁。

内容的提问来源于stack exchange，提问作者Nuwan Madushanka