当前位置: 七九推 > IT编程>数据库>MSSQL > 高并发状态下Replace Into造成的死锁问题解决

高并发状态下Replace Into造成的死锁问题解决

2023年01月20日 MSSQL 我要评论
1.问题出现:在测试阶段,大数据并发的情况下,发现sql语句造成表的死锁,过一段时间,死锁消失。于是进行排查报错如下:对应的sql语句如下: @insert("replace into ${ta

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操作会有一定的性能上的影响,需要后续测试跟进。

到此这篇关于高并发状态下replace into造成的死锁问题解决的文章就介绍到这了,更多相关replace into死锁内容请搜索七九推以前的文章或继续浏览下面的相关文章希望大家以后多多支持七九推!

(0)
打赏 微信扫一扫 微信扫一扫

相关文章:

  • Kotlin关于协程是什么的探究

    Kotlin关于协程是什么的探究

    kotlin协程是什么本文只是自己经过研究后,对 kotlin 协程的理解概括,如有偏差,还请斧正。简要概括:协程是 kotlin 提供的一套线程 api 框架... [阅读全文]
  • sqlserver 合并列数据的实现

    sqlserver 合并列数据的实现

    sql server 递归查询树型结构某节点的所有上级节点,并且把这些所有上级节点多行拼接为一行,即合并列数据with eps_root(pk_eps, pk_... [阅读全文]
  • SQLServer 清理日志的实现

    SQLServer 清理日志的实现

    本文以sql2012为例第一步,(查询日志中文件名和大小):use 数据库名goselect file_id, name,size,* from sys.dat... [阅读全文]
  • SQL 嵌套查询的具体使用

    嵌套查询又称子查询,有select子查询,where子查询,from子查询,exists子查询。插入数据#创建表及插入记录create table class ( cid int…

    2023年01月27日 数据库
  • Nginx中Location配置超详细讲解

    Nginx中Location配置超详细讲解

    一、语法location 是 nginx 中一个非常核心的配置,关于location,举个简单的配置例子:server { listen 80; s... [阅读全文]
  • SQLserver中的any和all运算符的用法

    SQLserver中的any和all运算符的用法

    一,sql server any 运算符any 是一个逻辑运算符 ,它将值与子查询返回的一组进行比较。any运算符必须要结合比较运算符使用,>,>=... [阅读全文]

版权声明:本文内容由互联网用户贡献,该文观点仅代表作者本人。本站仅提供信息存储服务,不拥有所有权,不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 2386932994@qq.com 举报,一经查实将立刻删除。

发表评论

验证码:
Copyright © 2017-2023  七九推 保留所有权利. 粤ICP备17035492号
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com