一个由INSERT INTO ON DUPLICATE KEY UPDATE引起的报错及其处理

站长

2023年08月29日 01:14 · 阅读数 11

一、东窗事发

某日早，收到了许多接口报错的告警，虽涉及多个接口，但报错信息出奇一致。相关背景（模拟）交代如下：

1.错误信息：

Error attempting to get column 'id' from result set. Cause:    
com.mysql.jdbc.exceptions.jdbc4.MySQLDataException: '2.156220742E9' in column '1' is outside valid range for the datatype INTEGER.

2.代码信息：

a.建表sql

CREATE TABLE `t_test`
(
    `id`               int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
    `biz_field`        varchar(255)     NOT NULL DEFAULT '' COMMENT '业务字段',
    `biz_field_unique` varchar(255)     NOT NULL DEFAULT '' COMMENT '业务字段（有唯一索引）',
    PRIMARY KEY (`id`),
    UNIQUE KEY `udx_t_test_biz_field_unique` (`biz_field_unique`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8mb4 COMMENT ='测试表'

主键 id 是 int(11) unsigned 类型
字段 biz_field_unique 建了唯一索引

b.MyBatis的Mapper【报错的Mapper】

<select id="getAllTest" resultType="TestPo">
    SELECT * FROM t_test
    WHERE biz_field = #{bizValue}
</select>

查询字段使用了 select *

<insert id="insertTest" parameterType="TestPo">
    -- 此处虽然有写id，但代码中 #{id} 未被赋值，所以一直都是用自增id
    INSERT INTO t_test(id, biz_field, biz_field_unique)
    VALUES (#{id}, #{bizField}, #{bizFieldUnique})
    ON DUPLICATE KEY
    UPDATE
        biz_field = #{bizField}
</insert>

插入数据时使用了 INSERT INTO ON DUPLICATE KEY UPDATE

c.映射的Po

//kotlin
class TestPo(
    /**
     * 主键id
     */
    var id: Int = 0,

    /**
     * 业务字段
     */
    var bizField: String = "",

    /**
     * 业务字段（有唯一索引）
     */
    var bizFieldUnique: String = ""
)

映射字段id使用了Int类型（对应java中的Integer）

二、简要分析

从错误信息的字面看，翻译过来大概就是：

原文：'2.156220742E9' in column '1' is outside valid range for the datatype INTEGER.
译文：第一列的值 '2.156220742E9' 超出 INTEGER 类型的取值范围。

从 Mapper 的 sql 可以看到报错的是一条查询的语句，通过查询表数据可知，'2.156220742E9'= 2156220742 数据就是表中id的值。因此不难理解，这是在MyBatis请求select语句时，拿到id字段的值超过了java中的Integer类型允许的最大值，无法被映射而报错。

MySql 中 id 值是 unsigned int，取值范围 [0,4294967295]
java 中 id 是 Integer，取值范围：[-2147483648,2147483648]
2147483648(max java Integer) < 2156220742(db行记录值) < 4294967295(max mysql int unsigned)

虽然java与MySql中都称为int类型，但由于Mysql中是 unsigned 的，所以其上限值比java中的int高，因此自动生成超过java中int值上限的id值时，并不会报错

三、临时处理

在第二步我们明确了错误的直接原因，临时处理起来也比较简单

1.业务没有使用该字段

a.在Po中移除该字段

如果该字段没有在业务中使用，可以直接在Po中去掉该字段，这样即使Mapper的sql中有select出该字段，但是由于不会尝试将该字段映射字到Po，所以也不存在类型转换问题，当然也不会引起报错。

//kotlin
class TestPo(
    /**
     * 主键id，移除字段后，即使 select 出该字段，也不会报错
     */
    //var id: Int = 0,
    
    ...
)

b.在sql中移除该字段

同样的，该字段没有在业务中使用的话，也可以在直接在Mapper的sql中去掉该字段，这样mybatis处理的时候，会使用Po默认值填充，同样也不会报错。

<select id="getAllTest" resultType="TestPo">
    --将select * 改成具体的字段，不包括有问题的字段id         
    SELECT biz_field,biz_field_unique 
    FROM t_test
    WHERE biz_field = #{bizValue}
</select>

如果在sql中使用了select * ，那就比较麻烦了，还要逐个字段名写上去

2.业务有使用该字段

a.修改po中该字段的类型

如果业务有使用该字段，那就需要修改Po中该字段的类型了，改为long类型即可。

//kotlin
class TestPo(
    /**
     * 主键id
     */
    var id: Long = 0
    
    ...

)

如果其它表有保存该字段，那也需要做相应的修改，并且如果该字段的存储类型值范围小于 int unsigned 的话，需要修改该字段的类型

四、寻找真凶

临时处理完成后暂时恢复了正常，但是真正原因仍有待进一步寻找

1.真有22亿的数据？当然不是

报错时我们看到该表的自增id高达21亿，但不代该表数据真有21亿（如果真的有，那你大概率会被运维请去喝茶），所以我们用select count 确认了一下，只有5万的数据。那么问题来了5万的数据，那自增id为何能增长到21亿呢？

sql-> select count(1) from t_test;
res-> 50000

2.有人手动插了id很大的数据？也不是

我们都知道，自增id值是不会自动回填的，也就是我手动插了一个id=1亿的数据，那下一个自增id就是1亿01。所以我们手动插入一个id值为21亿的数据，那就有可能出现仅有5万数据的情况下自增id达到21亿的场景。（当然一般直接修改线上数据是不被允许的）确认的方法：导出所有的id，然后看下区间分布即可一个由INSERT INTO ON DUPLICATE KEY UPDATE引起的报错及其处理结果：id值分布均匀、没有高度集中且跨度较大，不符合推测，那跨度中间的id是怎样消失的呢？思考：由于id是明显不连续的，所以推测肯定是有什么地方申请了id没有使用，并且mysql不会回收这部分id

问：不连续和跨度大是怎么看出来的？从图上看不是挺连续的吗？答：图的横坐标是id的序号，而纵坐标是id值，在21亿里边均匀分布了5万的数据，其id肯定不是连续滴。（这点直接看真实id值可能会更直观）

3.那么，什么情况下会导致mysql自增id不连续呢？

a.有人删除了中间的id值

该表没有物理删除的操作，排除

b.代码指定了不连续的id进行插入

没有此逻辑，排除

c.带插入语句的事务回滚

没有此逻辑，排除

d.唯一建冲突

由表结构可知，存在 biz_field_unique 字段，是有唯一索引的，所以可能会产生冲突而消耗自增id值

回看代码，插入该表数据的就只有一个sql，并且明确了代码中不会指定id的值，也就保证了所有id均由mysql生成，所以必定是有某些操作影响了id值的生成！

4.`INSERT INTO ON DUPLICATE KEY UPDATE` ？ yes

经查阅资料可知，INSERT INTO ON DUPLICATE KEY UPDATE 在唯一索引冲突时，虽然执行的是更新操作，但仍然会使该表的自增id+1。通过逻辑排查发现该sql所在接口的调用频率很高，而且绝大部分都是更新数据，这样会导致频繁发生唯一索引冲突，消耗自增id，这也符合id相对连续且间隔较大的特点。

五、后续处理

1.为什么要用 `INSERT INTO ON DUPLICATE KEY UPDATE` ？

a.`INSERT INTO ON DUPLICATE KEY UPDATE` 的作用

顾名思义，使用该句式可以在发生唯一键冲突的时候，去更新某些字段，不冲突的时候，则插入一条记录。需要注意的是，该语句包含了判断是否存在唯一键冲突及插入或更新数据两步操作的。

b.为什么不用 select ，然后再判断用 update 或 insert 呢？

既然 INSERT INTO ON DUPLICATE KEY UPDATE 在频繁冲突更新的情况下会浪费id，那我换种方式，先根据主键select一次，如果记录存在，我就执行update操作，如果记录不存在，我再执行insert操作？

I.朴素版

//伪代码
var data = "select * from t_test where biz_field_unique = uniqueValue"
if (data == null){
    "insert into t_test(biz_field, biz_field_unique) values ('value','uniqueValue')"
}else{
    "update t_test set biz_field = value where  biz_field_unique = uniqueValue"
}

有经验的小伙伴一眼就能看出来，这是会存在并发问题的，如下：一个由INSERT INTO ON DUPLICATE KEY UPDATE引起的报错及其处理

request-2 的select语句在 request-1 的insert语句之前执行，导致重复插入报错

II.事务版

这时有些小伙伴就要问了，既然sql执行有并发问题，那我加个事务是否可以呢？

//伪代码
transaction.open //打开事务
var data = "select * from t_test where biz_field_unique = uniqueValue;"
if (data == null){
    "insert into t_test(biz_field, biz_field_unique) values ('value','uniqueValue');"
}else{
    "update t_test set biz_field = value where  biz_field_unique = uniqueValue;"
}
transaction.commit //提交事务

在 mysql RR级别下是不行滴，朴素版的问题依旧会存在，因为select的时候是没有互斥锁的，所以 request-2 的select语句仍有可能在 request-1 的insert语句之前执行。

III.互斥锁版

既然如此，那我手动给个互斥锁（for update）如何呢？

//伪代码
transaction.open //打开事务
//select 语句加入 for update
var data = "select * from t_test where biz_field_unique = uniqueValue for update;" 
if (data == null){
    "insert into t_test(biz_field, biz_field_unique) values ('value','uniqueValue');"
}else{
    "update t_test set biz_field = value where  biz_field_unique = uniqueValue;"
}
transaction.commit //提交事务

一个由INSERT INTO ON DUPLICATE KEY UPDATE引起的报错及其处理从示意图可以看到，这样是可以的，但其执行过程相当于串行了，性能堪忧，并且其锁竞争发生在mysql中，给db造成了压力。

IV.分布式锁版

既然不想将压力放到db，其实也可以从服务侧解决这个问题：

//伪代码
distributeLock.lock //获取分布式锁
var data = "select * from t_test where biz_field_unique = uniqueValue;"
if (data == null){
    "insert into t_test(biz_field, biz_field_unique) values ('value','uniqueValue');"
}else{
    "update t_test set biz_field = value where  biz_field_unique = uniqueValue;"
}
distributeLock.unlock //释放分布式锁

通过分布式锁，可以将这串代码串行执行，但性能依旧堪忧

V.结合业务版

前面几个都是版本都是希望能得到通用解决方案的，那我们结合业务实际使用情况会不会好点呢？首先明确一点，业务对该表时没有delete操作的，也就是记录一旦插入，往后所有操作都会是更新操作，根据表中数据，我们可以得到估算分支的执行频率，如下

//伪代码
//select 执行次数 21亿 100%
var data = "select * from t_test where biz_field_unique = uniqueValue;"
if (data == null){
    //insert 执行次数 5w 0.0024%
    "insert into t_test(biz_field, biz_field_unique) values ('value','uniqueValue');"
}else{
    //update 执行次数 21亿-5w 99.9976%
    "update t_test set biz_field = value where  biz_field_unique = uniqueValue;"
}

如上计算可见，实际进入到 insert 的占比是非常少的，为了这部分的请求量而让整个流程串行执行是不妥的，因此，我们可以针对 insert 这里做一下优化，使用 INSERT INTO ON DUPLICATE KEY UPDATE 来代替insert，可以在id消耗和性能之间找到一个平衡。

//伪代码
//select
var data = "select * from t_test where biz_field_unique = uniqueValue;"
if (data == null){
    //insert into on duplicate key update
    "insert into t_test(biz_field, biz_field_unique) values ('value','uniqueValue') on duplicate key update biz_field = value;"
}else{
    //update
    "update t_test set biz_field = value where  biz_field_unique = uniqueValue;"
}

使用了 INSERT INTO ON DUPLICATE KEY UPDATE ，所以出现冲突的时候还是会有id值的浪费，但这个场景是少数的，其增长范围是可接受的

六、写在最后

遇到问题的时候，一般都需要了解好业务的使用场景，虽然很多问题都有通用的解决办法，但不一定是最优解，还是需要综合考虑业务场景。例如本文的问题就可以使用分布式锁，但是由于我们业务中不会物理删数据，因此我们可以舍弃一部分id，换来的是保证数据正确的情况下，仍保持较好的性能。