高负载服务中无查询实现插入前数据存在性校验的方案咨询

表结构定义 #

表A的结构如下（id关联表B的id，id+serial_num为唯一键）：

create table A (
id varchar references B(id),
serial_num varchar,
is_actual boolean
)

业务处理逻辑 #

服务从Kafka接收批量消息（单次约500条），解析为实体后需执行以下操作：

• 校验表A中是否存在相同id+serial_num的实体
  • 若不存在：将该id对应的所有实体的is_actual设为false，再插入新实体
  • 若存在：不做任何操作
• 当前使用Spring Jdbc Template的batchUpdate()方法执行更新与插入

限制条件 #

DBA禁止使用任何SELECT查询校验数据存在性，原因是并发场景下无法保证查询结果的实时性，可能导致数据一致性问题，推荐使用insert into ... on conflict do update语句，但不清楚该语句如何适配当前业务逻辑。

示例场景 #

数据库已有数据（id, serial_num, is_actual） #

[1, 1, false]
[1, 2, true]
[2, 3, false]
[2, 4, true]

接收的Kafka消息（id, serial_num）及预期/实际问题 #

• [1, 1]：不应被设为实际状态，但仍可能从Kafka接收
• [1, 3]：应成为id=1对应的最新实际serial_num，但插入时无法自动将同id其他实际条目设为false，导致多个实际值
• [1, 2]：已处于实际状态，但如果在[1,3]之后接收，会覆盖[1,3]的实际状态，不符合预期

尝试方案的问题 #

曾考虑用insert into ... on conflict do update实现，但遇到以下问题：

1. 插入全新条目（如[1,3]）时不会触发冲突，插入时默认is_actual=true，但同id的其他实际条目不会被设为false，导致同一id存在多个is_actual=true的记录
2. 不确定能否在on conflict do update的更新部分执行多组操作（比如同时更新当前记录和同id的其他记录）
3. 若未接收到新的唯一键对，应保留最后一条实际条目（如[1,2]）的is_actual=true，但如果[1,1]在[1,2]之后被接收，会被错误设为实际状态

核心诉求 #

在禁止使用SELECT查询的前提下，如何实现符合预期的业务逻辑？insert into ... on conflict do update是否有未被利用的特性可以解决这个问题？

内容的提问来源于stack exchange，提问作者denstran