亲宝软件园·资讯

展开

Mysql insert on duplicate key 死锁

呼呼虎 人气:0

前言

最近在监测线上日志时发现我们一个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和插入意向锁的范围有交集,便可以出现共同持有锁反而造成死锁的问题。

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

idnamephonetimestamp
1jack155000000001970.1.1
2tom156000000001970.1.1
3hurry157000000001970.1.1
阶段tx1tx2tx3
1insert 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成功,等待中
4commit 提交事务,释放锁  
5 申请插入意向锁成功申请插入意向锁成功
6 死锁死锁

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

问题解决

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

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

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

加载全部内容

相关教程
猜你喜欢
用户评论