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

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

作者:PigeonEssence 原文地址:https://blog.csdn.net/m0_56289903/article/details/121007012

%s 个评论

要回复文章请先登录注册