有没有办法以原子方式执行SQL合并？

I am looking for a way to connect a tuple of values with a random UUID in a non-locking manner and without the potential to fail a transaction due to concurrency constraints.

我正在寻找一种方法来以非锁定的方式连接值的元组与随机UUID,并且由于并发约束而不会使事务失败。

The table I need to edit contains several values that should be described by a UUID. In this example, the table is named foo and declares two string columns bar and qux which point to a single field uuid. (bar, qux) is required to be unique throughout the table. UUIDs are unique by nature.

我需要编辑的表包含应由UUID描述的几个值。在此示例中,该表名为foo,并声明了两个字符串列bar和qux,它们指向单个字段uuid。 (bar,qux)在整个表格中必须是唯一的。 UUID本质上是独一无二的。

I wonder if SQL (using Oracle 12c) is capable of doing something like the following atomically:

我想知道SQL(使用Oracle 12c)是否能够以原子方式执行以下操作:

MERGE INTO foo a
  USING (SELECT bar, qux FROM foo b
  ON b.bar = 'a' and b.qux = 'b'
WHEN NOT MATCHED THEN INSERT (a.bar, a.qux, a.uuid)
  VALUES ('a', 'b', 'some-uuid');

SELECT uuid FROM foo WHERE bar = 'a' and qux = 'b';

As a result of my database query, I want the tuple (bar, qux) to be connected with a random UUID. This UUID must be the same for any concurrent transaction and I do not want the competing requests to fail because of a concurrent insert of another (random) UUID.

作为我的数据库查询的结果,我希望元组(bar,qux)与随机UUID连接。对于任何并发事务,此UUID必须相同,并且由于并发插入另一个(随机)UUID,我不希望竞争请求失败。

As a background: These inserts are part of a rather long-running transaction which are for the most part independant from one another but have this shared identificator table where values must not disagree. Many programming languages offer CAS and this would be what I am going for in this case but I would not know of a smilar feature in SQL.

作为背景:这些插入是一个相当长时间运行的事务的一部分,这些事务在很大程度上是彼此独立的,但是具有这个共享的标识符表,其中值不能不同意。许多编程语言提供CAS,这就是我在这种情况下的目的,但我不知道SQL中的一个微笑功能。

As one idea, I wondered if allowing for dirty reads (uncommitted reads isolation level) would be a solution but I have no idea if the merge statement is atomic and visible to other transactions in this case. (This is impossible in Oracle.) The database is accessed via JDBC but from potentially multiple VM nodes.

作为一个想法,我想知道是否允许脏读(未提交读取隔离级别)将是一个解决方案,但我不知道合并语句是否是原子的,在这种情况下是否对其他事务可见。 (这在Oracle中是不可能的。)数据库是通过JDBC访问的,但可能来自多个VM节点。

2 个解决方案

#1

You could encapsulate the MERGE and SELECT statements in a PL/SQL function which is defined with the AUTONOMOUS_TRANSACTION pragma.

您可以将MERGE和SELECT语句封装在使用AUTONOMOUS_TRANSACTION编译指示定义的PL / SQL函数中。

If you get a unique constraint violation because another session just inserted the same UUID, you can catch the exception in the function and just select and return the UUID.

如果由于另一个会话刚刚插入了相同的UUID而导致唯一约束违规,则可以捕获该函数中的异常,然后选择并返回UUID。

This way the MERGE statement locks the record only for a short period of time (as long as the function takes to execute) and you don't infer your applications current transaction, because the function runs in a seperate transaction and does the error handling in case of a unique constraint violation.

这样MERGE语句只会在很短的时间内锁定记录(只要该函数执行)并且您不会推断应用程序当前事务,因为该函数在单独的事务中运行并且在唯一约束违规的情况。

#2

You can't do it from withing the long-running transactions because the insert is going to be visible only after the transaction is committed.

您无法使用长时间运行的事务来执行此操作,因为仅在提交事务后才会显示插入。

What you need to do is open a new transaction from the application layer and then to the MERGE or UPSERT. This way, the MERGE/UPSERT atomicity is guaranteed by the secondary transaction that you issue. This way, once the secondary transaction is committed, the long-running ones will see the changes if you are running in READ_COMMITTED, but not SERIALIZABLE.

您需要做的是从应用程序层打开一个新事务,然后再打开MERGE或UPSERT。这样,MERGE / UPSERT原子性由您发出的辅助事务保证。这样,一旦提交了辅助事务,如果您在READ_COMMITTED中运行,而不是SERIALIZABLE,则长时间运行的事务将看到更改。

A similar approach is taken the Hibernate, TABLE-based identifier. You can read more about it in this article.

类似的方法采用基于TABLE的Hibernate标识符。您可以在本文中阅读有关它的更多信息。

#1