mysql 导入主键冲突_mysql 唯一键冲突的三种解决方法

使用方法

mysql 提供的 replace into 语句实现了有则更新无则插入的效果，使用也很简单

只要将原有的 insert 语句：

insert into test (`value`, `idxvalue`, `ukvalue`) values (3, 6, 5)

更改为 replace 语句即可：

replace into test (`value`, `idxvalue`, `ukvalue`) values (3, 6, 5)

实现原理

replace into 是 mysql server 层来实现的，他的执行过程如下：执行器调用引擎执行 insert 语句

引擎层返回 duplicate entry error

执行器确认转换模式

执行转换后的操作

这里提到的转换模式有两种：如果发生 duplicate key 冲突的索引是最后一个唯一索引，且没有外键引用，且不存在 delete trigger，使用 UPDATE ROW 的方式来解决冲突

否则，使用DELETE ROW + INSERT ROW的方式解决冲突

通常来说，产生 duplicate key 冲突的索引都并不位于最后，所以并发场景下，大量的 replace 操作是通过 delete + insert 方式来实现的

插入意向锁(Insert Intention Locks)

在并发环境下，replace into 有可能会出现死锁

要理解死锁出现的原因，需要首先介绍此前锁机制的介绍中没有介绍的另一个锁 -- 插入意向锁

可以参看官网的介绍：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-insert-intention-locks

插入意向锁是每次 insert 操作前尝试获取的锁，如果插入位置有其他锁或锁等待存在，那么插入意向锁就会陷入等待

为什么我们平常在 sql 执行过程中几乎无法意识到插入意向锁的存在呢？原因很简单，因为通常我们都是依赖 mysql 的自增 ID 作为数据表主键的，在大于当前表最大自增主键的范围上几乎是不会存在有事务加锁的情况的，因此插入意向锁在实际使用中也就不会出现锁冲突，从而被我们忽略

插入意向锁造成的锁冲突

如果我们不使用数据表的自增 ID 作为主键，而是自己手动指定主键，那么就有可能触发锁冲突

如下图所示，我们在表 test 中插入三条数据，然后在左侧的事务中通过 select for update 语句获取临键锁 (10, 20]

然后，在右侧的另一个事务中 insert id 为 15 的记录

此时，由于临键锁的存在，数据插入前尝试获取插入意向锁的操作被阻塞，直到获取锁超过超时时间退出

插入意向锁与死锁

既然插入意向锁的存在有可能造成锁等待，那么是否有可能造成死锁呢？

答案当然是有可能的

上图中，我们仍然在数据库中有三条原始记录的前提下开启事务

左侧的事务1中，我们通过 select for update 语句获取临键锁 (10, 20]

在右侧的事务2中，我们也尝试通过 select for update 语句获取临键锁 (10, 20]，由于 id = 20 的行造成事务2陷入锁等待

此时，我们在事务1中，通过 insert 语句插入 id = 15 的记录，由于待插入位置上存在锁等待，因此事务 1 需要阻塞等待事务 2，而事务 2 此时正在等待事务 1 释放临键锁，死锁就这样发生了

就这样 mysql 主动死锁检测检测到了死锁的发生，返回了：ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

replace into 与死锁

那么，上述描述的插入意向锁与死锁的产生究竟与 replace into 并发环境下发生死锁有什么关系呢？

我们上面已经提到，在大部分场景下，replace into 实际上是通过 delete + insert 来实现的

假设存在两个事务 transaction1 与 transaction2，数据库中主键依次为 a、b、ctransaction1 检测到与 b 记录唯一键冲突，transaction1 执行 delete b，从而获取到范围为 (a, c] 的临键锁

transaction2 同时检测到与 b 记录唯一键冲突，尝试执行 delete b，等待临键锁 (a, b]

transaction1 执行插入操作，尝试获取插入意向锁，如果此时插入的 id 位于 (a, b] 区间上，那么参考上文，就会出现死锁

在实际场景中，我们是通过 mysql 自增 ID 来作为主键的，mysql 通过 autoinc lock 保证了自增 ID 的唯一与递增，是否还会出现上述问题呢？

事实上，mysql 只能保证自增 id 生成的递增性，但在并发环境中，是无法保证获取到 id 的多个事务最终的执行顺序的，很可能后获取到自增 id 的事务先执行成功，以至于此前获取到较小 id 的时候试图插入到存在临键锁的区间中从而出现了死锁的问题

主从同步问题

除了上述所说的死锁问题，replace into 语句还有可能造成主从不一致

此前我们介绍了 binlog 的存储模式：

通常，基于性能考虑，线上不会使用 ROW 模式存储 binlog，而 MIXED 和 STATEMENT 模式下，对于 insert 语句，都只会记录执行的语句而不会记录实际插入的数据，这对于 replace into 来说是灾难性的

当 replace into 执行时，从上文可以了解到，大部分场景下，mysql 实际执行的是 delete + insert 两步操作，但 binlog 中实际上只会保存一条 update 语句

这造成主库中，表的 AUTO_INCREMENT 进行了加 1 操作，而从库中则不会进行这一操作，当前以及此后依赖 mysql 自增 ID 作为主键的行其主键都会出现主从不一致的情况

当然，要解决这个问题只要保证表的主键不作为业务实际意义使用即可，既然业务上并不存在对主键 ID 的依赖，那么主键值取多少，以及是否主从一致就显得没那么重要了，当然，即便如此，也仍然不推荐使用 replace into