Insert or update using Oracle and PL / SQL

Question

I have a PL / SQL function that performs an update / insert in an Oracle database that supports the total amount of the goal and returns the difference between the existing value and the new value.
Here is the code that I still have:

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 that never fail.
However, when booting on a live system, we saw this error with a stack trace that looks like this:

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

The line numbers (which I deleted as they are meaningless out of context) confirm that the first update failed due to lack of data, the insert failed due to uniqueness, and the second update does not have data, which should be impossible.

From what I read in another thread, the MERGE statement is also not atomic and may have similar problems.

Does anyone have any ideas how to prevent this?

Answer 1

This is not an impossible situation that you are facing, as Oracle tells you. You can get the described behavior if another process inserted a key that you are trying to insert but not yet committed. Updates will not see the inserted record, but an attempt to add a duplicate value to the unique index is prohibited, even if the inserted row has not yet been completed.

The only solutions that come to mind are to minimize the time during which any uncommitted inserts hang around this table, or implement some kind of locking scheme, or wait for your insert to fail with another transaction.

Answer 2

I do not quite agree with DCookie.

Session IF Inserts the value "blue" (which is forcibly unique), and then session B inserts the value "blue", session B will wait for a lock from session A. If session A ends, then session B will receive a restriction violation. if session A rolls back, then session B will be allowed to continue.

There is potentially very little opportunity for session A to insert a row and commit it, session B to get a constraint violation, and then the row that needs to be deleted before session B gets an update. I would appreciate it very unlikely.

First, I'll see if there is only one unique constraint in the target_total table. If not, you want to be sure which restriction is causing the violation. Also check for unique indexes as well as restrictions.

Check for any discrepancy between the data type or the interfering trigger. NUMBER (2.0) may not match the numerical value 1.1 in the match selection, but when pasted, the value 1.1 will be truncated to 1.0, which may violate the constraint. In my example, if the trigger fired the capital letter “BLUE”, then the choice may not match the “blue”, the insertion may fail on the duplicated key in “BLUE”, and the subsequent insertion also does not match “blue”.

Then check to see if variable name naming exists. In INSERT .... VALUES (identifier), then the identifier must be a PL / SQL variable. However, the table is SELECT * FROM WHERE column = identifier, then the identifier may be the name of the column, not the PL / SQL variable. If there is a column name or accountId function, this will take precedence over the PL / SQL variable with the same name. It's a good habit to prefix PL / SQL variables to ensure that such a namespace conflict never occurs.

My only other idea is that since you are using multithreading, is there any potential for thread conflicts. This may be more likely in a live environment where threads can hit locks from other sessions. This can make them synchronize in an odd way, which does not occur during testing.