排查Java测试代码引发的ORA-00001唯一约束违反异常

云上老周

2026-7-29

排查ORA-00001唯一约束违反异常

执行JobHousekeepingServiceIntegrationTest.java第280行时触发以下异常栈：

javax.persistence.PersistenceException at JobHousekeepingServiceIntegrationTest.java:280
Caused by: org.eclipse.persistence.exceptions.DatabaseException at JobHousekeepingServiceIntegrationTest.java:280
Caused by: java.sql.SQLIntegrityConstraintViolationException at JobHousekeepingServiceIntegrationTest.java:280

最终异常根源为ORA-00001: unique constraint violated（唯一约束违反）。以下是测试代码，无法定位冲突代码位置，请求排查：

private void givenSeveralJobInstancesWithExecutionsInStatusForDate(
        final int count,
        final JobExecutionStatus status, 
        final LocalDateTime lastUpdate, 
        final List<Long> instanceIdsCreated) {

    new Transaction() {

        @Override
        protected void run() throws Exception {

            for (int n = 0; n < count; ++n) {
                JobInstanceEntityBuilder instBuilder = JobInstanceEntityBuilder.forDefinition(jobDef)
                        .withName("SOME_JOB" + n)
                        .withStatus(status)
                        .withParameters(
                            new JobInstanceParameterEntity("prop", "val" + n),
                            optionalDocAttachment(n),
                            optionalDocAttachment(n % 8)
                        )
                        .withLastUpdateTimestamp(lastUpdate);
                JobInstanceEntity inst = instBuilder.persist(getEM());
                instanceIdsCreated.add(inst.getId());

                JobExecutionEntityBuilder.forInstance(inst)
                        .onServerNodeName("NODE_X")
                        .withStatus(JobExecutionStatus.FAILED)
                        .withMessages(new JobExecutionMessageEntity(JobExecutionMessageCategory.ERROR, "error message " + n, null))
                        .withEndTime(lastUpdate)
                        .persist(getEM());
                if (status != null) {
                    // execution but no queue entry anymore
                    JobExecutionEntityBuilder.forInstance(inst)
                            .onServerNodeName("NODE_X")
                            .withStatus(status)
                            .withMessages(new JobExecutionMessageEntity(JobExecutionMessageCategory.INFO, "info message " + n, null))
                            .withEndTime(lastUpdate)
                            .persist(getEM());
                } else {
                    // queue entry but no execution
                    getEM().persist(new QueuedJobInstanceEntity(inst));
                }

                instBuilder.forceUpdateTimestamp(getEM());
            }
        }
    }.executeWithCommit();
}

排查方向

定位数据库约束：执行SQL查询相关表的唯一约束，明确冲突字段：

SELECT CONSTRAINT_NAME, COLUMN_NAME 
FROM USER_CONS_COLUMNS 
WHERE CONSTRAINT_TYPE='U' 
  AND TABLE_NAME IN ('JOB_INSTANCE', 'JOB_EXECUTION', 'QUEUED_JOB_INSTANCE', 'JOB_INSTANCE_PARAMETER')

（替换表名为实际业务表名）

检查JobInstance参数重复：optionalDocAttachment(n)和optionalDocAttachment(n%8)可能生成相同名称的参数实体，如果JOB_INSTANCE_PARAMETER表存在JOB_INSTANCE_ID + PARAMETER_NAME的唯一约束，同一实例下重复添加同名参数会触发冲突。需确认optionalDocAttachment的实现逻辑。
JobExecution约束冲突：如果JOB_EXECUTION表存在JOB_INSTANCE_ID + STATUS或其他组合唯一约束，循环中为同一实例创建两条不同状态的执行记录（FAILED + 传入的status）可能违反约束；或者检查是否有其他字段（如SERVER_NODE_NAME）参与唯一约束。
QueuedJobInstance约束：若QUEUED_JOB_INSTANCE表的唯一约束为JOB_INSTANCE_ID，但测试中是新创建实例，理论上不会冲突，但需确认实体类是否存在重复插入逻辑。
分步调试定位：在循环的每一步操作后调用getEM().flush()，强制EntityManager执行SQL，这样能精准定位到触发异常的代码行（是插入实例、执行记录、队列记录还是更新操作）。

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

排查Java测试代码引发的ORA-00001唯一约束违反异常

排查方向 #

排查方向