INSERT…ON DUPLICATE KEY UPDATE历险记


一个普通的周末,被一连串系统告警短信轰炸了,查看日志,最终定位在了一段陈年老代码上,线上都平稳运行了很久了,怎么突然就崩了?先说一下这个需求,主要是 批量
将老师的课堂工具属性设置保存到数据库。由于需要考虑到属性的新增和修改并存的情况,自然而然想到了

INSERT...ON DUPLICATE KEY UPDATE
这个 SQL ,一条语句就可以搞定查询是否存在和插入或者更新这几个步骤。

一、语句的作用

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

二、举个:chestnut:

  1. 先看下当前环境

    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)
  2. 建表 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;
  3. 执行 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'

    可以看到唯一健冲突了。

  4. 如何解决呢?

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

    思考

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

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

  • 如果问题 1 发生在并发中,会发生什么呢?

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

  • 如何使用 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

    操作步骤 事务 t1 事务 t2 事务 t3
    1 begin;
    2 insert into tb_test(k, v) values (‘a’, 2), (‘c’, 3) on duplicate key update v = values(v);
    3 begin;
    4 insert into tb_test(k, v) values (‘a’, 4), (‘c’, 3) on duplicate key update v = values(v);
    5 begin;
    6 insert into tb_test(k, v) values (‘c’, 4), (‘a’, 5) on duplicate key update v = values(v);
    7 rollback

    当第 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

    操作步骤 事务 t1 事务 t2
    1 begin;
    2 insert into tb_test (k, v)  values (‘a’, ‘c’)
    3 begin;
    4 insert into tb_test (k, v)  values (‘a’, ‘d’)
    • 查看锁

      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 的值都会递增。

    注:生产一般不去修改 innodb_autoinc_lock_mode 这个值,会影响 MySQL 并发能力。

    六、参考

    • https://www.aneasystone.com/archives/2018/04/solving-dead-locks-four.html

    • https://blog.csdn.net/ignorewho/article/details/86424907

    七、作者介绍

    龚训瑶,掌门一对一教学平台部 PC 后端研发工程师,主要负责一对一后端课外部分研发,6 年互联网后端从业经验。

    特别感谢 常清泉

    欧阳逵

    项目复盘与总结中提供的技术支持与帮助。

    加入掌门

    欢迎大佬们加入掌门教育大家庭,一起畅谈技术,分享交流。在招职位有研发工程师/架构师(
    Web
    前端/
    Java
    /
    iOS

     / 安卓
     )、音视频工程师/架构师( 

    iOS
     、安卓、 
    PC

     
    )、

    DBA
     、大数据工程师、算法工程师、逆向工程师( 
    iOS
     、安卓、 
    PC
    端)、测试工程师。

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


    往期好文


    前端项目灰度支撑在掌门的落地实践


    AI课全自动化实现落地

    掌门技术
    我还没有学会写个人说明!
    上一篇

    BPF和Go:在Linux中内省的现代方式[译]

    下一篇

    由于不知道Java线程池的bug,某程序员叕被祭天

    你也可能喜欢

    评论已经被关闭。

    插入图片