数据库｜一文教你解决on duplicate key update引发的索引数据不一致问题

目录

一、背景

二、原因排查

三、问题分析

//现象一

//现象二

//触发条件说明

四、问题总结

---

最近在进行TiDB数据库操作时，遇到了一个问题，在使用insert into ... on duplicate key update操作时，系统返回了如下错误信息：

ERROR8141(HY000): assertion failed: key: xxxx, assertion: Exist, start_ts:xxxx, existing start ts:xxxx, existing commit ts:xx

本文将主要介绍这个问题的触发现象以及规避方法。

一、背景

在实际项目中，我使用了insert into ... on duplicate key update语句来处理数据插入或更新的逻辑。然而，当我执行这个操作时，TiDB会抛出异常报错，指向了一个8141的错误码。

//错误解读

首先，我通过官网去搜索该错误码8141的时候，发现官网有说明是因为索引数据不一致导致的，对于错误的详细解释是：

ERROR 8141(HY000): assertion failed: key: 7480000000000000455f6980000000000000020419b05820000000000419b0582000000000, assertion: Exist, start_ts:446128785517182978, existing start ts:446128785256087554, existing commit ts:446128785256087555

上述错误表明，事务提交时断言失败。根据数据索引一致的假设，TiDB 断言 key：

7480000000000000455f6980000000000000020419b05820000000000419b0582000000000不存在，提交事务时发现该 key 存在，是由 start ts 为 446128785256087554的事务写入的。TiDB 会将该 key 的 MVCC (Multi-Version Concurrency Control) 历史输出到日志。

二、原因排查

测试环境复现SQL如下：

#表结构
CREATETABLE`t`(
`a`timestampNOTNULLONUPDATECURRENT_TIMESTAMP,
`b`int(11) DEFAULTNULL,
PRIMARY KEY(`a`) /*T![clustered_index] CLUSTERED */,
KEY`idx`(`a`)
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_bin;

#插入语句类型insertinto.... onduplicatekeyupdate时候报错
insertintot values('2023-06-12 10:00:00',1) onduplicatekeyupdateb = values(b);
insertintot values('2023-06-12 10:00:00',2) onduplicatekeyupdateb = values(b);
insertintot values('2023-06-12 10:00:00',3) onduplicatekeyupdateb = values(b);

在这里可以看到，当我们执行到第三条INSERT时候发现，会发生报错：

最开始比较费解的是，为什么仅仅插入几条数据，就会出现索引不一致的情况，索引数据不一致情况我把他分为两类，一种是数量不匹配，另一种是数值不匹配。

我先是通过使用主键和使用索引两种方式来观察数据。既然是索引数据不一致，那我们只需要观察a列就好了，SQL如下：

select/*+ use_index(t,primary)*/a fromt useindex(primary);
select/*+ use_index(t,idx)*/a fromt useindex(idx);

这个时候我们可以看到，索引里面a列的数据为：2023-12-06 15:05:17 ，而主键里面a列的数据为：2023-06-12 10:00:00。

那么这两个数值为什么会造成这种差异呢？我观察了一下发现，我设置的a列是一个时间字段，并且呢，他是一个自动更新的时间字段ON UPDATE CURRENT_TIMESTAMP，也就是说，如果发生该行变更的情况下，a列的值应该更新为最新时间才对，目前从两者差异来看的话，可以看出，该值在主键里并没有更新，反而是在索引里面更新了，也就是说，其实现在索引里面的数据是真实的，主键里面的数据是不匹配的。

那可以知道大概率是因为ON UPDATE CURRENT_TIMESTAMP列引起的，我就去查了一下看是否有相关issues，找到相关issues：

https://github.com/pingcap/tidb/issues/44565

三、问题分析

找到问题后，进行多次测试，发现该问题总共会有两种现象，下面进行分开说明一下。

//现象一

触发条件存在的情况下，当我们进行update操作触发 aotu update 更新字段时，会造成索引数据进行更新，但是聚簇主键的字段没有进行更新，导致数据与索引不一致情况发生，该现象与我们在原因排查里面看到的现象一致。

该现象的情况发生在隐式事务提交下，因为我们知道隐式事务提交使用的是乐观事务，在乐观事务下，所进行的assertion检查项要跟悲观事务相比存在差异，所以在提交的时候会报错，导致提交失败，这种情况下只会导致主键和索引的数值不一样，主键和索引的数量是一样的。

//现象二

现象二则不同，该现象是在开启显示事务提交下触发，显示事务提交是使用的悲观事务进行，这样会导致批量插入最后进行commit时候不报错。

复现SQL如下：

CREATETABLE`t`(
`a`timestampNOTNULLONUPDATECURRENT_TIMESTAMP,
`b`int(11) DEFAULTNULL,
PRIMARY KEY(`a`) /*T![clustered_index] CLUSTERED */,
KEY`idx`(`a`)
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_bin;

begin;
insertintot values('2023-06-11 10:00:00',1) onduplicatekeyupdateb = values(b);
insertintot values('2023-06-11 10:00:00',2) onduplicatekeyupdateb = values(b);
insertintot values('2023-06-11 10:00:00',3) onduplicatekeyupdateb = values(b);
commit;

begin;
insertintot values('2023-06-12 10:00:00',1) onduplicatekeyupdateb = values(b);
insertintot values('2023-06-12 10:00:00',2) onduplicatekeyupdateb = values(b);
insertintot values('2023-06-12 10:00:00',3) onduplicatekeyupdateb = values(b);
commit;

admin checktablet;
select/*+ use_index(t,primary)*/a fromt useindex(primary);
select/*+ use_index(t,idx)*/a fromt useindex(idx);

可以看到，在事务提交时候是没有进行报错提示的，只有在我们进行check时候才返回报错。

如果想要不发生这种情况，需要将assertion级别调高，可以通过系统变量tidb_txn_assertion_level 进行调整。

该变量在v6.0版本引入，默认为FAST，调到最高级别STRICT则可以在commit阶段提示报错。

需要注意的是，这个变量因为是v6.0引入的，如果有低版本集群是原地升级升上来的，那么这个值默认是OFF，也就是关闭状态，这就导致在关闭状态下，这样进行插入操作就不会出现索引数据不一致报错，而是一直插入，导致无法发现问题。

//触发条件说明

根据自己测试来看，触发条件总共有三条，必须三条同时满足，才会触发该BUG，分别是：

1. 有 ON UPDATE CURRENT_TIMESTAMP 列
2. 该列同时在clustered index 中
3. 执行SQL时候，没有显示指定该列，而是去触发auto update

四、问题总结

综上所述，该问题的发生主要是因为聚簇主键列如果存在auto update约束时，不会触发自动更新导致，该问题于v6.5.3版本进行修复。如果有满足触发条件的表，提供如下几点建议：

1、如果是UPDATE语句，在UPDATE当中手动去指定含有ON UPDATE CURRENT_TIMESTAMP 条件字段的值，避免让主键进行auto update操作。

2、如果是insert into ... on duplicate key update 语句，建议更改为replaice into 方式，遇到重复值进行delete + insert 操作，避免使用update。

作者：付家明 | 后端开发工程师

版权声明:本文由神州数码云基地团队整理撰写，若转载请注明出处。

公众号搜索神州数码云基地，后台回复数据库，加入数据库技术交流群。