MySQL并发插入导致死锁_mysql并发插入死锁csdn

大家好，我是「云舒编程」，今天我们来聊聊# MySQL并发插入导致死锁。

文章首发于微信公众号：云舒编程
关注公众号获取：
1、大厂项目分享
2、各种技术原理分享
3、部门内推

背景

某天下午组里有一个对外提供创建租户的接口突然产生了MySQL死锁的报警。
该服务是一个老服务，至少有一年没有人改动过该接口，并且租户这个场景只支持创建和查询，其他能力都不支持。收到报警的一刻，内心充满了疑惑：“这也能死锁?”

死锁日志

先是到MySQL上获取了死锁日志：

关于插入意向锁，MySQL官网有如下解释说明：

结合死锁日志和官网说明大概推断死锁原因是：

• 事务一持有了某个记录的S型Next_LOCK锁(也就是S锁+GAP锁)，然后等待另一个S型Next_LOCK锁。
• 事务二持有了某个记录的X锁，同时想获取某个间隙的插入意向锁。

也就是形成了下图的环：

其实到这还是无法根据死锁日志信息看出来为什么会形成环。不过由于表中的tenant_id是由调用方指定传入的，所以可以根据tenant_id去搜索日志，找到对应的trace_id，追踪当时整个链路发生了什么。

链路分析

不搜不要紧，一搜吓一跳。根据tenant_id搜索发现从网关发起了两条一模一样的请求，发起的时间也是一模一样。也就是说在MySQL层产生了并发插入。

同时发现插入数据的代码居然是使用的for循环插入，而不是批量插入。

for _, tenant := range tenants {
		if err := model.GetDB().Model(&model.Goods{}).Create(tenant).Error; err != nil {
			return nil, err
		}
	}
1
2
3
4
5

同时在MySQL官网找到一段关于并发插入可能导致死锁的说明：

按照图中的说法，当插入一条数据时会先给该数据加上排他锁，如果发生了「duplicate-key error」，那么就会加上共享锁，这样就会导致当出现多个会话同时插入数据并且发生「duplicate-key error」时就会导致死锁。
同时文中给了一个死锁案例：

刚好我们的t_tenant表中的tenant_id是唯一索引。不过官网的案例跟我获取的死锁信息和请求链路信息有所不同，

• 官网的死锁必须要3个或者3个以上的并发才会导致死锁，但是我的并发只有两个，按照图中的举例产生不了死锁的条件。
• 官网举例中只会产生排他锁和共享锁，但是死锁日志中出现了S型Next_lock锁。

不过由于没有新的突破点，打算先按照并发插入导致死锁的思路尝试进行复现，如果可以稳定复现，那应该就是并发加上唯一索引重复冲突导致的了。
也就是说当时的场景应该如下：

按照这个时序执行逻辑和官网的加锁说明：

• 事务一先对(1,“test1”)加上X锁(即排他锁，但是这里没有加GAP锁)
• 事务二插入(1,“test1”)，由于tenant_id是唯一索引，导致「duplicate-key error」，那么事务二在(1,“test1”)加上S锁(即共享锁，同样这里没有GAP锁）
• 然后事务一插入(2,“test2”)，同时给(2,“test2”)加上X锁(即排他锁，但是这里没有加GAP锁)

分析到这里，不对啊，这里没有产生环啊，不可能产生死锁啊
就在产生疑惑的时候，线上又出了一例死锁，查看死锁日志和请求日志。发现跟之前的一模一样，依旧是网关发起两条一模一样的请求，依旧是批量插入租户数据。
到这里只能死马当做活马医，先在本地用上面的请求时序模拟复现一遍，先不管理论原因了。

模拟复现

MySQL版本：8.0

隔离级别：REPEATABLE-READ

CREATE TABLE `t_tenant` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` int DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_tenant_id` (`tenant_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1
2
3
4
5
6
7

拿着上面的请求时序进行模拟，发现根本不会产生死锁。
事务二在事务一提交之前全程都是被阻塞的，当事务一提交之后，事务二就直接报唯一键冲突了，根本不会产生死锁。

柳暗花明又一村

到这里彻底失去了眉目，连复现都复现不了更别说分析原因了。就当我想先放下后面再处理时，又来了一例死锁，同时leader也过来问解决进度，没办法只能继续分析了。
拿着三个死锁日志发现完全看不出来什么，于是重新分享请求日志，这一分析就发现了华点。三个死锁的请求都来自同一个调用方，同时他们传递过来的数组中的tenant_id是降序的。
难道是这个原因？也太逆天了吧，抱着试一试的心态，重新修改了执行逻辑：

居然真死锁了，而且百分之百稳定复现。这也太逆天了，换个顺序就死锁了。

原因分析

既然可以稳定复现了，那么就可以每执行一步SQL后就去MySQL的data_locks、data_lock_waits、innodb_trx表看下分别加了哪些锁，锁住了哪些数据。

第一步：

先分别执行一条插入语句：


执行完第一步后，先根据innodb_trx表分别获取两个事务的trx_id，方便去data_locks表和data_lock_waits分析。
这里可以看出

• 事务一的trx_id是628906
• 事务二的trx_id是628907

第二步：

data_locks表：

图中的19是记录的主键id
从data_locks表分析：

• 事务一对表加了IX意向锁，同时对记录(2,“test2”)加上了X锁，但是没有加GAP锁。
• 事务二同样对表加了IX意向锁，然后想对记录(2,“test2”)加上S锁(即共享锁+GAP锁)，但是由于事务一已经加上了X锁，所以事务二只能进行锁的等待队列中

从这里就发现了跟文档中的不同，文档中描述冲突时加的是共享锁，但是实际加的是S锁(即共享锁+GAP锁)。
也就是(2,“test2”)前面间隙事务二想尝试锁上，而(1,“test1”)想插入的位置刚好就是(2,“test2”)前面，如果是这样的确会死锁。
接着往下看

第三步：

事务一的确想在记录(2,“test2”)前插入「插入意向锁」，但是由于被事务二加了「S锁(即共享锁+GAP锁)」，导致其无法加上，同时事务二又在等待事务一在(2,“test2”)上的锁，就形成了环，最终导致了死锁。

就这么破案了？

其中从上述的分析，还存在疑点：

问题一：事务二对记录加S锁，其实状态是waiting，其实并没有真的加上，那为什么会阻塞后面的事务一呢？
问题二：事务二为什么把记录前面的间隙也锁上呢？

关于问题一，找到了一些解释：也不知道这到底算bug还是feature，听起来倒是也合理。

关于问题二：
秉承着你遇到的bug已经有千千万万个人遇到过的原则，到MySQL的bug反馈社区进行了搜索，还果然让我搜到了同道中人
mysql insert is deadlocked
同时还给出了完整的代码分析：

不过MySQL团队还未给出反馈，还不知道这到底算bug还是算特意设计，有知道的朋友也可以在评论区分享下

解决方案

其实所有的问题是由于网关的bug导致并发，进而诱发的死锁。不过网关同学说他们还不知道为什么会这样，他们本意是加超时重试的，而且目前只有我们的服务反馈了这个问题，他们也还不知道原因。

那就只能在我们服务内部自行解决了：
方案一：对参数进行排序后计算MD5值，然后作为分布式锁的key进行加锁，没有加锁成功的就丢弃。
方案二：由于前面我们发现升序的tenant_id并发插入不会死锁，所以可以在插入前进行升序排序(不过这个方案直接被毙了，大家一致认为太玄学了)

参考资料

data_locks表说明
mysql不同SQL加锁模式
mysql锁类型

推荐阅读

1、原来阿里字节员工简历长这样
2、一条SQL差点引发离职
3、MySQL并发插入导致死锁

图解 | 深入揭秘数据链路层、物理层工作原理

图解 | 深入揭秘IP层工作原理
图解 | 深入揭秘TCP工作原理
图解 | 深入揭秘HTTP工作原理
图解 | 深入揭秘Linux 接收网络数据包
图解 | 深入揭秘IO多路复用原理

如果你也觉得我的分享有价值，记得点赞或者收藏哦！你的鼓励与支持，会让我更有动力写出更好的文章哦！
更多精彩内容，请关注公众号「云舒编程」