on duplicate key update引发的索引数据不一致问题

原创
2023/12/07 00:00
阅读数 18

作者: Ming 原文来源:https://tidb.net/blog/1c725b1c

前言

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

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

image-20231206150555088.png

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

背景

在实际项目中,我使用了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如下:

#表结构
 CREATE TABLE `t` (
  `a` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */,
  KEY `idx` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
​
#插入语句类型insert into .... on duplicate key update时候报错
insert into t values('2023-06-12 10:00:00',1) on duplicate key update b = values(b);
insert into t values('2023-06-12 10:00:00',2) on duplicate key update b = values(b);
insert into t values('2023-06-12 10:00:00',3) on duplicate key update b = values(b);

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

image-20231206151801635.png

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

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

select /*+ use_index(t,primary)*/ a from t use index(primary);
select /*+ use_index(t,idx)*/ a from t use index(idx);

image-20231206152518628.png

这个时候我们可以看到,索引里面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如下:

CREATE TABLE `t` (
  `a` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */,
  KEY `idx` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
​
begin;
insert into t values('2023-06-11 10:00:00',1) on duplicate key update b = values(b);
insert into t values('2023-06-11 10:00:00',2) on duplicate key update b = values(b);
insert into t values('2023-06-11 10:00:00',3) on duplicate key update b = values(b);
commit;
​
begin;
insert into t values('2023-06-12 10:00:00',1) on duplicate key update b = values(b);
insert into t values('2023-06-12 10:00:00',2) on duplicate key update b = values(b);
insert into t values('2023-06-12 10:00:00',3) on duplicate key update b = values(b);
commit;
​
admin check table t;
select /*+ use_index(t,primary)*/ a from t use index(primary);
select /*+ use_index(t,idx)*/ a from t use index(idx);

image-20231206160659359.png

image-20231206160737305.png

可以看到,在事务提交时候是没有进行报错提示的,只有在我们进行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。

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部