高并发状态下Replace Into造成的死锁问题解决
作者:PigeonEssence 发布时间:2024-01-17 10:17:37
1.问题出现:
在测试阶段,大数据并发的情况下,发现sql语句造成表的死锁,过一段时间,死锁消失。于是进行排查
报错如下:
对应的sql语句如下:
@Insert("replace into ${tableName}( windcode,date, \n" +
" code, high, open, low, \n" +
" `close`, volume, turnover,gtm_modify) "
+ "values (#{obj.windcode},#{obj.date},#{obj.code},#{obj.high},#{obj.open},#{obj.low},#{obj.close},#{obj.volume},#{obj.turnover},#{obj.updateTime})" )
int insertOne(@Param("obj") KDTO obj, @Param("tableName") String tableName);
在排除了数据问题和线程重复调用以后,我们关注了一下sql语句本身。 看了网上很多经验分享,觉得问题可能出现在 Replace Into 语句上。
2.分析解决
首先我们分析一下为什么并发replace into导致MySQL死锁
Replace into 一般作用是,当存在冲突时,会把旧记录替换成新的记录。也就是说这条语句执行,分为了两个大步:判断和执行
1.判断:
首先判断我们需要操作的记录是否存在(根据主键或者唯一索引判断)
2.操作:
针对不存在的记录,语句会执行insert,插入操作。
针对已经存在的记录,语句可以拆分为delete+insert操作
测试:
建立表
插入数据:
我们使用replace into语句去执行一个已经存在的数据:
可以清楚的发现,影响的行数是两行
第一行的数据被修改了
我们使用replace into语句去执行一个不存在的数据:
可以清楚的发现,影响的行数是一行
执行了插入操作:
逻辑非常的清晰,但是这种单条sql语句在什么情况下会出现死锁呢?我们就要去考虑这个加锁的时机。
正常的插入逻辑是:
首先插入聚集索引记录,在上例中id列为自增列。
随后插入二级索引num,由于其是唯一索引,在检查duplicate key时,为其加上类型为LOCK_X的记录锁。
发现错误:
由于检测到duplicate key,因此第一步插入的聚集索引记录需要被回滚掉(row_undo_ins)。
从InnoDB层失败返回到Server层后,收到duplicate key错误,首先检索唯一键冲突的索引,并对冲突的索引记录(及聚集索引记录)加锁。
转换模式:
如果发生uk冲突的索引是最后一个唯一索引、没有外键引用、且不存在delete trigger时,使用UPDATE ROW的方式来解决冲突;
否则,使用DELETE ROW + INSERT ROW的方式解决冲突。
更新记录:
对于聚集索引,由于PK列发生变化,采用delete + insert 聚集索引记录的方式更新。
对于二级uk索引,同样采用标记删除 + 插入的方式。
所以死锁的问题多半就会出现在X记录锁上面。
死锁分析:
所以再多线程高并发的环境状态下,存在两个事务同时去获取一个记录的修改的情况:
事务1拿到X记录锁,
事务2检测到冲突,获取X|NK锁,被事务1阻塞
事务1检测到冲突,申请获取S|NK,被事务2阻塞
事务1 | 事务2 |
---|---|
LOCK_X LOCK_NOT_GAP | - |
- | LOCK_X-LOCK_NEXT_KEY 阻塞 |
LOCK_S-LOCK_NEXT_KEY | 死锁回滚 |
所以在等待执行期间sql会有死锁报错,高并发环境下的死锁也就出现了,再事务执行完成回滚操作以后,死锁回滚,也就解释了死锁消失的问题。
3.解决方案:
经过多方讨论,最终决定使用 insetr + ON DUPLICATE KEY UPDATE语句替换高并发环境下的Replace Into语句解决死锁问题。
ON DUPLICATE KEY UPDATE语句的作用是:
若该数据的主键值/ UNIQUE KEY 已经在表中存在,则执行更新操作, 即UPDATE 后面的操作。
否则插入一条新的记录。
实现了Replace Into有相同的查重替换功能,而避免了高并发的死锁问题。
但是UPDATE操作性能相比DELETE操作会有一定的性能上的影响,需要后续测试跟进。
来源:https://blog.csdn.net/m0_56289903/article/details/121007012
猜你喜欢
- 不进行计算时,生成器和list空间占用import timefrom memory_profiler import profile@prof
- Django url pathDjango 路由在 urls.py 配置path('浏览器地址栏表示URL', '处
- 前言pycharm是python的一个商业的集成开发工具,本人感觉做python开发还是很好用的,django是一个很流行的python w
- 今天设计models时,用到了choice这个属性,用来限制用户做出选择的范围。比如说性别的选择(男或女)。class User(Abstr
- 一、控制用户存取 1、创建修改用户Creating Users Create/alter user new_user identified
- 1、File > Setting > Project:xxx > Project Interpreter 选择或添加环境2
- 前言在以前,商业分析对应的英文单词是Business Analysis,大家用的分析工具是Excel,后来数据量大了,Excel应付不过来了
- 本教程为大家分享了oracle 11g r2安装教程,供大家参考,具体内容如下一、环境脚本简单配置#!/bin/bashmv /etc/yu
- 关于选课程序,最近着实有点忙,没机会复习os、pickle两部分模块,所以数据储存和字典读取成为了一个问题,大致原理知道,但是具体操作可能还
- 首先要用designer设计ui界面打开后就和c#一样拖动控件做ui界面保存后是xxx.ui文件再添加个工具Arguments:-m PyQ
- 之前有文章,使用Android平台的OpenCV接入了视频,控制的目标是手机的摄像头,这是OpenCV的好处,使用OpenCV可以使用跨平台
- python中的二叉树模块内容:BinaryTree:非平衡二叉树 AVLTree:平衡的AVL树 RBTree:平衡的
- PyQt5简介1.什么是GUI:Graphical User Interface,图形用户界面,用于人机交互。2.怎么设计GUI:Qt3.什
- 时间久了,注册用户和朋友数据库里的废记录渐渐多了起来,尤其是电子邮件地址,请问有什么好的办法可以快速安全地将它们删除吗?试试下面这个办法,它
- 如果是django2.0 必须下载xadmin2.0 不然很多地方不兼容xadmin2.0下载地址https://github.com/ss
- 条形码和二维码#引入所需要的基本包from reportlab.pdfgen import canvasfrom reportlab.gra
- 1.表达式操作符Table 1 算术操作符操作符 语法 含义+ a + b 相加 - a - b 相减 - - a
- 一、随机数种子为什么要提出随机数种子呢?咱们前面提到过了,随机数均是模拟出来的, 想要模拟的比较真实,就需要变换种子函数内的数值,一般以时间
- 众所周知,如果py文件不在当前路径,那么就不能import,因此,本文介绍如下两种有效的方法:方法1:修改环境变量,在~/.bashrc里面
- 本篇文章主要介绍Java操作MongoDB。开发环境:System:WindowsIDE:eclipse、MyEclipse 8Databa