Can I do an atomic MERGE in Oracle?

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.

The MERGE statement in the second session can not "see" the insert that the first session did until that session commits. If you reduce the size of the transactions the probability that this will occur will be reduced.

Or, can you sort or partition your data so that all records of a given primary key will be given to the same session. A simple function like "primary key mod N" should distribute evenly to N sessions.

btw, if two records have the same primary key, the second will overwrite the first. Sounds a little odd.

Yes, and it's called.... MERGE

EDIT: The only way to get this water tight is to insert, catch the dup_val_on_index exception and handle it appropriately (update, or insert other record perhaps). This can easily be done with PL/SQL, but you can't use that.

You're also looking for workarounds. Can you catch the dup_val_on_index in Java and issue an extra UPDATE again?

In pseudo-code:

try {  // MERGE}catch (dup_val_on_index) {  // UPDATE}