为何JOOQ执行批量Upsert时日志显示重复执行语句？

问题场景 #

我编写了如下Upsert方法，执行后控制台日志显示执行了9条INSERT语句，但实际仅向空表中插入了5行数据，日志最后一行标注的批量大小为5，且不存在数据冲突情况，想排查该现象的原因。

实现代码 #

public void update(long objectId, Map<Long, String> props) {
    if (props != null && !props.isEmpty()) {
        var inserts = props.entrySet().stream()
                .map(p -> dsl.insertInto(OBJECT_PROPERTIES_VALUES)
                        .set(new ObjectPropertiesValuesRecord(objectId, p.getKey(), p.getValue()))
                        .onDuplicateKeyUpdate()
                        .set(OBJECT_PROPERTIES_VALUES.PROPERTY_VALUE, p.getValue()))
                .collect(Collectors.toList());
        dsl.batch(inserts).execute();
    }
}

控制台执行日志 #

Executing batch query    : insert into "warehouse"."object_properties_values" ("object_id", "property_id", "property_value") values (3, 9, '2008') on conflict ("object_id", "property_id") do update set "property_value" = '2008'
Executing batch query    : insert into "warehouse"."object_properties_values" ("object_id", "property_id", "property_value") values (3, 10, 'Зеленый в бурую крапинку') on conflict ("object_id", "property_id") do update set "property_value" = 'Зеленый в бурую крапинку'
Executing batch query    : insert into "warehouse"."object_properties_values" ("object_id", "property_id", "property_value") values (3, 2, '170') on conflict ("object_id", "property_id") do update set "property_value" = '170'
Executing batch query    : insert into "warehouse"."object_properties_values" ("object_id", "property_id", "property_value") values (3, 4, '400') on conflict ("object_id", "property_id") do update set "property_value" = '400'
Executing batch query    : insert into "warehouse"."object_properties_values" ("object_id", "property_id", "property_value") values (3, 9, '2008') on conflict ("object_id", "property_id") do update set "property_value" = '2008'
Executing batch query    : insert into "warehouse"."object_properties_values" ("object_id", "property_id", "property_value") values (3, 10, 'Зеленый в бурую крапинку') on conflict ("object_id", "property_id") do update set "property_value" = 'Зеленый в бурую крапинку'
Executing batch query    : insert into "warehouse"."object_properties_values" ("object_id", "property_id", "property_value") values (3, 2, '170') on conflict ("object_id", "property_id") do update set "property_value" = '170'
Executing batch query    : insert into "warehouse"."object_properties_values" ("object_id", "property_id", "property_value") values (3, 4, '400') on conflict ("object_id", "property_id") do update set "property_value" = '400'
Executing batch query    : insert into "warehouse"."object_properties_values" ("object_id", "property_id", "property_value") values (3, 5, '140') on conflict ("object_id", "property_id") do update set "property_value" = '140'
Batch size               : 5

实际数据情况 #

• 表中最终仅存在5行数据，对应object_id=3，property_id分别为2、4、5、9、10，与第二次批量执行的5条数据对应；
• 表初始为空，无冲突数据。

问题原因分析 #

1. 方法被重复调用：日志中前4条INSERT语句完全重复，第5条为新增语句，说明update方法被连续调用了两次：

   • 第一次调用时，传入的props包含4个键值对（property_id=9、10、2、4），执行4条INSERT后向空表插入4行数据；
   • 第二次调用时，传入的props包含5个键值对（原有4个基础上新增property_id=5），执行5条语句——其中4条触发Upsert的更新逻辑（因值未变化，表中数据无改动），1条插入新行，最终表中保留5行数据。
     两次调用的日志被连续输出，因此显示9条INSERT语句，最后一行的Batch size:5是第二次批量执行的大小。

2. 关键验证点：最终表中5行数据正好对应第二次调用的5个键值对，重复的日志语句也直接印证了两次调用的存在。

解决方案 #

• 排查调用update方法的业务逻辑，确保方法仅被调用一次；
• 在方法中添加日志，记录每次调用时props的大小与内容，便于快速定位重复调用问题；
• 若业务允许重复调用，可无需处理（Upsert逻辑会自动处理重复数据），但需确认是否符合预期业务流程。

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