当并发insert on duplicate key update遇见死锁:更新丢失

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 数据库死锁问题,是一个老生常谈且很常见的问题,网上也有非常多对于各类死锁场景的解析和复现,但凡和死锁有关,无外乎不涉及数据库隔离等级、索引、以及innodb锁等相关原因。但是我这个案例我搜遍了全网也没能找到比较相似情况。于是我想尽可能的复现出这种情况,找出死锁的原因,找出可能出现的隐患。问题的背景:我们的数据库中的发生死锁的表是具有”多列组合构建的唯一索引“(不包含

数据库死锁问题,是一个老生常谈且很常见的问题,网上也有非常多对于各类死锁场景的解析和复现,但凡和死锁有关,无外乎不涉及数据库隔离等级、索引、以及innodb锁等相关原因。但是我这个案例我搜遍了全网也没能找到比较相似情况。于是我想尽可能的复现出这种情况,找出死锁的原因,找出可能出现的隐患。

问题的背景:

我们的数据库中的发生死锁的表是具有”多列组合构建的唯一索引“(不包含自增的主键),且数据库的隔离等级为Read Committed, 另外对于这个表来说是写入远大于读取的,由于业务的原因,经常会出现同一数据反复插入同一数据指唯一索引值相同的数据,但其他非索引字段可能不同),所以为了简化代码,我们使用insert on duplicate key update来解决这种问题,当mysql检测到唯一键冲突时,仅更新特定(非索引)字段。但是问题就出现在大规模多worker并发插入的时候,会经常出现"Deadlock found when trying to get lock"。开始真的是百思不得其解,于是乎开始疯狂查阅mysql手册以及各类博文尝试找到问题所在。

问题的现象:

一般定位死锁原因第一步就是执行”show engine innodb status“, 查看innodb Standard monitor输出结果,这里面会有数据库最后一次的死锁记录。会记录出现死锁的两个事务,它们分别在等待什么锁,并且手里持有什么锁。mysql在检测到发生死锁的时候,会随机回滚其中的一个事务,从而解开死锁。下面的截图是发生死锁的时候innodb status截图(和业务相关的数据已脱敏,这里均用column_n和value_n表示)

Transaction1:

Transaction2:

现象阐述:从上方两个截图可以发现,死锁均发生在insert on duplicate key update语句执行的时候,并且每个insert语句均为批量插入多个数据。对于事务一,可以看到事务一在等待某个锁的获取,且这个锁是"lock_mode X locks gap before rec insert intention waiting",直接翻译过来就是插入意向锁在等待排他gap锁的释放,也就是只有排他gap锁释放后插入意向锁才能获取到(关于这些锁的含义见下一节)。对于事务二,同样可以看到相同的一句话。并且两个事务的锁冲突均发生在”唯一索引“上。再进一步观察可以看到,事务二所持有("Holds the Locks"下方展示的索引值)的排它锁所在的索引(锁均是加在索引上或者索引区间上的),与事务一等待获取锁的索引是一样的。进一步展示了的确,在同一个索引上出现了一个等待获取,一个已经获取的冲突现象。

相关概念:

在分析问题前,有必要概述一下(详细了解可以见附录),这里面涉及到的锁相关知识。具体可以详见Mysql手册

innodb级锁按照隔离能力,主要分为共享锁(S锁)和排他锁(X锁)。事务T1的某行上持有S锁,则另一事务T2可以在此行获取S锁,但是不能获取此行的X锁,而如果T1在某行上持有X锁,则另一事务T2,对此行既无法获取S锁,也无法获取X锁。(除了S和X锁外,还有表级锁,分别是意向共享IS锁和意向排他IX锁,这里不做深入)。

按照锁的种类:主要有四种。

1. Record锁:这种锁会在索引上加锁,比如sql为select column_1 from table where column_1=1 for update,且column_1上有索引,则会把colunm_1为1的行都加排它锁,其他事务禁止对此行读和写。

2. Gap锁(间隙锁):这种锁作用在索引记录之间。目的只需要记住:他是为防止其他事务插入间隙包括防止insert方式插入新数据到间隙,以及update方式将其他行变更到此间隙)。Gap锁可以有效的防止”幻读“(因为这些间隙都被上了锁,其他事务不可能再插入数据到这些间隙中,于是当前事务在连续进行”当前读“时,每次读到的都是相同的记录)。虽然Gap锁只作用在隔离级别为RR及以上的数据库上,但是不意味着隔离等级为RC级别的不会使用,在RC级别,在进行外键约束检测和唯一键约束检测的时候,会使用到Gap锁,而正是这个duplicate-key checking导致了上文出现的死锁发生。关于Gap锁到底是如何加锁的,可以参阅这篇文章

3. Next-Key锁:本质上就是Gap锁和Record锁的结合,锁住索引外还要锁住索引的间隙。再具体一些就是,一个record锁,加上,位于此索引记录前的第一个间隙处的间隙锁。举个简单的例子就是,如果现在有一个索引包含三个值1,3,5,则next-key lock锁,可能锁住的范围就有(-∞,1],(1,3],(3,5],(5,+∞]。同样在next-key lock一般作用在RR隔离等级的数据库,但是当出现在insert时候,检测到唯一键冲突的时候,会在冲突所在唯一索引出和之前的间隙处加Next-key lock.

mysq官方手册中,对Next-key lock在innodb monitor中的打印如下图所示:

可以发现和我们在”问题的现象“一节贴的日志中事务二”Hold the Locks“处非常相似。所以可以怀疑当时死锁发生的时候,出现了排他的next-key lock。

4. Insert Intention锁(插入意向锁):顾名思义,这个锁是在数据插入之前会加此锁。它是一种轻量的Gap锁,同事也是意向排他锁的一种。它的存在使得多个事务在写入不同数据到统一索引间隙的时候,不会发生锁等待。另外由于它是一种意向插入锁,所以当排他锁已经处于间隙上的时候,根据锁的兼容矩阵,可以知道,意向插入锁必须等待此间隙上的排它锁释放,才能获取。

根据上面,对锁的种类说明,其实我们已经能猜到,大概是什么锁导致了死锁的出现。(这里我要再明确一点,我们的数据库隔离等级为Read Committed级别。)本质上就是两个事务同时获取到了不同间隙的X Next-key锁,而这个两个事务又同时想要向对方已经获取了next-key锁的间隙内插入新的数据于是乎死锁出现了。下面我们来完全复现一下。

问题的复现:

数据库准备:数据库中能够包含一个unique key: code

CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `code` int(11) NOT NULL,
  `other` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB 

初始数据:insert into test2 (code, other) values(1,1),(3,3),(5,5)

复现场景:原始的code字段1,3, 5,现在要在中间插入code2345row, 如果碰到唯一键约束则更新other字段。

Time Session1 Session2
T1

start transaction;

insert into test2(code, other) values (3, 4) on duplicate key update other = VALUES(other);

T2

start transaction;

insert into test2(code, other) values (5, 6) on duplicate key update other = VALUES(other);

T3

insert into test2(code, other) values (4, 4) on duplicate key update other = VALUES(other)

现象:一直阻塞

T4

insert into test2(code, other) values (2, 2) on duplicate key update other = VALUES(other);

现象:出现死锁,事务回滚 (2,2插入失败, 且code5时,更新other字段失败,更新和插入均丢失)

死锁出现后,我们查看innodb status中的死锁记录,如下:

可以发现,复现出来的结果,和上文中的案例几乎完全一致。下面我们对此结果进行分析。

问题的分析:

  1. 在T1时刻Session1执行完insert操作后,由于插入的code=2已经存在于表中,发生了唯一键冲突,所以触发了duplicate-checking,导致在(1,3]这个区间加上了next-key lock。这里,我为了进一步证明确实只有(1,3]这个区间加了锁。在T1时刻执行完后,验证插入code=0/4/6的数据可以在Session2中执行成功。同时这个时候Session2中可以修改code=1的数据,如update test2 set other=0 where code=1可以执行成功(当然你不能update test2 set code=2 where code=1,因为这个操作是在向(1,3]的间隙内插入了数据,违反了gap锁的要求)。同时我们可以证明这时code=3肯定是被排他锁锁住的,由于当出现唯一键冲突时,就会执行on duplicate key update,更新other字段,所以code=3一定在更新结束后处于排它锁锁定状态(补充说明:可以证明如果是共享锁的话,session2在T2时刻执行insert into test2(code, other) values (3, 33)语句的话,一定会立刻包duplicate error而不会阻塞。但是事实上如果Session2在T2时刻执行这句sql,会一直阻塞,进一步说明code=3加的是排它锁。另外需要注意的是,其实我目前只能非常确定code = 3是有排它锁,但是(1,3)上面,到底是S gap lock 还是X gap lock无法确定,不过无论是S还是X,不影响后续的解释。
  2. T2 完成时,同理也会在35]这个区间上X next-key lock (在上面的截图中也可以看到插入code=5后,正在插入code=2的时候,写着HOLD the lock hex 80000005)

  3. T1和T2执行完成之后,我们可以看到(1,3] 和(3,5]分别被Session1Session2锁定,T3时候,Session1尝试插入code=4, 由于在插入前会加插入意向锁,(对于插入意向锁的锁的范围,我目前尚无法确认在3~5的区间内加锁的时候,左右临界的开合问题)但是很明显,插入意向锁一定和(3,5]区间的next-key lock有重合,所以会出现在Session1执行T3的时候,语句被阻塞了,它在等待Session2释放(3,5]这个区间的X next_key lock 。可以参考下图——一个非常详细的锁兼容矩阵,理解阻塞原因(兼容矩阵图链接)。

  4. 同理,在T4时刻Session2执行插入语句的时候,由于(1,3]被阻塞了,但是插入的时候又要请求1~3这个区间的插入意向锁,等待Session1释放X next-key lock。于是乎死锁发生,Session2被回滚。

至此:死锁的现象可以顺利的解释通。(当然,这里还有一个疑惑不是很明白,当出现唯一冲突的时候为什么要加Next-Key Lock。有知道原因的小伙伴可以告诉我)

问题的拓展:

  1. 如果将insert on duplicate key update换成insert ignore语句,是否可以避免死锁的发生呢?答案是:否定的。其实原理都是一样的。如果我们将上述复现中的insert on duplicate key update换成insert ignore,同样会在T4时刻出现死锁。
  2. 同样,update和insert on duplicate key update组合也可以构造出死锁的出现。数据库中表结构不变,数据初始化为(1,1,1),(3,3,3),(5,5,5) 分别对应id, code,other, idpk.
Time Session1 Session2
T1

start transaction;

update test2 set other=1 where id=3;

T2

start transaction;

insert into test2(code, other) values (5, 55) on duplicate key update other = VALUES(other);

T3

update test2 set other=1 where id=5;

现象:一直阻塞

T4

insert into test2(code, other) values (3, 33) on duplicate key update other = VALUES(other);

现象:出现死锁,回滚 (2,2插入失败)

总结:

说了这么多,死锁的原因找到了,解决的办法其实比较简单。

  1. 将批量insert on duplicate key update,拆分成多个语句。保证一次事务中不要插入过多值,将多个数据,变成多个sql,执行插入。可以有效的减少死锁命中的发生。
  2. 重试:死锁不可怕,当出现死锁发生时,多执行重试操作可以有效保证插入成功,更新不丢失。

参考文章:

  1. https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-insert-intention-locks
  2. https://www.cnblogs.com/crazylqy/p/7773492.html
  3. https://www.jianshu.com/p/dca007208a58
  4. https://my.oschina.net/actiontechoss/blog/3068976
  5. https://www.aneasystone.com/archives/2017/12/solving-dead-locks-three.html
  6. https://www.cnblogs.com/zhoujinyi/p/3435982.html
  7. http://thushw.blogspot.com/2010/11/mysql-deadlocks-with-concurrent-inserts.html

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
6月前
|
数据库
SQLSERVER 2014 删除数据库定时备份任务提示失败DELETE 语句与 REFERENCE 约束“FK_subplan_job_id“冲突
SQLSERVER 2014 删除数据库定时备份任务提示失败DELETE 语句与 REFERENCE 约束“FK_subplan_job_id“冲突
执行ALTER TABLE语句时如何避免长时间阻塞并发查询
执行ALTER TABLE语句时如何避免长时间阻塞并发查询
167 0
|
关系型数据库 MySQL Java
MySQL 巨坑:update 更新慎用影响行数做判断!!!
MySQL 巨坑:update 更新慎用影响行数做判断!!!
1148 0
MySQL 巨坑:update 更新慎用影响行数做判断!!!
|
6月前
|
关系型数据库 MySQL 数据库
MySQL 并发 insert on duplicate key 导致的死锁
一 前言死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助。二 案例分析2.1 业务场景业务方的目的是使用insert on duplicate key update对重复存在的记...
764 0
MySQL 并发 insert on duplicate key 导致的死锁
|
SQL C# 数据库
C#编程学习16:清除access中某个数据表的所有数据并重置ID从1自增
C#编程学习16:清除access中某个数据表的所有数据并重置ID从1自增
C#编程学习16:清除access中某个数据表的所有数据并重置ID从1自增
|
6月前
|
SQL 关系型数据库 MySQL
MySQL insert 遇到delete 唯一键未提交导致死锁
一 前言死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助二案例分析2.1 业务场景用户录入商品,应用程序会提前检查是否存在相同记录,如果有则先删除再插入;如果没有则直接插入。2....
283 0
|
6月前
|
监控 关系型数据库 MySQL
MySQL 并发insert 唯一键冲突导致的死锁
一 前言死锁其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发朋友都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助。二 背景知识2.1 insert 锁机制在分析死锁案例之前,我们先学习一下背景知识 insert 语句的加锁策略。我们先来看...
913 0
|
6月前
|
SQL 关系型数据库 MySQL
MySQL 并发更新唯一键和插入数据导致死锁
一 前言死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助。二 案例分析2.1 业务场景业务开发同学想同步数据,他们的逻辑是通过update 更新操作,如果更新记录返回的affec...
402 0
|
关系型数据库 MySQL 索引
mysql insert判断记录存不存在 存在即更新不存在即插入 DUPLICATE key update
mysql insert判断记录存不存在 存在即更新不存在即插入 DUPLICATE key update
236 0