Can I do an atomic MERGE in Oracle?

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开发者_JS百科 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.

---

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
}

---

I am surprised that MERGE would behave the way you describe, but I haven't used it sufficiently to say whether it should or not.

In any case, you might have the transactions that wish to execute the merge set their isolation level to SERIALIZABLE. I think that may solve your issue.