使用 Oracle 和 PL/SQL 插入或更新

Question

我有一个 PL/SQL 函数，它在 Oracle 数据库上执行更新/插入，该数据库维护目标总计并返回现有值和新值之间的差值。
这是我到目前为止的代码：

FUNCTION calcTargetTotal(accountId varchar2, newTotal numeric ) RETURN number is  
oldTotal numeric(20,6);  
difference numeric(20,6);  

begin
    difference := 0;  
    begin  
        select value into oldTotal
        from target_total
        WHERE account_id = accountId
        for update of value;

        if (oldTotal != newTotal) then
            update target_total
            set value = newTotal
            WHERE account_id = accountId
            difference := newTotal - oldTotal;
        end if;
    exception
        when NO_DATA_FOUND then
        begin
            difference := newTotal;
            insert into target_total
                ( account_id, value )
            values
                ( accountId, newTotal );

        -- sometimes a race condition occurs and this stmt fails
        -- in those cases try to update again
        exception
            when DUP_VAL_ON_INDEX then
            begin
                difference := 0;
                select value into oldTotal
                from target_total
                WHERE account_id = accountId
                for update of value;

                if (oldTotal != newTotal) then
                    update target_total
                    set value = newTotal
                    WHERE account_id = accountId
                    difference := newTotal - oldTotal;
                end if;
            end;
        end;
    end;
    return difference
end calcTargetTotal;

这在单元测试中按预期工作，多线程永远不会失败。
然而，当加载到实时系统上时，我们看到此失败，堆栈跟踪如下所示：

ORA-01403: no data found  
ORA-00001: unique constraint () violated  
ORA-01403: no data found  

行号（我已将其删除，因为它们脱离上下文毫无意义）验证第一次更新由于没有数据而失败，插入失败由于唯一性，第二次更新失败且没有数据，这应该是不可能的。

从我在其他线程上读到的内容来看， MERGE 语句也不是原子的，可能会遇到类似的问题。

有谁知道如何防止这种情况发生？

Answer 1

正如 Oracle 告诉您的那样，您遇到的情况并非不可能。如果另一个进程插入了您尝试插入但尚未提交的密钥，您可以获得所描述的行为。更新不会看到插入的记录，但即使尚未提交插入的行，也禁止尝试将重复值添加到唯一索引。

想到的唯一解决方案是最大限度地减少任何未提交的插入在此表上停留的时间，或者实施某种锁定方案，或者在插入失败时等待其他事务完成。

Answer 2

不太同意 DCookie 的观点。

如果会话 A 插入值“blue”（强制为唯一），然后会话 B 插入值“blue”，则会话 B 将等待会话 A 的锁定。如果会话 A 提交，则会话 B 将获得约束违反。如果会话 A 进行回滚，则会话 B 将被允许继续。

会话 A 插入一行并提交它、会话 B 获取约束冲突以及在会话 B 更新该行之前删除该行的范围可能非常小。但我认为这不太可能。

我首先查看 target_total 表上是否只有一个唯一约束。如果不是，您需要非常确定哪个约束导致了违规。还要检查唯一索引和约束。

检查是否存在任何数据类型不匹配或干扰触发器。在选择匹配中，NUMBER(2,0) 可能不等于 1.1 数值，但在插入时，1.1 会被截断为 1.0，可能会触发约束违规。在我的示例中，如果触发器强制使用大写“BLUE”，则选择可能无法匹配“blue”，插入可能无法匹配“BLUE”上的重复键，并且后续插入也无法匹配“蓝色的”。

然后检查变量命名。在 INSERT .... VALUES (标识符) 中，标识符 必须是 PL/SQL 变量。但是，如果 SELECT * FROM table WHERE column = identifier，则 identifier 可能是列名而不是 PL/SQL 变量。如果存在列名或 accountId 函数，则其优先级高于同名的 PL/SQL 变量。为 PL/SQL 变量添加前缀是一个好习惯，以确保永远不会出现此类命名空间冲突。

我唯一的另一个想法是，由于您正在运行多线程，因此线程是否有可能发生冲突。在实时环境中，当线程可能遇到来自其他会话的锁时，这种情况可能更常见。这可能会迫使它们以一种奇怪的方式同步，而这种方式不会在测试中出现。