sql - Oracle Merge - 违反唯一键的约束

Question

我在 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)

我正在使用我的应用程序(Java)中的上述合并。合并调用发生在多个线程中。代码一直运行良好，但昨天我们在唯一键“ORDER_REF_ID”上遇到了约束违规 (ORA-00001)。

Merge 语句有问题吗？或者，当上述 Merge 语句可能导致违反约束时，是否有任何特定情况？

谢谢

普什卡

Answer 1

如果您有两个 session (在您的例子中是 Java 线程)尝试插入相同的 ORDER_REF_ID，就会发生这种情况。考虑以下场景:

1) session 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) session 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);

(这将尝试插入行，因为 session 2 不会“看到”来自 session 1 的未提交更改。 session 2 将阻塞，因为它正在等待 session 1 持有的锁):

3) session 1 提交

=> session 2 现在尝试执行插入，这将引发 ORA-00001: UNIQUE CONSTRAINT VIOLATION 因为 ORDER_REF_ID 1 已经存在

更新

为了解决这个问题，我建议您修改您的应用程序并在 Java 线程和 ORDER_REF_ID 之间引入某种关联——每个 ORDER_REF_ID 应该“属于”一个线程，并且该线程应该专门为其 ORDER_REF_ID 插入/更新数据。