CODEWITHSUNDEEP
sqloracle-databaseplsqlupsertora-00001
Shane
Shane

Reputation: 41

Insert or Update using Oracle and PL/SQL

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?

Upvotes: 4

Views: 5852

Answers (2)

Gary Myers
Gary Myers

Reputation: 35401

Don't quite agree with 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.

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.

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.

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".

Then check for variable naming. In an INSERT .... VALUES (identifier), then identifier must be a PL/SQL variable. However a SELECT * FROM table WHERE column = identifier, then identifier might 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.

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.

Upvotes: 1

DCookie
DCookie

Reputation: 43533

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.

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.

Upvotes: 1

Related Questions