blog:mysql:production-problems:3

MySQL死锁场景【insert on duplicate 导致的死锁】

背景介绍

  • MySQL版本：5.7.16
  • 数据库的隔离级别为RR
经测试，在MySQL8中未发现此问题

场景模拟

CREATE TABLE `song_rank` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `songId` INT(11) NOT NULL,
  `weight` INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `songId_idx` (`songId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
 
 
mysql> INSERT INTO song_rank (songId, weight) VALUES (10, 100), (20, 100);
Query OK, 2 ROWS affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM song_rank;
+----+--------+--------+
| id | songId | weight |
+----+--------+--------+
| 10 |     10 |    100 |
| 11 |     20 |    100 |
+----+--------+--------+
2 ROWS IN SET (0.00 sec)
session 1 session 2session 3
step-1 begin;
step-2 insert into song_rank(songId,weight) values(11,100) on duplicate key update weight=weight+1;
step-3 begin;
step-4 insert into song_rank(songId,weight) values(12,100) on duplicate key update weight=weight+1;
step-5 begin;
step-6 insert into song_rank(songId,weight) values(13,100) on duplicate key update weight=weight+1;
step-7 rollback;
Query OK, 0 rows affected (1.02 sec)
step-8 Query OK, 1 row affected (17.84 sec) 1213 - Deadlock found when trying to get lock; try restarting transaction

问题分析

1、查看死锁日志

show engine innodb status;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-04-21 17:59:32 0x7f021c5e3700
*** (1) TRANSACTION:
TRANSACTION 559227425, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3647007, OS thread handle 139647058839296, query id 1764961729 localhost root update
insert into song_rank(songId,weight) values(13,100) on duplicate key update weight=weight+1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5474 page no 4 n bits 72 index songId_idx of table `okami`.`song_rank` trx id 559227425 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 8000000b; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 559227356, ACTIVE 16 sec inserting
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 3647012, OS thread handle 139647042598656, query id 1764961109 localhost root update
insert into song_rank(songId,weight) values(12,100) on duplicate key update weight=weight+1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5474 page no 4 n bits 72 index songId_idx of table `okami`.`song_rank` trx id 559227356 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 8000000b; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5474 page no 4 n bits 72 index songId_idx of table `okami`.`song_rank` trx id 559227356 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 8000000b; asc     ;;

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

2、加锁日志分析

事务1

  • 执行的SQL
INSERT INTO  song_rank(songId,weight) VALUES(3,100) ON duplicate KEY UPDATE  weight=weight+1
  • 正在等待的锁: songId_idx索引的插入意向排查锁
INDEX songId_idx OF TABLE `okami`.`song_rank` trx id 545836086 lock_mode X locks gap BEFORE rec INSERT intention waiting

事务2

  • 正在执行的SQL
INSERT INTO  song_rank(songId,weight) VALUES(2,100) ON duplicate KEY UPDATE  weight=weight+1
  • 正在持有的锁: songId_idx索引的间隙锁
index songId_idx of table `okami`.`song_rank` trx id 545835906 lock_mode X locks gap before rec
  • 正在等待的锁：songId_idx索引的插入意向排他锁
index songId_idx of table `okami`.`song_rank` trx id 545835906 lock_mode X locks gap before rec insert intention waiting

3、SQL加锁分析

官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

insert加锁策略

insert语句会对插入的这条记录加排他记录锁，在加记录锁之前还会加一种 GAP 锁，叫做插入意向(insert intention)锁，如果出现唯一键冲突，还会加一个共享记录(S)锁。

—-

  • session1 执行SQL insert into songrank(songId,weight) values(11,100) on duplicate key update weight=weight+1; - session1 会持有 一个间隙锁（lockmode X locks gap before rec）
---TRANSACTION 559216739, ACTIVE 13 sec
2 LOCK struct(s), heap SIZE 1136, 2 ROW LOCK(s), undo log entries 1
MySQL thread id 3646813, OS thread handle 139647039670016, query id 1764905750 localhost root starting
SHOW engine innodb STATUS
TABLE LOCK TABLE `okami`.`song_rank` trx id 559216739 LOCK mode IX
RECORD LOCKS SPACE id 5474 page no 4 n bits 72 INDEX songId_idx OF TABLE `okami`.`song_rank` trx id 559216739 lock_mode X locks gap BEFORE rec
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; ASC     ;;
 1: len 4; hex 8000000b; ASC     ;;
 
Record LOCK, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000b; ASC     ;;
 1: len 4; hex 8000000c; ASC     ;;
  • session2 执行SQL insert into song_rank(songId,weight) values(12,100) on duplicate key update weight=weight+1;
  • 由于Gap锁之间不冲突，session2 会持有 Gap锁 ，因为sesison1持有间隙锁，插入意向锁和间隙锁冲突，所以需要等待获取 insert intention lock（插入意向锁）
---TRANSACTION 559227356, ACTIVE 5 sec inserting
mysql TABLES IN USE 1, locked 1
LOCK WAIT 3 LOCK struct(s), heap SIZE 1136, 2 ROW LOCK(s), undo log entries 1
MySQL thread id 3646826, OS thread handle 139647068956416, query id 1764917435 localhost root UPDATE
INSERT INTO song_rank(songId,weight) VALUES(12,100) ON duplicate KEY UPDATE weight=weight+1
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 5474 page no 4 n bits 72 INDEX songId_idx OF TABLE `okami`.`song_rank` trx id 559227356 lock_mode X locks gap BEFORE rec INSERT intention waiting
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; ASC     ;;
 1: len 4; hex 8000000b; ASC     ;;
 
------------------
TABLE LOCK TABLE `okami`.`song_rank` trx id 559219332 LOCK mode IX
RECORD LOCKS SPACE id 5474 page no 4 n bits 72 INDEX songId_idx OF TABLE `okami`.`song_rank` trx id 559227356 lock_mode X locks gap BEFORE rec
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; ASC     ;;
 1: len 4; hex 8000000b; ASC     ;;
 
RECORD LOCKS SPACE id 5474 page no 4 n bits 72 INDEX songId_idx OF TABLE `okami`.`song_rank` trx id 559227356 lock_mode X locks gap BEFORE rec INSERT intention waiting
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; ASC     ;;
 1: len 4; hex 8000000b; ASC     ;;
  • session3 执行SQL insert into song_rank(songId,weight) values(13,100) on duplicate key update weight=weight+1;
  • session2 和session3 同时等待获取insert intention lock（插入意向锁）。
---TRANSACTION 559227425, ACTIVE 17 sec inserting
mysql TABLES IN USE 1, locked 1
LOCK WAIT 3 LOCK struct(s), heap SIZE 1136, 2 ROW LOCK(s), undo log entries 1
MySQL thread id 3646955, OS thread handle 139647074281216, query id 1764945161 localhost root UPDATE
INSERT INTO song_rank(songId,weight) VALUES(13,100) ON duplicate KEY UPDATE weight=weight+1
------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 5474 page no 4 n bits 72 INDEX songId_idx OF TABLE `okami`.`song_rank` trx id 559227425 lock_mode X locks gap BEFORE rec INSERT intention waiting
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; ASC     ;;
 1: len 4; hex 8000000b; ASC     ;;
 
------------------
TABLE LOCK TABLE `okami`.`song_rank` trx id 559224240 LOCK mode IX
RECORD LOCKS SPACE id 5474 page no 4 n bits 72 INDEX songId_idx OF TABLE `okami`.`song_rank` trx id 559227425 lock_mode X locks gap BEFORE rec
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; ASC     ;;
 1: len 4; hex 8000000b; ASC     ;;
 
RECORD LOCKS SPACE id 5474 page no 4 n bits 72 INDEX songId_idx OF TABLE `okami`.`song_rank` trx id 559227425 lock_mode X locks gap BEFORE rec INSERT intention waiting
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; ASC     ;;
 1: len 4; hex 8000000b; ASC     ;;
 
---TRANSACTION 559227356, ACTIVE 29 sec inserting
mysql TABLES IN USE 1, locked 1
LOCK WAIT 3 LOCK struct(s), heap SIZE 1136, 2 ROW LOCK(s), undo log entries 1
MySQL thread id 3646826, OS thread handle 139647068956416, query id 1764944225 localhost root UPDATE
INSERT INTO song_rank(songId,weight) VALUES(12,100) ON duplicate KEY UPDATE weight=weight+1
------- TRX HAS BEEN WAITING 29 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 5474 page no 4 n bits 72 INDEX songId_idx OF TABLE `okami`.`song_rank` trx id 559227356 lock_mode X locks gap BEFORE rec INSERT intention waiting
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; ASC     ;;
 1: len 4; hex 8000000b; ASC     ;;
 
------------------
TABLE LOCK TABLE `okami`.`song_rank` trx id 559224130 LOCK mode IX
RECORD LOCKS SPACE id 5474 page no 4 n bits 72 INDEX songId_idx OF TABLE `okami`.`song_rank` trx id 559227356 lock_mode X locks gap BEFORE rec
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; ASC     ;;
 1: len 4; hex 8000000b; ASC     ;;
 
RECORD LOCKS SPACE id 5474 page no 4 n bits 72 INDEX songId_idx OF TABLE `okami`.`song_rank` trx id 559227356 lock_mode X locks gap BEFORE rec INSERT intention waiting
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; ASC     ;;
 1: len 4; hex 8000000b; ASC     ;;
 
---TRANSACTION 559224078, ACTIVE 38 sec
2 LOCK struct(s), heap SIZE 1136, 2 ROW LOCK(s), undo log entries 1
MySQL thread id 3646813, OS thread handle 139647039670016, query id 1764946672 localhost root starting
SHOW engine innodb STATUS
TABLE LOCK TABLE `okami`.`song_rank` trx id 559224078 LOCK mode IX
RECORD LOCKS SPACE id 5474 page no 4 n bits 72 INDEX songId_idx OF TABLE `okami`.`song_rank` trx id 559224078 lock_mode X locks gap BEFORE rec
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; ASC     ;;
 1: len 4; hex 8000000b; ASC     ;;
 
Record LOCK, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000b; ASC     ;;
 1: len 4; hex 80000011; ASC     ;;
  • session1 执行 rollback。session2 插入成功，session3 提示死锁。通过 show engine innodb status\G查看锁情况，看到如下死锁内容
  • session1 执行rollback, 释放Gap锁。 session2 和 session3此时同时持有Gap锁。插入意向锁和间隙锁冲突，所以session2和session3同时等待获取插入意向锁。造成死锁
  • session2 要获取插入意向锁，就得等待session3 间隙锁的释放。session3要获取插入意向锁，也得等待 session2的间隙锁释放
LATEST DETECTED DEADLOCK
------------------------
2022-04-21 17:59:32 0x7f021c5e3700
*** (1) TRANSACTION:
TRANSACTION 559227425, ACTIVE 5 sec inserting
mysql TABLES IN USE 1, locked 1
LOCK WAIT 3 LOCK struct(s), heap SIZE 1136, 2 ROW LOCK(s), undo log entries 1
MySQL thread id 3647007, OS thread handle 139647058839296, query id 1764961729 localhost root UPDATE
INSERT INTO song_rank(songId,weight) VALUES(13,100) ON duplicate KEY UPDATE weight=weight+1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 5474 page no 4 n bits 72 INDEX songId_idx OF TABLE `okami`.`song_rank` trx id 559227425 lock_mode X locks gap BEFORE rec INSERT intention waiting
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; ASC     ;;
 1: len 4; hex 8000000b; ASC     ;;
 
*** (2) TRANSACTION:
TRANSACTION 559227356, ACTIVE 16 sec inserting
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 3647012, OS thread handle 139647042598656, query id 1764961109 localhost root UPDATE
INSERT INTO song_rank(songId,weight) VALUES(12,100) ON duplicate KEY UPDATE weight=weight+1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS SPACE id 5474 page no 4 n bits 72 INDEX songId_idx OF TABLE `okami`.`song_rank` trx id 559227356 lock_mode X locks gap BEFORE rec
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; ASC     ;;
 1: len 4; hex 8000000b; ASC     ;;
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 5474 page no 4 n bits 72 INDEX songId_idx OF TABLE `okami`.`song_rank` trx id 559227356 lock_mode X locks gap BEFORE rec INSERT intention waiting
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; ASC     ;;
 1: len 4; hex 8000000b; ASC     ;;
 
*** WE ROLL BACK TRANSACTION (1)

4、解决方案

  • 这是 MySQL官方的bug
  • 在MySQL8中，已经修复这个问题。该场景不会再产生死锁。可以通过升级MySQL来处理这个问题
  • 如果不想调整MySQl的版本，可以调整sql。
   try{
       insert();
   }catch(DuplicateKeyException e){
       // 插入异常，证明记录已经存在。改为update
       update();
   }
, , ,
  • blog/mysql/production-problems/3.txt
  • 最后更改: 2022/04/21 16:25
  • 由 okami