Is there a way to execute an SQL merge atomically?

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.

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.

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

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.

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.

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.

Answer

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

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.

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.

Advertisement

Answer