INSERT...ON DUPLICATE KEY UPDATE历险记

 一个普通的周末，被一连串系统告警短信轰炸了，查看日志，最终定位在了一段陈年老代码上，线上都平稳运行了很久了，怎么突然就崩了？先说一下这个需求，主要是批量将老师的课堂工具属性设置保存到数据库。由于需要考虑到属性的新增和修改并存的情况，自然而然想到了INSERT...ON DUPLICATE KEY UPDATE这个 SQL ，一条语句就可以搞定查询是否存在和插入或者更新这几个步骤。

一、语句的作用

如果在 INSERT 语句末尾指定了 ON DUPLICATE KEY UPDATE ，并且插入行后会导致在一个 UNIQUE 索引或 PRIMARY KEY 中出现重复值，则在出现重复值的行执行 UPDATE ，如果不会导致唯一值列重复的问题，则插入新行。

二、举个🌰

0. 先看下当前环境

   mysql> select version();
   +------------+
   | @@version  |
   +------------+
   | 5.6.16-log |
   +------------+
   1 row in set (0.00 sec)
   
   mysql> select @@tx_isolation;
   +-----------------+
   | @@tx_isolation  |
   +-----------------+
   |  READ-COMMITTED |
   +-----------------+
   1 row in set, 1 warning (0.00 sec)
   

1. 建表 tb_test

   create table tb_test(
    id  int         auto_increment primary key,
    k   varchar(50) not null,
    v   varchar(50) not null,
    constraint tb_test_k_uindex unique (k)
   ) engine=innodb;
   

2. 执行 SQL

   mysql> insert into tb_test (k, v)  values ('a', '1'), ('c', '2') 
   [2020-12-20 16:40:31] 1 row affected in 56 ms
   mysql> insert into tb_test (k, v)  values ('a', '2'), ('c', '3') 
   [2020-12-20 16:41:43] [23000][1062] Duplicate entry 'a' for key 'tb_test_k_uindex'
   

   可以看到唯一健冲突了。

3. 如何解决呢？

   先按k查询值“a”是否存在，如果存在，则执行 update ，否则执行 insert 。

   思考
   

• 如果你要处理的数据是一个 List 呢？其中一部分数据在存在于数据库，一部分不存在呢？

  你要筛选出不存在于数据库中的数据来执行 insert ，筛选出存在于数据库的数据来执行 update 。

• 如果问题 1 发生在并发中，会发生什么呢？

  很明显，由于需要拆分成两个 SQL 执行，所以处理起来需要加锁（如果在分布式环境中，则需要分布式锁，如 Redis 等）

4. 如何使用 INSET...ON DUPLICATE KEY UPDATE 解决上述问题

   mysql> insert into tb_test (k, v)  values ('a', '2') on duplicate key update v = values(v);
   [2020-12-20 16:43:29] 2 rows affected in 47 ms
   

   看似很完美的解决了问题，不需要筛选，不需要加锁，简直太方便了。但是真的没有问题了吗？

三、死锁问题

前面我们说了 INSET...ON DUPLICATE KEY UPDATE 会导致死锁，那如何复现呢？我们仍然使用上述 tb_test 表来模拟并发情况下批量插入事务执行过程。

先看下表中数据

mysql> select * from tb_test;
+---+---+---+
| id| k | v |
+---+---+---+
| 1 | a | 1 |
| 2 | c | 2 |
+---+---+---+
2 rows in set (0.00 sec)

假设现在有三个事务：t1、t2、t3

当第 7 步执行结束，可以立马看到死锁被 MySQL 死锁检测机制检测到：[2020-12-20 16:42:33] [40001][1213] Deadlock found when trying to get lock; try restarting transaction

四、原理分析

查看死锁日志

show engine innodb status;

分析死锁日志

# 事务 t3 正在等待 X 锁
RECORD LOCKS space id 109961 page no 4 n bits 72 index `tb_test_k_uindex` of table `behavior_data`.`tb_test` trx id 10805228283 lock_mode X waiting

# 事务 t2 也正在等待 X 锁
RECORD LOCKS space id 109961 page no 4 n bits 72 index `tb_test_k_uindex` of table `behavior_data`.`tb_test` trx id 10805227907 lock_mode X

# 同时，事务 t2 还持有了 X 锁
RECORD LOCKS space id 109961 page no 4 n bits 72 index `tb_test_k_uindex` of table `behavior_data`.`tb_test` trx id 10805227907 lock_mode X

看上去比较奇怪，怎么 t2 持有了 X 锁还需要 X 锁？其实这两个 X 锁时不同记录上的 X 锁（‘a’和‘c’记录），其实这个事务日志只打印最后一部分死锁信息，这里面隐含的条件是，事务 t3 也持有 X 锁，一个事务持有‘a’记录上的 X 锁，等待‘c’记录上的 X 锁，另一个事务反之，这样就导致了死锁。

总结

当 MySQL 执行 INSERT...ON DUPLICATE KEY UPDATE 的 INSERT 时，存储引擎会检查插入的行是否会产生 DuplicateKey 错误。如果存在，则对该行记录加上 S 锁（共享锁）并返回该行记录给 MySQL ，MySQL 执行 UPDATE 操作后交给存储引擎，存储引擎对该记录加上 X 锁（排他锁），最后进行写入。

五、其他问题

INSERT...ON DUPLICATE KEY UPDATE ，虽然解决了批量插入的繁琐问题，但是也给我们带来了一系列的其他问题，诸如 INSERT 与唯一健组合作用导致的并发情况下连接池耗尽和主键消耗过快，以及主从不一致等问题。

连接池耗尽问题

如果你使用了INSERT...ON DUPLICATE KEY UPDATE，那么在高并发情况下你很容易在日志中看到Failed to obtain JDBC Connection，数据库连接池耗尽了？可是它怎么会导致连接池耗尽呢？

假设有事务 t1、t2

• 查看锁

  select
         t.trx_state, t.trx_query, t.trx_operation_state,
         l.lock_mode, l.lock_type, l.lock_index, l.lock_data
  from information_schema.innodb_trx t
      left join information_schema.innodb_locks l on t.trx_id = l.lock_trx_id;
  

• 插入意向锁

在 MySQL 官方文档中有这样一段话：“An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. ”。

即在 insert 操作时产生。在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。但是如果在唯一健情况下，两个事务写入同样数据，则会导致锁等待。可以看到上述 SQL 是在同一个位置进行插入，lock_data 为‘a’，这样导致所有的 SQL 成了串行，这就可能产生慢 SQL ，导致连接池耗尽。

主键消耗过快问题

Duplicate entry '2147483647' for key 'PRIMARY' ，可以看到新插入的数据分配的主键和已有数据冲突了，2147483647 是个很常见的数字，int 类型最大值，说明主键自增已经达到了最大值，然后就出现了上述错误，这也是INSERT...ON DUPLICATE KEY UPDATE的问题，主键消耗过快。

# 查看当前自增值
mysql> select auto_increment from information_schema.tables where table_schema='database_name' and table_name='table_name';
| auto_increment |
| -------------- |
|             56 |

# 第一次执行
mysql> insert into tb_test (k, v) values ('a', 'e') on duplicate key update v = values(v);
[2020-12-08 13:35:48] 1 row affected in 48 ms

mysql> select auto_increment from information_schema.tables where table_schema='database_name' and table_name='table_name';
| auto_increment |
| -------------- |
|             57 |

# 第二次执行
mysql> insert into tb_test (k, v) values ('a', 'e') on duplicate key update v = values(v);
[2020-12-08 13:36:07] 1 row affected in 49 ms

mysql> select auto_increment from information_schema.tables where table_schema='database_name' and table_name='table_name';
| auto_increment |
| -------------- |
|          58    |

两次 SQL 执行后发现数据库数据并没有变化，但是自增值却增加了。

自增锁是一种特殊的表级锁，主要用于获取事务中插入的自增字段，也就是我们最常用的自增主键 id 。通过 innodb_autoinc_lock_mode 参数可以控制自增主键的生成策略，本质上就是控制 auto_increment 值的生成方式。innodb_autoinc_lock_mode 值为 1 是数据库的默认策略，对于“Simple inserts”（要插入的行数事先已知）通过在 mutex（轻量锁）的控制下获得所需数量的自动递增值来避免表级 AUTO-INC 锁，它只在分配过程的持续时间内保持，而不是直到语句完成。不使用表级 AUTO-INC 锁，除非 AUTO-INC 锁由另一个事务保持。这意味着，当 innodb_autoinc_lock_mode=1 时，无论插入是否成功，auto_increment 的值都会递增。

六、参考

• https://www.aneasystone.com/archives/2018/04/solving-dead-locks-four.html
• https://blog.csdn.net/ignorewho/article/details/86424907

七、作者介绍

特别感谢常清泉和欧阳逵在项目复盘与总结中提供的技术支持与帮助。

欢迎大佬们加入掌门教育大家庭，一起畅谈技术，分享交流。在招职位有研发工程师/架构师（ Web 前端/ Java / iOS  / 安卓 ）、音视频工程师/架构师（ iOS 、安卓、 PC 端）、DBA 、大数据工程师、算法工程师、逆向工程师（ iOS 、安卓、 PC 端）、测试工程师。

投递信箱：zeying.shi@zhangmen.com 施老师。