INSERT ... ON DUPLICATE KEY UPDATE产生death lock死锁原理

最新推荐文章于 2025-07-10 17:39:30 发布
最新推荐文章于 2025-07-10 17:39:30 发布
阅读量4.9w 收藏 71
点赞数 13
CC 4.0 BY-SA版权
分类专栏： mybatis java 文章标签： mysql death lock
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/pml18710973036/article/details/78452688
探讨 MySQL InnoDB 引擎下 INSERT...ON DUPLICATE KEY UPDATE 的潜在问题，包括死锁及主从复制数据不一致的风险，并提出相应解决方案。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

前言

编辑

    我们在实际业务场景中，经常会有一个这样的需求，插入某条记录，如果已经存在了则更新它如果更新日期或者某些列上的累加操作等，我们肯定会想到使用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?id=58637

也就是

     当mysql执行INSERT ON DUPLICATE KEY的 INSERT时，存储引擎会检查插入的行是否会产生重复键错误。如果是的话，它会将现有的
行返回给mysql，mysql会更新它并将其发送回存储引擎。当表具有多个唯一或主键时，此语句对存储引擎检查密钥的顺序非常敏感。根据这个顺序，
存储引擎可以确定不同的行数据给到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?id=52020

https://bugs.mysql.com/bug.php?id=58637

编辑

https://bugs.mysql.com/bug.php?id=21356

 

解决办法：

1、尽量不对存在多个唯一键的table使用该语句

2、在有可能有并发事务执行的insert 的内容一样情况下不使用该语句

如何实现一个每天从0开始的递增序列
诺浅的专栏
07-21 1734
首先要借助一个函数LAST_INSERT_ID，这个函数会返回调用这个函数最后一次设置的值。 select LAST_INSERT_ID(); // 输出0 select LAST_INSERT_ID(11); // 输出11 select LAST_INSERT_ID(); // 输出11 我们可以在每次获取这个值的时候用当前值加1 select LAST_INSERT_ID(LAST_INSERT_ID()+1); 好了，现在我们实现一个递增序列，但是此时还没有实现按日期从零开始，到了第二天这个值
MySQL insert on duplicate 加锁分析（1）
ActionTech的博客
12-10 1052
insert on duplicate key update 语句，新插入记录和主键索引中已有记录冲突，可重复读和读已提交两个隔离级别下，加锁流程和加锁结果相同。如果主键索引中和新插入记录冲突的那条记录已经被标记删除了，但是执行标记删除操作的事务还没有提交，加锁情况会有什么变化？欢迎评论区留言交流。
mysql使用INSERT INTO ON DUPLICATE KEY UPDATE导致dead lock分析和解决
猫小妖的专栏
03-16 4592
一.事件回放 2019年1月的几天时间里每天会收到1次或者2次死锁的短信告警，死锁日志如下图。 二、检查业务场景和程序处理流程 用户打赏礼物请求处理，涉及到用户余额表和佣金账户表，以下所有操作在一个事务里，事务的隔离级别是read-commited,前一个操作执行成功才会继续下一个步骤否则抛出异常回滚事务： 1.扣除用户余额账户金额 2.增加陪玩佣金账户金额 3.增加公会佣金账户金...
高并发导致重复key问题--org.springframework.dao.DuplicateKeyException
最新发布
不知名小人物的博客
07-10 604
高并发场景下数据库唯一键冲突(DuplicateKeyException)问题分析与解决方案 在高并发系统中，重复唯一键异常(DuplicateKeyException)是常见问题，主要由非原子性操作、数据库隔离级别不足和唯一索引设计缺陷导致。典型场景如用户注册时多线程同时通过唯一性检查后重复插入。解决方案包括： 数据库层：建立唯一索引约束，使用ON DUPLICATE KEY UPDATE语法 应用层：采用悲观锁、乐观锁或分布式锁确保原子性操作 代码改造：通过事务管理或ReentrantLock实现同步控
Mysql replace into 与 insert into on duplicate key update 死锁和性能测试
ryqiufeng0013的专栏
08-14 1万+
REPLACEINTO 测试           1   编写目的 1.  测试 replace into 引发死锁 2.  测试 replace 和INSET INTO  ***  ON DUPLICATE KEY UPDATE *** 性能差 2   数据库环境说明 1、 数据库系统： 名称：Mysql 5.5.31 引擎：innodb        字符集：UT
INSERT ... ON DUPLICATE KEY UPDATE死锁原理
抽象的螺旋
06-17 4774
INSERT ... ON DUPLICATE KEY UPDATE死锁原理
INSERT INTO ... ON DUPLICATE KEY UPDATE 报错： Deadlock found when trying to get lock
xtsheng123456的博客
10-22 407
ON DUPLICATE KEY UPDATE 报错： 死锁 Deadlock found when trying to get lock; try restarting transaction
【转】INSERT...ONDUPLICATEKEYUPDATE产生deathlock死锁原理讲解及解决办法
路漫漫其修远兮
01-01 2377
地址：https://www.2cto.com/database/201711/695662.html 前言   我们在实际业务场景中，经常会有一个这样的需求，插入某条记录，如果已经存在了则更新它如果更新日期或者某些列上的累加操作等，我们肯定会想到使用INSERT … ON DUPLICATE KEY UPDATE语句，一条语句就搞定了查询是否存在和插入或者更新这几个步骤，但是使用这条语句在msy...
MySQL中ON DUPLICATE KEY UPDATE的介绍与使用、批量更新、存在即更新不存在则插入
m0_54850303的博客
01-20 847
基本用法：ON DUPLICATE KEY UPDATE是一种MySQL的语法，它在插入新数据时，如果遇到唯一键冲突（即已存在相同的唯一键值），则会执行更新操作，而不是抛出异常或忽略该条数据。这个语法可以大大简化我们的代码，减少不必要的判断和查询操作。用法总结1：on duplicate key update 语句根据主键id或唯一键来判断当前插入是否已存在。2：记录已存在时，只会更新on duplicate key update之后指定的字段。
mysql索引（七）唯一索引
热门推荐
camellia的博客
07-19 3万+
Mysql索引大概有五种类型： 普通索引(INDEX)：最基本的索引，没有任何限制 唯一索引(UNIQUE)：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。 主键索引(PRIMARY)：它 是一种特殊的唯一索引，不允许有空值。 全文索引(FULLTEXT )：可用于 MyISAM 表，mysql5.6之后也可用于innodb表， 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时和空间。 联合（组合）索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原
insert on duplicate key update死锁
maoyeqiu的专栏
12-26 1758
无论是死锁还是锁放大，根源在于锁的范围扩大了。所以建议大家还是最好不要用这个语法。MySQL的insert on duplicate 逻辑是，先插入主键，再插唯一键，如果唯一键发生冲突会出现加next-key锁，然后进行一次回滚（要回滚写入到主键的记录）,此时刚刚插入的遗留在主键记录的锁（MySQL的隐式锁机制）退化成了gap锁（因为主键上的记录不再需要了），锁的范围就退化成主键的最后到无穷大，在批量更新场景下，也许会有另外一个行数据插入到最后，还会有一把插入意向锁，此时死锁。
Mysql 并发引起的死锁问题
cs729298的博客
04-27 1936
平台的某个数据库上面有近千个连接，每个连接对应一个爬虫，爬虫将爬来的数据放到cdb里供后期分析查询使用。前段时间经常出现cdb查询缓慢，cpu占有率高的现象。通过show processlist后发现，大量的连接卡在了执行INSERT ... ON DUPLICATE KEY UPDATE这样的语句上面。难道并发执行INSERT ... ON DUPLICATE KEY UPDATE会导致cp
【INSERT INTO ... ON DUPLICATE KEY UPDATE 的死锁问题主要由间隙锁和高并发场景下的锁竞争引起】
TroyeMartin的博客
06-10 213
【代码】【INSERT INTO ... ON DUPLICATE KEY UPDATE 的死锁问题主要由间隙锁和高并发场景下的锁竞争引起】
聊一聊 insert on duplicate key update 下的死锁场景
weixin_40903413的博客
11-05 3882
理论上，insert on duplicate key 的执行过程中加锁顺序一致，并且对唯一索引加Record Lock 不存在唯一索引的风险，但是生产中近来出现了多次该场景下的DEADLOCK，在这里借着这个场景聊聊一些知识点以及排查问题的思路
Insert into on duplicate key update 死锁问题解析
Y_eatMeat的博客
10-18 1711
Insert into on duplicate key update 死锁问题解析
INSERT...ON DUPLICATE KEY UPDATE 产生 deathlock 死锁原理讲解及解决办法
wzy0623的专栏
02-01 831
INSERT...ONDUPLICATEKEYUPDATE产生deathlock死锁原理讲解及解决办法
Mysql 死锁案例6-并发 insert on duplicate key 导致的死锁
meser88的博客
03-14 1328
mysql 5.7.12 ,事务隔离级别RRBEGIN;SHOW ENGINE INNODB STATUS 死锁日志。
Mysql on duplicate key update用法及优缺点
wys0127的博客
08-03 2211
在实际应用中，经常碰到导入数据的功能，当导入的数据不存在时则进行添加，有修改时则进行更新，在刚碰到的时候，一般思路是将其实现分为两块，分别是判断增加，判断更新，后来发现在mysql中有ON DUPLICATE KEY UPDATE一步就可以完成(Mysql独有的语法)。
INSERT ... ON DUPLICATE KEY UPDATE 和replace 性能差异 tidb
06-26
在 TiDB 中，`INSERT ... ON DUPLICATE KEY UPDATE` 和 `REPLACE` 是两种常见的用于处理数据插入和更新的语句。尽管它们的功能类似，但在实现机制、锁行为和性能方面存在显著差异。 ### 实现机制 - **INSERT ... ON DUPLICATE KEY UPDATE**：当插入的数据导致唯一键（包括主键）冲突时，会执行指定的更新操作。这种机制允许对现有记录进行部分更新，而不会删除和重新插入整行。 - **REPLACE**：当插入的数据导致唯一键冲突时，TiDB 会先删除冲突的旧记录，然后尝试重新插入新记录。这意味着如果存在多个唯一索引，可能会触发多次删除和插入操作。 ### 锁行为 - **INSERT ... ON DUPLICATE KEY UPDATE**：在检测到唯一键冲突时，会对相关行加锁以确保事务的隔离性和一致性。由于只是更新现有行，锁定的范围通常较小。 - **REPLACE**：由于需要先删除冲突的记录再插入新记录，因此涉及更多的锁操作。删除操作会导致对相关行的独占锁，插入操作同样需要获取相应的锁。这可能导致更高的锁竞争和更长的等待时间。 ### 性能差异 1. **写入吞吐量**： - `INSERT ... ON DUPLICATE KEY UPDATE` 通常具有更高的写入吞吐量，因为它避免了删除和重新插入整行的操作。更新操作仅修改受影响的列，减少了 I/O 操作和日志写入量。 - `REPLACE` 的性能相对较低，因为每次操作都可能涉及两次写入（删除和插入），增加了数据库的负担 [^1]。 2. **事务开销**： - `INSERT ... ON DUPLICATE KEY UPDATE` 在事务中只需要一次提交即可完成插入或更新操作，减少了事务的开销。 - `REPLACE` 需要两次独立的操作（删除和插入），每个操作都需要单独的日志记录和事务管理，增加了事务的复杂性和开销 [^2]。 3. **索引维护**： - `INSERT ... ON DUPLICATE KEY UPDATE` 只需更新受影响的索引条目，减少了索引重建的成本。 - `REPLACE` 需要先删除旧记录，导致所有相关的索引条目被标记为删除，然后再插入新记录时重新生成索引条目，增加了索引维护的开销 [^3]。 4. **并发控制**： - `INSERT ... ON DUPLICATE KEY UPDATE` 在高并发环境下表现更好，因为它对行的锁定范围较小，减少了锁竞争的可能性。 - `REPLACE` 由于涉及更多的锁操作，容易引发死锁或阻塞其他事务，尤其是在大量并发写入的情况下 [^4]。 ### 使用建议 - 如果只需要对现有记录的部分字段进行更新，推荐使用 `INSERT ... ON DUPLICATE KEY UPDATE`，因为它在性能和资源消耗上更为高效。 - 如果确实需要完全替换某条记录（例如，删除旧记录并插入新记录），可以考虑使用 `REPLACE`，但需要注意其带来的额外开销和潜在的锁竞争问题。 ```sql -- 示例：INSERT ... ON DUPLICATE KEY UPDATE INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com') ON DUPLICATE KEY UPDATE name = 'Alice', email = 'alice@example.com'; -- 示例：REPLACE REPLACE INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com'); ``` ###
评论 19
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值