mysql数据库先update后insert死锁分析

先update再insert的并发死锁问题分析。

背景

“如果库里有对应记录，就更新，没有就插入”

很简单的一个逻辑，相信很多人都会遇到。

最近看一个工程里实现代码是这样的，mysql数据库走的是默认的事务级别:可重复读。包在一个事务中执行：

1if update更新结果>0
2  then return "成功";
3else
4  insert一条新记录

看起来似乎没什么问题，线上频频出现insert死锁。

这里总结下，分享下实验SQL，便于有兴趣研究的同学去复现。

1CREATE TABLE `activity_log`(
2  `id`            bigint(20) unsigned NOT NULL AUTO_INCREMENT,
3  `user_id`       varchar(16) NOT NULL COMMENT '用户id',
4  `activity_id`   varchar(32) NOT NULL COMMENT '活动id',
5  `time_count`    int(11)     DEFAULT 0 COMMENT '活动参与次数',
6  PRIMARY KEY (`id`),
7  UNIQUE KEY `uniq_uid_activity_id` (`user_id`,`activity_id`))ENGINE=InnoDB CHARSET=utf8;
8
9//更新语句
10UPDATE activity_log SET time_count=time_count+1 WHERE `user_id`='5' AND `activity_id`='2020';
11//插入语句
12INSERT INTO activity_log(`user_id`,`activity_id`) VALUES('5','2020');

并发执行现场

死锁日志分析

查看最近的死锁日志mysql命令：show engine innodb status

12020-12-04 19:35:01 7f7aeb30c700
2*** (1) TRANSACTION:
3TRANSACTION 25906, ACTIVE 35 sec inserting 
4mysql tables in use 1, locked 1
5LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
6MySQL thread id 5509, OS thread handle 0x7f7b0d6f2700, query id 51970 localhost root update
7INSERT INTO activity_log(`user_id`,`activity_id`) VALUES('4','2020')
8*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
9RECORD LOCKS space id 44 page no 4 n bits 72 index `uniq_uid_activity_id` of table `test`.`activity_log` trx id 25906 lock_mode X insert intention waiting
10Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
11 0: len 8; hex 73757072656d756d; asc supremum;;
12
13*** (2) TRANSACTION:
14TRANSACTION 25907, ACTIVE 26 sec inserting
15mysql tables in use 1, locked 1
163 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
17MySQL thread id 5510, OS thread handle 0x7f7aeb30c700, query id 51971 localhost root update
18INSERT INTO activity_log(`user_id`,`activity_id`) VALUES('5','2020')
19*** (2) HOLDS THE LOCK(S):
20RECORD LOCKS space id 44 page no 4 n bits 72 index `uniq_uid_activity_id` of table `test`.`activity_log` trx id 25907 lock_mode X
21Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
22 0: len 8; hex 73757072656d756d; asc supremum;;
23
24*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
25RECORD LOCKS space id 44 page no 4 n bits 72 index `uniq_uid_activity_id` of table `test`.`activity_log` trx id 25907 lock_mode X insert intention waiting
26Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
27 0: len 8; hex 73757072656d756d; asc supremum;;
28
29*** WE ROLL BACK TRANSACTION (2)
30------------

mysql死锁日志中,不同类型锁对应的日志信息如下

记录锁(LOCK_REC_NOT_GAP): lock_mode X locks rec but not gap
间隙锁(LOCK_GAP): lock_mode X locks gap before rec
Next-key锁(LOCK_ORNIDARY): lock_mode X
插入意向锁(LOCK_INSERT_INTENTION): lock_mode X locks gap before rec insert intention

例外情况

如果在supremum record 上加锁，locks gap before rec 会省略掉，间隙锁会显示成 lock_mode X，插入意向锁会显示成 lock_mode X insert intention。譬如上面的

1RECORD LOCKS space id 44 page no 4 n bits 72 index `uniq_uid_activity_id` of table `test`.`activity_log` trx id 25907 lock_mode X
2Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
3 0: len 8; hex 73757072656d756d; asc supremum;;

看起来像是 Next-key 锁,但是看下面的 heap no 1 表示这个记录是 supremum record（另外.infimum record 的 heap no 为 0），所以这个锁应该看作是一个间隙锁。

在InnoDB存储引擎中，每个数据页中有两个虚拟的行记录，用来界定记录的边界。Infimum 是比该页中任何主键值都要小的值。Supremum 指的是比任何可能打的值还要大的值。这两个值在页创建时被建立，并且任何情况下不会删除。

最终分析

两个事务update不存在的记录，先后获得了间隙锁（gap锁），gap锁之间是兼容的所以在update环节不会阻塞。

二者都持有gap锁，然后去竞争插入意向锁。当存在其他会话持有gap锁的时候，当前会话申请不了插入意向锁，导致死锁。

推荐阅读:

《记一次mysql的insert死锁》《记一次mysql的update死锁》

mysql数据库先update后insert死锁分析

背景

并发执行现场

死锁日志分析

评论区

相关推荐