目录
1 .出现问题:
2 .通过分析解决
3 .解决方案:
在1.问题出现:测试阶段,如果并发了大数据,则会发现sql语句对表的死锁,一会儿死锁就会消失。 在那里进行故障诊断
错误如下。
对应的sql语句如下:
@ insert (replace into $ { tablename } ) windcode、date、\n’ ‘ code、high、open、low、\n’ ‘ `close `、volume、ge #{obj.code}、#{obj.high}、# { obj.open } # { obj.update time } ((intinsertone ) param ) ) obj ) KDTO obj,@ ppate 在排除了数据问题和线程的重复调用后,我们关注了sql语句本身。 看了网上很多经验共享,我觉得问题可能出在Replace Into语句上。
首先我们分析一下为什么并发replace into导致MySQL死锁
Replace into的常见作用是在发生冲突时用新记录替换旧记录。 也就是说,该语句的执行分为判断和执行两大步骤
1 .判断:
首先,判断是否有需要操作的记录。 (根据主键或者唯一索引判断)。
2 .操作:
对于不存在的记录,语句执行insert并插入操作。
对于已存在的记录,语句可以拆分为delete insert操作
测试:
创建表
插入数据:
使用replace into语句运行已经存在的数据。
很明显,产生影响的行数为两行
修改了第一行中的数据
我们使用replace into语句运行不存在的数据。
很明显,产生影响的行数为1行
已执行插入操作:
虽然逻辑非常清楚,但是这个单个sql语句在什么情况下会发生死锁呢? 我们必须考虑打开这个锁的时机。
正常的插入逻辑是:
首先插入聚集索引记录。 在上面的示例中,id列是自增加列。
然后,由于它是唯一索引,因此在检查duplicate key时,插入辅助索引num以添加LOCK_X类型的记录锁。
发现错误:
由于检测到duplicate key,因此必须回滚在第一步中插入的聚合索引记录。 (row_undo_ins )。
在从InnoDB层返回Server层失败后,它会收到duplicate key错误,首先查找唯一密钥冲突的索引,然后锁定冲突的索引记录(和聚合索引记录)。
转换模式:
如果发生uk冲突的索引是最后一个唯一索引,没有外键引用,并且delete trigger不存在,请使用UPDATE ROW解决冲突。
否则,使用DELETE ROW INSERT ROW解决冲突。
更新记录:
对于聚合索引,由于PK列发生了更改,因此用delete insert聚合索引记录方法进行了更新。
对于2级uk索引,也采用了删除标签插入方式。
所以死锁的问题多半就会出现在X记录锁上面。
死锁分析:
因此,在多线程、并发的环境中,两个事务可能同时获取一个记录的更改。
取得事务1x记录锁定,
事务2检测到冲突,获取X|NK锁定,并被事务1阻止
事务1检测到冲突,申请获取S|NK,并被事务2阻止
事务处理1事务处理2 lock _ xlock _ not _ gap– lock _ x-lock _ next _ key阻止LOCK_S-LOCK_NEXT_KEY死锁处于锁定状态
3.解决方案:经过多方讨论,最终决定用高并发环境下的Replace Into语句替换insetr ON DUPLICATE KEY UPDATE语句来解决死锁问题。
ON DUPLICATE KEY UPDATE语句的作用如下:
如果表中已存在数据的主键值/UNIQUE KEY,请执行更新操作,即更新后的操作。
否则插入新记录。
实现了与Replace Into相同的复检更换功能,避免了高并发死锁问题。
但是,UPDATE操作的性能与DELETE操作相比有一定的性能影响,需要后续的测试随访。