Can I do an atomic MERGE in Oracle?

Question

I have a couple instances of a J2EE app running in a single WebLogic cluster.

At some point, these apps do a MERGE to insert or update a record into the back-end Oracle database. The MERGE checks to see if a row with a specified primary key is there or not. If it's there, update. If not, insert.

Now suppose two app instances want to insert or update a row with primary key = 100. Suppose the row doesn't exist. During the "check" stage of merge, they both see that the rows not there, so both of them attempt to insert. Then I get a unique key constraint violation.

My question is this: Is there an atomic MERGE in Oracle? I'm looking for something that has a similar effect to INSERT ... FOR UPDATE in PL/SQL except that I can only execute SQL from my apps.

EDIT: I was unclear. I AM using the MERGE statement while this error still occurs. The thing is, only the "modifying" part is atomic, not the whole merge.

Answer 1

This is not a problem with MERGE as such. Rather the issue lies in your application. Consider this stored procedure:

create or replace procedure upsert_t23 
    ( p_id in t23.id%type
      , p_name in t23.name%type )
is
    cursor c is
        select null 
        from t23
        where id = p_id;
    dummy varchar2(1);
begin
    open c;
    fetch c into dummy;
    if c%notfound then
        insert into t23 
            values (p_id, p_name);
    else
        update t23
             set name = p_name
             where id = p_id;
    end if;
 end;

So, this is the PL/SQL equivalent of a MERGE on T23. What happens if two sessions call it simultaneously?

SSN1>  exec upsert_t23(100, 'FOX IN SOCKS')

SSN2>  exec upsert_t23(100, 'MR KNOX')

SSN1 gets there first, finds no matching record and inserts a record. SSN2 gets there second but before SSN1 commits, finds no record, inserts a record and hangs because SSN1 has a lock on the unique index node for 100. When SSN1 commits SSN2 will hurl a DUP_VAL_ON_INDEX violation.

The MERGE statement works in exactly the same way. Both sessions will check on (t23.id = 100), not find it and go down the INSERT branch. The first session will succeed and the second will hurl ORA-00001.

One way to handle this is to introduce pessimistic locking. At the start of the UPSERT_T23 procedure we lock the table:

...
lock table t23 in row shared mode nowait;
open c;
...

Now, SSN1 arrives, grabs the lock and proceeds as before. When SSN2 arrives it can't get the lock, so it fails immediately. Which is frustrating for the second user but at least they are not hanging, plus they know someone else is working on the same record.

There is no syntax for INSERT which is equivalent to SELECT ... FOR UPDATE, because there is nothing to select. And so there is no such syntax for MERGE either. What you need to do is include the LOCK TABLE statement in the program unit which issues the MERGE. Whether this is possible for you depends on the framework you're using.