我可以在 Oracle 中进行原子合并吗？

Can I do an atomic MERGE in Oracle?

提问by Russell

I have a couple instances of a J2EE app running in a single WebLogic cluster.

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

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 is there or not. If it's there, update. If not, insert.

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

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.

现在假设两个应用程序实例想要插入或更新主键 = 100 的行。假设该行不存在。在合并的“检查”阶段，他们都看到行不在那里，所以他们都试图插入。然后我得到一个唯一键约束违规。

My question is this: Is there an atomic MERGE in Oracle? I'm looking for something that has a similar effect to INSERT ... FOR UPDATEin PL/SQL except that I can only execute SQL from my apps.

我的问题是：Oracle 中是否存在原子合并？我正在寻找与INSERT ... FOR UPDATEPL/SQL具有类似效果的东西，只是我只能从我的应用程序中执行 SQL。

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.

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

采纳答案by redcayuga

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.

第二个会话中的 MERGE 语句不能“看到”第一个会话所做的插入，直到该会话提交。如果您减少交易的规模，发生这种情况的可能性就会降低。

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.

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

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

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

回答by APC

This is not a problem with MERGE as such. Rather the issue lies in your application. Consider this stored procedure:

这对 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;

So, this is the PL/SQL equivalent of a MERGE on T23. What happens if two sessions call it simultaneously?

因此，这是 T23 上 MERGE 的 PL/SQL 等价物。如果两个会话同时调用它会发生什么？

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 hangsbecause SSN1 has a lock on the unique index node for 100. When SSN1 commits SSN2 will hurl a DUP_VAL_ON_INDEX violation.

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

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.

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

One way to handle this is to introduce pessimistic locking. At the start of the UPSERT_T23 procedure we lock the table:

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

...
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.

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

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.

INSERT 没有相当于 SELECT ... FOR UPDATE 的语法，因为没有什么可以选择的。所以 MERGE 也没有这样的语法。您需要做的是在发出 MERGE 的程序单元中包含 LOCK TABLE 语句。这对您来说是否可行取决于您使用的框架。

回答by Martin Schapendonk

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

是的，它被称为.... 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.

编辑：让这个水密的唯一方法是插入、捕获 dup_val_on_index 异常并适当处理它（更新或插入其他记录）。这可以使用 PL/SQL 轻松完成，但您不能使用它。

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

您也在寻找解决方法。您能在 Java 中捕获 dup_val_on_index 并再次发出额外的 UPDATE 吗？

In pseudo-code:

在伪代码中：

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

回答by Dave Costa

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.

我很惊讶 MERGE 会按照您描述的方式行事，但我还没有充分使用它来说明是否应该这样做。

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.

在任何情况下，您可能都希望执行合并的事务将其隔离级别设置为 SERIALIZABLE。我认为这可以解决您的问题。