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:
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.
Does anyone have any ideas how to prevent this from occurring?
