前言

工作中我们会经常遇到这样的场景，插入一条数据，当不存在时新增，存在时更新某些字段或者某些字段执行累计等操作。

首先想到的就是使用INSERT ... ON DUPLICATE KEY UPDATE语句，一条语句搞定查询是否存在和插入或者更新好几个步骤，但是该用法在MYSQL InnoDB 5.0以上版本有很多bug，有可能导致死锁也有可能导致主从模型下replication产生数据不一致。

死锁

INSERT ... ON DUPLICATE KEY执行时，InnoDB引擎会先判断插入的行是否产生DUPLICATE KEY ERROR，如果存在，在对该现有的行加上S（共享锁）锁，并返回该行数据给MYSQL,然后MYSQL执行完DUPLICATE后的UPDATE操作，然后对该记录加上X（排他锁），最后进行UPDATE写入。

如果有两个事务并发的执行同样的语句，那么就会产生death lock，如：

时间

事务T1

事务T2

time1

执行INSERT ON DUPLICATE KEY

time2

key存在，获取该行的S锁，获取行数据

执行INSERT ON DUPLICATE KEY

time3

修改获取的行数据

key存在，获取该行的S锁，获取行数据

time4

修改获取的行数据

time5

修改结果写入存储引擎，该行加X锁，T2存在S锁，等待T2释放S锁

time6

修改结果写入存储引擎，该行加X锁，T1存在S锁，等待T1释放S锁

time7

死锁

数据不一致

MySQL Bugs: #58637: Mark INSERT...ON DUPLICATE KEY UPDATE unsafe when there is more than one key

当MYSQL执行INSERT ON DUPLICATE KEY的INSERT时，存储引擎会检查插入的行是否会产生重复键错误。如果是的话，它会将现有的行返回给MYSQL，MYSQL会更新它并将其发送回存储引擎。

当表具有多个唯一或主键时，此语句对存储引擎检查密钥的顺序非常敏感。根据这个顺序，存储引擎可以确定不同的行数据给到MYSQL，因此MYSQL可以更新不同的行。

存储引擎检查key的顺序不是确定性的，当表存在主键和多个唯一键时，InnoDB优先按照主键来检查，其他按照索引添加到表的顺序检查。

由于存储引擎检查KEY的顺序不确定性，当Master和Slave的索引创建顺序不一致时，且基于SQL语句的复制(statement-based replication, SBR)时，主从复制的数据不一致，因为主从节点的存储引擎依据索引的创建顺序返回不同的行数据；基于行的复制(row-based replication, RBR)可以FIX该bug。