我可以在Oracle中做一个原子MERGE吗？ [英] Can I do an atomic MERGE in Oracle?

问题描述

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

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

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

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.

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

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.

我的问题是这是：在Oracle有一个原子MERGE？我正在寻找类似的效果与 INSERT ... FOR UPDATE 在PL / SQL中，除了我只能从我的应用程序执行SQL。

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.

编辑：我不清楚。我使用MERGE语句，而这个错误仍然发生。事实是，只有修改部分是原子的，而不是整个合并。

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语句无法看到第一个会话执行的插入，直到该会话提交。

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.

或者，您可以对数据进行排序或分区，以便将给定主数据库的所有记录键将被给予同一会话。一个简单的函数，如主键模N应该平均分配给N个会话。

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，如果两个记录具有相同的主键，第二个将覆盖第一个。听起来有点奇怪。

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

这篇关于我可以在Oracle中做一个原子MERGE吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！