使用 Oracle 和 PL/SQL 插入或更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5236666/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert or Update using Oracle and PL/SQL
提问by Shane
I have a PL/SQL function that performs an update/insert on an Oracle database that maintains a target total and returns the difference between the existing value and the new value.
Here is the code I have so far:
我有一个 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;
This works as expected in unit tests with multiple threads never failing.
However when loaded on a live system we have seen this fail with a stack trace looking like this:
这在具有多个线程的单元测试中按预期工作，永远不会失败。
但是，当加载到实时系统上时，我们看到此操作失败，堆栈跟踪如下所示：
ORA-01403: no data found
ORA-00001: unique constraint () violated
ORA-01403: no data found
The line numbers (which I have removed since they are meaningless out of context) verify that the first update fails due to no data, the insert fail due to uniqueness, and the 2nd update is failing with no data, which should be impossible.
行号（我已将其删除，因为它们在上下文中毫无意义）验证第一次更新因没有数据而失败，插入因唯一性而失败，第二次更新因没有数据而失败，这应该是不可能的。
From what I have read on other thread a MERGE statement is also not atomic and could suffer similar problems.
从我在其他线程上阅读的内容来看，MERGE 语句也不是原子的，可能会遇到类似的问题。
Does anyone have any ideas how to prevent this from occurring?
有没有人有任何想法如何防止这种情况发生？
回答by DCookie
It's not an impossible situation you're encountering, as Oracle is telling you. You can get the described behavior if another process has inserted the key you're trying to insert but not yet committed. Updates won't see the inserted record, but the attempt to add the duplicate value to the unique index is prohibited even if the inserted row is not committed yet.
正如 Oracle 告诉您的那样，这不是您遇到的不可能的情况。如果另一个进程插入了您尝试插入但尚未提交的密钥，您可以获得所描述的行为。更新不会看到插入的记录，但即使插入的行尚未提交，也禁止尝试将重复值添加到唯一索引。
The only solutions that come to mind are to minimize the amount of time any uncommitted inserts are hanging around for this table, or to implement some sort of locking scheme, or to wait when your insert fails for the other transaction to complete.
想到的唯一解决方案是最小化任何未提交的插入为该表挂起的时间，或者实现某种锁定方案，或者在插入失败时等待另一个事务完成。
回答by Gary Myers
Don't quite agree with DCookie.
不太同意 DCookie。
IF session A inserts value "blue" (which is enforced to be unique), and then session B inserts value "blue", session B will wait on the lock from session A. If session A commits, then session B will get the constraint violation. if session A does a rollback, then session B will be allowed to continue.
如果会话 A 插入值“blue”（强制唯一），然后会话 B 插入值“blue”，会话 B 将等待会话 A 的锁。如果会话 A 提交，则会话 B 将获得约束违反。如果会话 A 回滚，则会话 B 将被允许继续。
Potentially, there is a very small scope for session A to insert a row and commit it, session B to get the constraint violation and then the row to be deleted before session B gets to update it. I'd judge that very unlikely though.
会话 A 插入一行并提交，会话 B 获取约束违规，然后在会话 B 更新它之前删除该行，可能有一个非常小的范围。不过，我认为这不太可能。
I'd first look at whether there is only one unique constraint on the target_total table. If not, you want to be very sure which constraint is causing the violation. Also check for unique indexes as well as constraints.
我首先看看 target_total 表上是否只有一个唯一约束。如果不是，您需要非常确定是哪个约束导致了违规。还要检查唯一索引和约束。
Check whether there is any datatype mismatch or an interfering trigger. A NUMBER(2,0) might not equal a 1.1 numeric value in a select match, but on insert the 1.1 would get truncated to a 1.0, potentially triggering a constraint violation. In my example, if a trigger had forced an uppercase "BLUE", then the select might fail to match on "blue", the insert might fail on a duplicate key on "BLUE", and the subsequent insert also fails to match on "blue".
检查是否存在任何数据类型不匹配或干扰触发器。在选择匹配中 NUMBER(2,0) 可能不等于 1.1 数值，但在插入时 1.1 会被截断为 1.0，可能会触发约束违规。在我的示例中，如果触发器强制使用大写“BLUE”，则选择可能无法匹配“blue”，插入可能会在“BLUE”上的重复键上失败，并且后续插入也无法匹配“蓝色”。
Then check for variable naming. In an INSERT .... VALUES (identifier), then identifiermust be a PL/SQL variable. However a SELECT * FROM table WHERE column = identifier, then identifiermight be a column name not a PL/SQL variable. If there is a column name or a function of accountId, that would take precedence over the PL/SQL variable of the same name. It is a good habit to prefix PL/SQL variables to ensure there is never such a namespace conflict.
然后检查变量命名。在 INSERT .... VALUES ( identifier) 中，identifier必须是 PL/SQL 变量。但是， SELECT * FROM table WHERE column = identifier，则标识符可能是列名而不是 PL/SQL 变量。如果有一个accountId的列名或函数，它会优先于同名的 PL/SQL 变量。为 PL/SQL 变量添加前缀以确保永远不会发生这样的命名空间冲突是一个好习惯。
My only other idea is that, since you are running multi-threaded, is there any potential for the threads to conflict. This might be more likely in a live environment when threads might hit locks from other sessions. This might force them to synchronise in an odd fashion that doesn't crop up in testing.
我唯一的另一个想法是，由于您正在运行多线程，线程是否有可能发生冲突。当线程可能会从其他会话中锁定时，这在实时环境中更有可能发生。这可能会迫使它们以一种在测试中不会出现的奇怪方式进行同步。
