INSERT ... ON DUPLICATE KEY UPDATE死锁原理

概述

我们在实际业务场景中，经常会有一个这样的要求，插入某条记录，如果已经存在了则更新日期、某些列上的信息等，我们肯定会想到INSERT … ON DUPLICATE KEY UPDATE语句，一条语句就搞定了查询是否存在、插入或者更新这几个步骤，但是使用这条语句在mysql的innodb5.0以上版本有很多的陷阱，极有可能导致death lock死锁也有可能导致主从模式下的replication产生数据不一致。

死锁现象

上面的sql语句确实很好用，但是在并发量较大时，多个事务并发执行同一条insert … on duplicate key update … ，容易发生死锁（比如insert的内容相同时），导致操作执行失败。

org.springframework.dao.DeadlockLoserDataAccessException:
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in class path resource [mapper/PersonGroupRefMapper.xml]
### The error may involve com.order.addOrder-Inline
### The error occurred while setting parameters
### SQL: insert into t_p_order(id,order_id,money,create_time,creator,creator_id) values         (?, ?, ?, ?, ?, ?)         ON DUPLICATE KEY UPDATE money= VALUES(money), update_time = now(),modifier = VALUES(creator), modifier_id = VALUES(creator_id)
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

产生死锁的原因

对于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。
如果一个表定义有多个唯一键（包括唯一索引、主键）时，是不安全的。
当mysql执行INSERT ON DUPLICATE KEY的INSERT时，存储引擎会检查插入的行为是否产生重复错误。如果是的话，它会将现有的行返回给mysql，mysql会更新它并将其发送回存储引擎。当表具有多个唯一索引或主键时，此语句对存储引擎检查密钥的顺序非常敏感。

替代方案

参考

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