注意啦！mysql 唯一键冲突与解决冲突时的死锁风险

1. 引言

此前的文章中，我们介绍了 mysql 中的事务和锁机制。 一文讲透 MySQL 的 MVCC 机制 MySQL 锁机制（上） — 全局锁与表级锁 MySQL 锁机制（下） — 细说 InnoDB 行锁（记录锁、间隙锁与临键锁）

在实际的使用场景中，常常会发生唯一键的冲突。 如何解决唯一键冲突，这些解决方案中又隐着哪些潜在的陷阱呢？本文我们就来详细解读。

2. 唯一键冲突与解决方案

在业务中，我们为了保证符合某些条件的行的唯一性，在 mysql 表创建时通过 UNIQUE KEY 来限制唯一键是一个很好的习惯。

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `value` int(10) unsigned NOT NULL,
  `idxvalue` int(10) unsigned NOT NULL,
  `ukvalue` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_value` (`idxvalue`),
  UNIQUE KEY `uk_value` (`ukvalue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

当尝试插入的行对应的主键或唯一键已存在的时，mysql server 层会直接抛出 Error：

ERROR 1062 (23000): Duplicate entry ’1’ for key ’PRIMARY’

但在实际场景中，发生唯一键冲突直接报错通常是我们不希望看到的。 解决这个报错问题方法通常有以下三种：

1. replace into
2. insert on duplicate key update
3. insert ignore into

3. replace into

3.1. 使用方法

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)

3.2. 实现原理

replace into 是 mysql server 层来实现的，他的执行过程如下：

1. 执行器调用引擎执行 insert 语句
2. 引擎层返回 duplicate entry error
3. 执行器确认转换模式
4. 执行转换后的操作

这里提到的转换模式有两种：

1. 如果发生 duplicate key 冲突的索引是最后一个唯一索引，且没有外键引用，且不存在 delete trigger，使用 UPDATE ROW 的方式来解决冲突
2. 否则，使用DELETE ROW + INSERT ROW的方式解决冲突

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

3.3. 插入意向锁（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 作为数据表主键的，在大于当前表最大自增主键的范围上几乎是不会存在有事务加锁的情况的，因此插入意向锁在实际使用中也就不会出现锁冲突，从而被我们忽略。

3.3.1. 插入意向锁造成的锁冲突

如果我们不使用数据表的自增 ID 作为主键，而是自己手动指定主键，那么就有可能触发锁冲突。 如下图所示，我们在表 test 中插入三条数据，然后在左侧的事务中通过 select for update 语句获取临键锁 (10, 20] 然后，在右侧的另一个事务中 insert id 为 15 的记录。 此时，由于临键锁的存在，数据插入前尝试获取插入意向锁的操作被阻塞，直到获取锁超过超时时间退出。

3.3.2. 插入意向锁与死锁

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

上图中，我们仍然在数据库中有三条原始记录的前提下开启事务。 左侧的事务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

3.3.3. replace into 与死锁

那么，上述描述的插入意向锁与死锁的产生究竟与 replace into 并发环境下发生死锁有什么关系呢？ 我们上面已经提到，在大部分场景下，replace into 实际上是通过 delete + insert 来实现的。 假设存在两个事务 transaction1 与 transaction2，数据库中主键依次为 a、b、c。

1. transaction1 检测到与 b 记录唯一键冲突，transaction1 执行 delete b，从而获取到范围为 (a, c] 的临键锁
2. transaction2 同时检测到与 b 记录唯一键冲突，尝试执行 delete b，等待临键锁 (a, b]
3. transaction1 执行插入操作，尝试获取插入意向锁，如果此时插入的 id 位于 (a, b] 区间上，那么参考上文，就会出现死锁

在实际场景中，我们是通过 mysql 自增 ID 来作为主键的，mysql 通过 autoinc lock 保证了自增 ID 的唯一与递增，是否还会出现上述问题呢？ 事实上，mysql 只能保证自增 id 生成的递增性，但在并发环境中，是无法保证获取到 id 的多个事务最终的执行顺序的，很可能后获取到自增 id 的事务先执行成功，以至于此前获取到较小 id 的时候试图插入到存在临键锁的区间中从而出现了死锁的问题。

3.4. 主从同步问题

除了上述所说的死锁问题，replace into 语句还有可能造成主从不一致。 此前我们介绍了 binlog 的存储模式： 怎么避免从删库到跑路 — 详解 mysql binlog 的配置与使用

通常，基于性能考虑，线上不会使用 ROW 模式存储 binlog，而 MIXED 和 STATEMENT 模式下，对于 insert 语句，都只会记录执行的语句而不会记录实际插入的数据，这对于 replace into 来说是灾难性的。 当 replace into 执行时，从上文可以了解到，大部分场景下，mysql 实际执行的是 delete + insert 两步操作，但 binlog 中实际上只会保存一条 update 语句。 这造成主库中，表的 AUTO_INCREMENT 进行了加 1 操作，而从库中则不会进行这一操作，当前以及此后依赖 mysql 自增 ID 作为主键的行其主键都会出现主从不一致的情况。 当然，要解决这个问题只要保证表的主键不作为业务实际意义使用即可，既然业务上并不存在对主键 ID 的依赖，那么主键值取多少，以及是否主从一致就显得没那么重要了，当然，即便如此，也仍然不推荐使用 replace into。

4. insert on duplicate update

4.1. 使用方法

相比 replace into，insert on duplicate update 实现了类似效果，但显得更为灵活。 在其后的 update 语句中，mysql 允许使用者将任意字段更新为任何值，而不仅仅局限于 insert 语句中预先指定的值。

insert into test (`value`, `idxvalue`, `ukvalue`) values (3, 6, 5) on duplicate key update value = 3, idxvalue = 5

4.2. 死锁问题

既然 replace into 发生死锁的原因是 delete + insert 两步操作中插入意向锁与另一事务等待的临键锁循环等待造成的，那么，在发生唯一键冲突时只有一步 update 操作的 insert on duplicate update 语句是不是就不会发生死锁了呢？ 很遗憾，答案是否定的。 参见 mysql 官方 bug 文档：https://bugs.mysql.com/bug.php?id=52020。

在 insert on duplicate update 语句执行时，server 层实际上是通过两个步骤来实现的：

1. 查询待插入数据是否会产生唯一键冲突，如果会产生冲突则持有前一个索引到该位置间的临键锁，否则持有前一个索引到待插入位置的间隙锁
2. 如果不会产生唯一键冲突，那么执行 insert 语句插入，否则执行 update 语句进行更新

上述的两步流程中，在并发环境下，多个事务同时检测不会发生键冲突，于是获取间隙锁，由于间隙锁之间不会发生冲突，所以均获取成功。 但根据我们上面所介绍的，这些事务在执行 insert 语句时，同样要去获取插入意向锁，于是和 replace into 一样也会出现死锁的问题。

4.2.1. mysql 版本问题

在 mysql 5.6 中，使用 INSERT INTO … SELECT … ON DUPLICATE KEY UPDATE 语句并不会出现死锁的问题，但是却有可能造成并发环境下，数据写入错误的情况，事实上，这是更加严重的问题。 在 mysql 官方文档中，有下面一段话：

INSERT INTO … SELECT … ON DUPLICATE KEY UPDATE and LOAD DATA CONCURRENT REPLACE took too weak a lock, leading to the possibility of concurrent SELECT statements returning inconsistent results. (Bug #38046, Bug #11749055)

也就是说在 mysql 5.7 版本中，加强了 INSERT INTO … SELECT … ON DUPLICATE KEY UPDATE 语句所使用的锁，保证了数据的一致性，但也造成了死锁的出现。

5. insert ignore into

5.1. 使用方法

很多情况下，直接忽略 Error 是最简单粗暴有效的方案。 在 insert 语句中加入 ignore 关键字，可以将 Error 变为 Warning，同时不影响语句正常的执行。

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

5.2. 缺点

但这么做会让问题被隐藏，如果不去对比实际插入的条数，可能造成数据与你预期的不一致，例如按照插入时间查找发现数据行少于预期的问题等。

6. 死锁的解决

理解了上述死锁出现的原因，就可以发现 replace into 与 insert on duplicate update 语句的死锁问题在并发环境下是很难避免的。 那么如何解决死锁呢？ 事实上，开启主动死锁检测 innodb_deadlock_detect，在死锁发生时立即返回错误，在业务代码中增加重试机制，就可以有效处理问题了。 但考虑到主动死锁检测在高并发场景下对 CPU 的消耗，使用 insert ignore into 也可能是一个很好的选择，因此，实际上需要根据具体的业务场景来寻找最适合的方案。

7. 参考资料

https://dev.mysql.com/doc/refman/5.7/en/replace.html。 https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html。 https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-insert-intention-locks。 http://blog.itpub.net/7728585/viewspace-2141409/ https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-0.html。