Oracle Merge - violation of unique key restrictions

Question

I have this table in oracle 11g.

TABLE: ORDER_LOCK
Name                   Null     Type                
---------------------- -------- ----------          
ORDER_ID                     NOT NULL NUMBER(10) [PRIMARY KEY] 
ORDER_REF_ID                 NUMBER(10)          [UNIQUE KEY]
ORDER_MSG_SENT                   NUMBER(1)          


merge into ORDER_LOCK al 
using ( select ? ORDER_REF_ID, ? ORDER_MSG_SENT from dual ) t 
on (al.ORDER_REF_ID = t.ORDER_REF_ID) 
when not matched then 
    insert (ORDER_ID, ORDER_REF_ID, ORDER_MSG_SENT) 
    values (ORDER_LOCK_SEQ.nextval, t.ORDER_REF_ID, t.ORDER_MSG_SENT)

I am using the above Merge from my applications (Java). A merge call comes from multiple threads. The code works fine, but yesterday we received a restriction violation (ORA-00001) on the unique key "ORDER_REF_ID".

Is there a problem with the Merge statement? or is there any specific scenario where the above Merge statement could violate the constraint?

thanks

Pushkar

Answer 1

This will happen if you have two sessions (in your case, Java threads) that are trying to insert the same ORDER_REF_ID. Consider the following scenario:

1) 1 MERGE ( ):

merge into ORDER_LOCK al 
using ( select 1 ORDER_REF_ID, sysdate ORDER_MSG_SENT from dual ) t 
on (al.ORDER_REF_ID = t.ORDER_REF_ID) 
when not matched then 
    insert (ORDER_ID, ORDER_REF_ID, ORDER_MSG_SENT) 
    values (ORDER_LOCK_SEQ.nextval, t.ORDER_REF_ID, t.ORDER_MSG_SENT);

2) 2 MERGE:

merge into ORDER_LOCK al 
using ( select 1 ORDER_REF_ID, sysdate ORDER_MSG_SENT from dual ) t 
on (al.ORDER_REF_ID = t.ORDER_REF_ID) 
when not matched then 
    insert (ORDER_ID, ORDER_REF_ID, ORDER_MSG_SENT) 
    values (ORDER_LOCK_SEQ.nextval, t.ORDER_REF_ID, t.ORDER_MSG_SENT);

( , 2 " 1. 2 , , 1):

3) 1

= > 2 , ORA-00001: , ORDER_REF_ID 1

UPDATE

, Java ORDER_REF_IDs - ORDER_REF_ID "" , / ORDER_REF_ID.