我可以在 Oracle 中执行原子 MERGE 吗?

2022-09-02 10:08:30

我有几个 J2EE 应用程序的实例在单个 WebLogic 集群中运行。

在某些时候,这些应用会执行 MERGE 以将记录插入或更新到后端 Oracle 数据库中。MERGE 检查是否存在具有指定主键的行。如果存在,请更新。如果没有,请插入。

现在假设两个应用实例想要插入或更新主键 = 100 的行。假设该行不存在。在合并的“检查”阶段,他们都看到行不存在,因此他们都尝试插入。然后我得到一个唯一的键约束冲突。

我的问题是:甲骨文中是否有原子合并?我正在寻找与PL / SQL具有类似效果的东西,除了我只能从我的应用程序执行SQL。INSERT ... FOR UPDATE

编辑:我不清楚。我正在使用 MERGE 语句,而此错误仍然发生。问题是,只有“修改”部分是原子的,而不是整个合并。


答案 1

这不是MERGE本身的问题。相反,问题在于您的应用程序。请考虑以下存储过程:

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;

因此,这是 PL/SQL 相当于 T23 上的 MERGE。如果两个会话同时调用它会发生什么情况?

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

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

SSN1 首先到达那里,找不到匹配的记录并插入记录。SSN2 第二次到达那里,但在 SSN1 提交之前,找不到任何记录,插入一条记录并挂起,因为 SSN1 在 100 的唯一索引节点上有一个锁定。当 SSN1 提交 SSN2 时,将引发DUP_VAL_ON_INDEX违规。

MERGE 语句的工作方式完全相同。两个会话都将检查 ,而不是找到它并进入 INSERT 分支。第一个会话将成功,第二个会话将投掷 ORA-00001。on (t23.id = 100)

处理此问题的一种方法是引入悲观锁定。在UPSERT_T23过程开始时,我们锁定表:

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

现在,SSN1到达,抓住锁并像以前一样继续。当SSN2到达时,它无法获得锁定,因此立即失败。这对第二个用户来说是令人沮丧的,但至少他们没有挂起,而且他们知道其他人正在处理相同的记录。

INSERT 没有等同于 SELECT 的语法 ...对于更新,因为没有要选择的内容。因此,MERGE也没有这样的语法。您需要做的是在发出 MERGE 的程序单元中包含 LOCK TABLE 语句。这是否可能取决于您使用的框架。


答案 2

在第二个会话中,MERGE 语句无法“看到”第一个会话在该会话提交之前所做的插入。如果减小事务的大小,则发生这种情况的可能性将降低。

或者,是否可以对数据进行排序或分区,以便将给定主键的所有记录提供给同一会话。像“主键mod N”这样的简单函数应该均匀地分布到N个会话。

顺便说一句,如果两条记录具有相同的主键,则第二条记录将覆盖第一条记录。听起来有点奇怪。