浅谈Mysql insert on duplicate key 死锁问题定位与解决

目录
  • 前言
  • 死锁定位
  • insert on duplicate key的锁
  • 问题解决

前言

最近在监测线上日志时发现我们一个Mysql业务db时常出现 dead lock，频次不高但却一直出现，定位后发现是在并发场景下的 insert on duplicate key update sql 出现的死锁。经过分析发现这种sql确实比较容易造成死锁，不太适用于我们目前的业务场景，于是更换后解决问题。

这篇文章就从分析死锁展开，到最终如何解决这样的问题 分享相应的思路。

死锁定位

我们目前生产环境使用Mysql版本为5.7，默认事务隔离级别为RR，以下为我们的大致table结构（字段已经完全脱敏，使用非业务字段）。

CREATE TABLE IF NOT EXISTS `user_info` (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        name VARCHAR(20) NOT NULL,
        phone BIGINT(20) UNSIGNED NOT NULL,
        update_time timestamp  NOT NULL,
        UNIQUE KEY phone (phone)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

造成死锁的sql如下：

insert into user_info (name, phone, update_time) values (X,Y,Z) on duplicate key update update_time=Z;

当我们看到死锁后，在对应数据库中进行分析，”show engine innodb status“，就发现这样的报错信息"lock_mode X locks gap before rec insert intention waiting"。意思就是在等待gap lock（间隙锁）。

于是我们开始分析on duplicate key这个关键字的sql所可能引入的锁，以及对应我们业务场景中可能触发死锁的问题。

insert on duplicate key的锁

首先insert on duplicate key 这条sql的语义是：如果insert中的对应键值在数据库中没有找到对应的唯一索引记录，即进行插入；如果对表中唯一索引记录冲突，便进行更新，能够很轻松的达到一种效果： 有则直接更新，无则插入。而我们业务中的sql是自增主键id，这样一来冲突的只有可能是 phone这个唯一索引了。

首先，在RR的事务隔离级别下，insert on duplicate key这个sql与普通insert只插入意向锁和记录锁不同，insert on duplicate key sql如果没有找到对应的会在唯一键上插入gap lock和插入意向锁（如果有对应记录则会获取next key lock，next key lock 比gap lock多了一个边缘的记录锁）。Mysql sql lock。

gap lock即间隙锁，假设目前表中唯一键的数据有以下几个，1，5，10。那么insert的key如果是4,在1-5之间，则获取的gap lock的区间就是（1，5）；如果插入的数据是15，则在10-正无穷之间，因此gap lock的区间就是（10，正无穷），这个gap lock。

插入意向锁也是类似于gap lock的一种，生效的范围也一致，只是对应锁上相同范围或者有交集的。横轴为已持有，纵轴为后续申请，是否互斥或兼容。

兼容性 插入意向锁 行锁 gap lock
插入意向锁 兼容 互斥 互斥
行锁 兼容 互斥 兼容
gap lock 兼容 兼容 兼容

因此可以看到，在持有gap lock时，在插入的时候如果申请插入意向锁，便会需要等待，而insert on duplicate key的sql在执行时一般就是gap lock和插入意向锁。那么造成死锁的问题就定位到了，肯定是同一时间多个insert事务到来，并且所插入的记录对应的唯一键范围基本一致，所拥有的gap lock和插入意向锁的范围有交集，便可以出现共同持有锁反而造成死锁的问题。

那我们大致还原一下对应场景，以下是目前数据库中的数据

id name phone timestamp
1 jack 15500000000 1970.1.1
2 tom 15600000000 1970.1.1
3 hurry 15700000000 1970.1.1
阶段 tx1 tx2 tx3
1 insert into user_info (name, phone, update_time) values (test1,15700000001,1970.1.1) on duplicate key update update_time=now();    
1 持有（15700000001，正无穷）的插入意向锁以及gap lock    
2   insert into user_info (name, phone, update_time) values (test2,15700000002,1970.1.1) on duplicate key update update_time=now();  
2   申请（15700000002，正无穷）的插入意向锁失败，申请gap lock成功，等待中  
3     insert into user_info (name, phone, update_time) values (test3,15700000004,1970.1.1) on duplicate key update update_time=now();
3     申请（15700000003，正无穷）的插入意向锁失败，申请gap lock成功，等待中
4 commit 提交事务，释放锁    
5   申请插入意向锁成功 申请插入意向锁成功
6   死锁 死锁

因此形成死锁，其中一个事务回滚。

问题解决

可以看到，在我们的业务场景中，并没有特别复杂的sql，但是仍然会导致死锁，主要是插入数据的有序性以及高并发性，因此我们的解决思路也相对简单。

针对我们业务的几个思路：

  • 取消使用insert on duplicate key sql，换用普通insert sql，然后捕获对应dupicate 异常，进行异常重试和插入；
  • 业务上进行接口限流，并且入参数据的insert on duplicate key 数据list大小在事务中进行控制，分批执行，可以减少死锁的情况。

insert on duplicate key 虽然很方便一条sql完成几条sql的事情，保证原子性，但是还是不适用于较高并发的场景，使用时需要多权衡。

到此这篇关于浅谈Mysql insert on duplicate key 死锁问题定位与解决的文章就介绍到这了,更多相关Mysql insert on duplicate key 死锁内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • Mysql中Insert into xxx on duplicate key update问题

    例如,如果列a被定义为unique,并且值为1,则下列语句有同样的效果,也就是说一旦出入的记录中存在a=1的情况,直接更新c = c + 1,而不执行c = 3的操作. 复制代码 代码如下: insert into table(a, b, c) values (1, 2, 3) on duplicate key update c = c + 1;1 update table set c = c + 1 where a = 1; 另外值得一提的是,这个语句知识mysql中,而标准sql语句中是没有

  • MySQL的Replace into 与Insert into on duplicate key update真正的不同之处

    MySQL的Replace into 与Insert into on duplicate key update真正的不同之处

    看下面的例子吧: 1 Replace into ...1.1 录入原始数据mysql> use test;Database changedmysql> mysql> CREATE TABLE t1 SELECT 1 AS a, 'c3' AS b, 'c2' AS c;ALTER TABLE t1 CHANGE a a INT PRIMARY KEY AUTO_INCREMENT ;Query OK, 1 row affected (0.03 sec)Records: 1  Duplic

  • mysql insert的几点操作(DELAYED,IGNORE,ON DUPLICATE KEY UPDATE )

    INSERT语法 INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] 或: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INT

  • mysql 中 replace into 与 insert into on duplicate key update 的用法和不同点实例分析

    mysql 中 replace into 与 insert into on duplicate key update 的用法和不同点实例分析

    本文实例讲述了mysql 中 replace into 与 insert into on duplicate key update 的用法和不同点.分享给大家供大家参考,具体如下: replace into和insert into on duplicate key update都是为了解决我们平时的一个问题 就是如果数据库中存在了该条记录,就更新记录中的数据,没有,则添加记录. 我们创建一个测试表test CREATE TABLE `test` ( `id` int(11) unsigned N

  • 浅谈Mysql insert on duplicate key 死锁问题定位与解决

    目录 前言 死锁定位 insert on duplicate key的锁 问题解决 前言 最近在监测线上日志时发现我们一个Mysql业务db时常出现 dead lock,频次不高但却一直出现,定位后发现是在并发场景下的 insert on duplicate key update sql 出现的死锁.经过分析发现这种sql确实比较容易造成死锁,不太适用于我们目前的业务场景,于是更换后解决问题. 这篇文章就从分析死锁展开,到最终如何解决这样的问题 分享相应的思路. 死锁定位 我们目前生产环境使用M

  • 浅谈MySQL 有哪些死锁场景

    目录 1 环境准备 2 同一张表下的死锁 3 不同表下的死锁 4 间隙锁下的死锁 5 INSERT 语句的死锁 首先一起来复习一下死锁的概念:死锁是指两个或者多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象. 下面我们通过几个实验,来验证几种死锁场景. 1 环境准备 use martin; drop table if exists dl; CREATE TABLE `dl` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` in

  • 浅谈MySQL中的自增主键用完了怎么办

    在面试中,大家应该经历过如下场景 面试官:"用过mysql吧,你们是用自增主键还是UUID?" 你:"用的是自增主键" 面试官:"为什么是自增主键?" 你:"因为采用自增主键,数据在物理结构上是顺序存储,性能最好,blabla-" 面试官:"那自增主键达到最大值了,用完了怎么办?" 你:"what,没复习啊!!"    (然后,你就可以回去等通知了!) 这个问题是一个粉丝给我提的,我觉得

  • 浅谈MYSQL主键约束和唯一约束的区别

    目录 主键约束 唯一约束 主键约束 PRIMARY KRY 主键 是唯一的 一张表只能有一个主键 AUTO_INCREMENT 一定要和主键连用 主键一定是非空的 NOT NULL 特点:不能为空,不重复 ##没有约束 Create table stu0( Id int Name varcahr(50) ) Insert into stu0(name)value("张三丰"): ##方式一:创建表,并且添加主键约束 Create table stu1( Id int primary k

  • 浅谈mysql 针对单张表的备份与还原

    A.MySQL 备份工具xtrabackup 的安装 1. percona 官方xtrabackup 的二进制版本:二进制版本解压就能用了. 2. 解压xtrabackup & 创建连接 tar -xzvf percona-xtrabackup-2.3.4-Linux-x86_64.tar.gz -C /usr/local/ ln -s /usr/local/percona-xtrabackup-2.3.4 /usr/local/xtrabackup 3. 设置PATH环境变量 export P

  • 浅谈MySQL在cmd和python下的常用操作

    环境配置1:安装mysql,环境变量添加mysql的bin目录 环境配置2:python安装MySQL-Python 请根据自身操作系统下载安装,否则会报c ++ compile 9.0,import _mysql等错误 windows10 64位操作系统可到 http://www.lfd.uci.edu/~gohlke/pythonlibs/ 下载安装MySQL-Python包,至于whl和tar.gz在windows和Linux下的安装方法可查看我的上一篇文章 一 .cmd命令下的操作: 连

  • 浅谈mysql explain中key_len的计算方法

    mysql的explain命令可以分析sql的性能,其中有一项是key_len(索引的长度)的统计.本文将分析mysql explain中key_len的计算方法. 1.创建测试表及数据 CREATE TABLE `member` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `age` tinyint(3) unsigned DEFAULT NULL, PRIMARY KEY

  • 浅谈MySQL数据库中日期中包含零值的问题

    默认情况下MySQL是可以接受在日期中插入0值,对于现实来说日期中的0值又没有什么意义.调整MySQL的sql_mode变量就能达到目的. set @@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION'; set @@session.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION'; 例子: 有一个用于记录日志的表 c

  • 浅谈MySQL大表优化方案

    背景 阿里云RDS FOR MySQL(MySQL5.7版本)数据库业务表每月新增数据量超过千万,随着数据量持续增加,我们业务出现大表慢查询,在业务高峰期主业务表的慢查询需要几十秒严重影响业务 方案概述 一.数据库设计及索引优化 MySQL数据库本身高度灵活,造成性能不足,严重依赖开发人员的表设计能力以及索引优化能力,在这里给几点优化建议 时间类型转化为时间戳格式,用int类型储存,建索引增加查询效率 建议字段定义not null,null值很难查询优化且占用额外的索引空间 使用TINYINT类

  • 浅谈MySQL中float、double、decimal三个浮点类型的区别与总结

    下表中规划了每个浮点类型的存储大小和范围: 类型 大小 范围(有符号) 范围(无符号) 用途 ==float== 4 bytes (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0,(1.175 494 351 E-38,3.402 823 466 E+38) 单精度 浮点数值 ==double== 8 bytes (-1.797 693 134 862 315 7 E

随机推荐