营销广告网站,百度云搜索资源入口,wordpress 文章加密,网页制作下载安装包转载自 insert ... on duplicate key update产生death lock死锁原理
前言
编辑 我们在实际业务场景中#xff0c;经常会有一个这样的需求#xff0c;插入某条记录#xff0c;如果已经存在了则更新它如果更新日期或者某些列上的累加操作等#xff0c;我们肯定会想到使用I…转载自 insert ... on duplicate key update产生death lock死锁原理
前言
编辑 我们在实际业务场景中经常会有一个这样的需求插入某条记录如果已经存在了则更新它如果更新日期或者某些列上的累加操作等我们肯定会想到使用INSERT ... ON DUPLICATE KEY UPDATE语句一条语句就搞定了查询是否存在和插入或者更新这几个步骤但是使用这条语句在msyql的innodb5.0以上版本有很多的陷阱即有可能导致death lock死锁也有可能导致主从模式下的replication产生数据不一致。
正文 正如前言说的那样在实际业务中曾经有过一个需求就是插入一条业务数据如果不存在则新增存在则累加更新某一个字段的值于是乎就想到了使用insert... on duplicate key update这个语句但是有一天去测试环境查看错误日志时却发现了在多个事务并发执行同一条insert...on duplicate key update 语句时也就是insert的内容相同时发生 了死锁。 对于insert...on duplicate key update这个语句会引发dealth lock问题官方文档也没有相关描述只是进行如下描述
An INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637)
也就是如果一个表定义有多个唯一键或者主键时是不安全的这又引发了以一个问题见https://bugs.mysql.com/bug.php?id58637
也就是 当mysql执行INSERT ON DUPLICATE KEY的 INSERT时存储引擎会检查插入的行是否会产生重复键错误。如果是的话它会将现有的
行返回给mysqlmysql会更新它并将其发送回存储引擎。当表具有多个唯一或主键时此语句对存储引擎检查密钥的顺序非常敏感。根据这个顺序
存储引擎可以确定不同的行数据给到mysql因此mysql可以更新不同的行。存储引擎检查key的顺序不是确定性的。例如InnoDB按照索引添加到
表的顺序检查键。insert ... on duplicate key 在执行时innodb引擎会先判断插入的行是否产生重复key错误如果存在在对该现有的行加上S共享锁锁如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作然后对该记录加上X排他锁最后进行update写入。 如果有两个事务并发的执行同样的语句那么就会产生death lock如 具体的bug描述见https://bugs.mysql.com/bug.php?id52020
https://bugs.mysql.com/bug.php?id58637
编辑
https://bugs.mysql.com/bug.php?id21356 解决办法
1、尽量不对存在多个唯一键的table使用该语句
2、在有可能有并发事务执行的insert 的内容一样情况下不使用该语句
