Oracle数据库并发竞态问题：多实例并行处理ObjectA致ObjectB重复执行

问题场景 #

需要处理3个ObjectA类型对象，规则是仅当最后一个ObjectA处理完成后，才能启动ObjectB的处理，且ObjectA的处理由多个部署实例并行执行。

期望行为 #

• ObjectA-1 更新状态 → 判定IsLastObjectA为false，不触发ObjectB
• ObjectA-2 更新状态 → 判定IsLastObjectA为false，不触发ObjectB
• ObjectA-3 更新状态 → 判定IsLastObjectA为true，触发ObjectB处理

当前错误行为 #

• ObjectA-1 更新状态 → 判定IsLastObjectA为false
• ObjectA-2与ObjectA-3并行更新：两者均判定IsLastObjectA为true，导致ObjectB被重复触发（本应仅执行一次）

现有限制 #

• 无法使用Serializable隔离级别：会大幅影响性能，且无权限调整Oracle的ini trans参数至推荐值3
• 无法使用select for update锁机制：每个ObjectA仅基于唯一主键更新一次状态，仅在自身状态更新后才读取其他ObjectA的状态
• 已尝试Oracle多种事务传播类型及锁技术，均未解决问题

相关代码与SQL #

Java代码片段 #

@Data
public class ObjectA {
    private int status;
    private Long id;
}

@Service
// 监听器从队列获取消息映射为ObjectA后调用此方法
public class ObjectAService {
    public boolean processObjectA(final ObjectA objectA) {
        final boolean isLastUpdate = callUpdateAndCheckApi(objectA); // 简化为调用接口
        
        if (isLastUpdate) {
            // 获取ObjectB信息并开始处理
        }
        return isLastUpdate;
    }
    
    // 模拟调用控制器接口
    private boolean callUpdateAndCheckApi(ObjectA objectA) {
        // 实际为HTTP调用或内部服务调用
        return true;
    }
}

@RestController
public class ObjectAController {
    @Autowired
    private ObjectDao objectDao;

    @PutMapping("/updatestatus/islastobject")
    public boolean isLastObjectToUpdate(
            @RequestParam(name = "id") final Long id,
            @RequestParam(name = "status") final int statusCode) {

        try {
            // 更新ObjectA为完成状态
            boolean updateStatus = objectDao.updateObjectStatus(id, statusCode);
            if (updateStatus) {
                // 校验所有ObjectA是否已完成
                return objectDao.isAllObjectACompleted();
            } else {
                throw new RuntimeException("更新ObjectA状态失败");
            }
        } catch (RuntimeException e) {
            return false;
        }
    }
}

原始SQL语句 #

-- 更新ObjectA为完成状态
UPDATE Object_A o SET o.status = 9 WHERE o.id = :id;

-- 校验所有ObjectA是否处于完成状态（status=9）
SELECT COUNT(*) FROM Object_A o WHERE o.status != 9; -- 原逻辑等价：无返回值则所有完成

可行解决方案 #

方案1：合并更新与判断为原子PL/SQL块 #

将更新ObjectA状态和判断是否为最后一个完成的操作合并为单条PL/SQL块，利用Oracle事务的原子性和行级锁特性，避免并发下的间隙问题。

DECLARE
    v_total_objects NUMBER;
    v_completed_objects NUMBER;
BEGIN
    -- 1. 更新当前ObjectA状态（行级锁，其他并行更新需等待）
    UPDATE Object_A 
    SET status = 9 
    WHERE id = :input_id;
    
    -- 2. 在同一个事务内获取总数量和已完成数量
    SELECT COUNT(*), SUM(CASE WHEN status = 9 THEN 1 ELSE 0 END)
    INTO v_total_objects, v_completed_objects
    FROM Object_A;
    
    -- 3. 判断是否为最后一个完成的ObjectA
    :is_last := (v_completed_objects = v_total_objects);
END;

优势：无需额外锁机制，利用Oracle自身事务特性保证原子性，性能影响极小，仅对当前更新的ObjectA行加锁，其他操作不受影响。

方案2：引入计数器表实现原子计数 #

新增一张计数器表，通过原子更新计数器来判断是否达到完成条件，彻底避免全表查询的并发问题。

步骤1：创建计数器表（仅初始化一次）

CREATE TABLE object_a_progress (
    total_count NUMBER NOT NULL,
    completed_count NUMBER NOT NULL DEFAULT 0,
    CONSTRAINT pk_progress PRIMARY KEY (total_count)
);
-- 初始化总数量为3
INSERT INTO object_a_progress (total_count) VALUES (3);

步骤2：原子更新与计数逻辑

DECLARE
    v_current_completed NUMBER;
BEGIN
    -- 1. 更新当前ObjectA状态
    UPDATE Object_A 
    SET status = 9 
    WHERE id = :input_id;
    
    -- 2. 原子性增加完成计数器（仅当总数量匹配时更新）
    UPDATE object_a_progress 
    SET completed_count = completed_count + 1 
    WHERE total_count = 3
    RETURNING completed_count INTO v_current_completed;
    
    -- 3. 判断是否触发ObjectB
    :is_last := (v_current_completed = 3);
END;

优势：避免全表扫描，计数器更新是行级操作，性能更高，并发下不会出现重复判断的问题。

方案3：分布式锁（跨实例场景） #

如果多个部署实例属于不同JVM，可以引入Redis等分布式锁，在处理每个ObjectA前获取锁，确保更新+判断逻辑串行执行。示例伪代码：

public boolean processObjectA(final ObjectA objectA) {
    String lockKey = "object_a_process_lock";
    try (RedisLock lock = redisLockClient.lock(lockKey, 5, TimeUnit.SECONDS)) {
        if (lock.isAcquired()) {
            boolean updateStatus = objectDao.updateObjectStatus(objectA.getId(), 9);
            if (updateStatus) {
                return objectDao.isAllObjectACompleted();
            }
        }
        return false;
    }
}

注意：锁超时时间需合理设置，避免死锁；同时要处理锁获取失败的重试逻辑。

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