Mysql 使用ON DUPLICATE KEY UPDATE更新数据还会出现Duplicate entry？

背景

在大部分业务系统开发中，我们经常会遇到以下类似的业务场景，这里以电商场景作为举例：

1. 在电商系统中，手机SN及IMEI编号是全局唯一的；
2. 当新增手机库存记录时，若不存在当前手机的SN及IMEI编号，则新增一条手机库存记录；反之，若存在，则更新对应手机的颜色等信息；

遇到以上业务场景，想必大部分程序猿，会使用以下SQL：

INSERT INTO phone (sn, imei, color)
VALUES ('sn1', 'imei2', 'GOLDEN')
ON DUPLICATE KEY UPDATE
sn = VALUES(sn), imei = VALUES(imei), color = VALUES(color);

以上SQL，把本需要执行二步的操作简化为一条SQL即可实现：若唯一键存在则更新，若不存在则新增；
ON DUPLICATE KEY UPDATE 本就是处理唯一键重复的场景的，那使用它更新数据时，是否会出现Duplicate entry异常呢？

就下来我们就来探究探究。。。

环境准备

使用docker安装mysql

创建以下docker-compose.yml文件：

version: '3.3'
services:
  mysql:
    image: mysql:8.0
    restart: always
    container_name: mysql
    user: "mysql:mysql"
    command:
      --character-set-server=utf8
      --collation-server=utf8_general_ci
    environment:
      MYSQL_DATABASE: sales
      MYSQL_USER: random
      MYSQL_PASSWORD: random
      MYSQL_ROOT_PASSWORD: 123456
    ports:
      - '3306:3306'
    expose:
      - '3306'
    volumes:
      - ./tmp:/var/lib/mysql

使用docker-compose安装mysql，如果没有docker-compose可以使用brew安装: brew install docker-compose

    docker-compose -f docker-compose.yml up -d

创建MYSQl表

在终端使用以下命令连接数据库：

mysql -P 3307 -h 127.0.0.1 -u root -p

在终端分别执行以下脚本，创建database及table：

create database sales;
use sales;
CREATE TABLE `phone` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `sn` varchar(32) DEFAULT NULL COMMENT 'sn',
  `imei` varchar(32) DEFAULT NULL COMMENT 'imei',
  `color` varchar(32) DEFAULT NULL COMMENT '颜色',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_sn` (`sn`),
  UNIQUE KEY `unique_imei` (`imei`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '手机';

其中phone表的唯一索引为sn、imei

验证

初始数据：

INSERT INTO phone (id, sn, imei, color) VALUES (1, 'sn1', 'imei1', 'GOLDEN'), (2, 'sn2', 'imei2', 'RED');

当更新sn1的数据时：

INSERT INTO phone (sn, imei, color)
VALUES ('sn1', 'imei2', 'GOLDEN')
ON DUPLICATE KEY UPDATE
sn = VALUES(sn), imei = VALUES(imei), color = VALUES(color);

执行失败，报错Duplicate entry

分析

在MySQL中，当一个表具有两个唯一索引（或更多），并且使用 ON DUPLICATE KEY UPDATE 子句进行数据更新时，若更新的值导致违反另一个唯一索引会出现 Duplicate entry错误，例如，如果表有两个唯一索引：sn 和 imei，并且在更新时，新值与表中已有的另一行的数据冲突，则会报错。

总结

当多个唯一索引同时冲突时，MySQL 会根据第一个遇到的冲突索引来决定如何处理，但具体哪个索引优先处理是不可预测的。因此，最好在设计和使用数据库时避免创建多个唯一索引（仅创建一个唯一索引，如以上例子中应修改为只创建sn和imei的联合唯一键）

另特别说明

请谨慎使用ON DUPLICATE KEY UPDATE，在高并发场景下，可能会带来死锁问题（什么是死锁及锁的分类，请自行google，由于篇幅原因这里不再详细介绍），这里只给出引发死锁原因的结论：
在执行insert...on duplicate key update时，innodb引擎会先判断是否存在重复的数据，若存在，则会在存在的记录上加上S锁（共享锁），再交给mysql处理duplicate的update语句，当update更新后，会在该记录上加上X锁（排他锁），最后再执行update。
因此，若在并发场景下有二个事务执行相同的语句，则可能会出现资源竞争，产生death lock，如

时间线	事务T1	事务T2
1	执行insert...on duplicate key update
2	在存在的记录上加S锁	执行insert...on duplicate key update
3	修改已存在的记录｜在存在的记录上加S锁
4		修改已存在的记录
5	把修改更新到存储引擎，在已存在记录上加X锁，等待T2 S锁的释放
6		把修改更新到存储引擎，在已存在记录上加X锁，等待T2 S锁的释放
7		死锁