Is there a way to execute an SQL merge atomically?

Question

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.

How often do you expect to see collisions normally? If not too often, you could put a unique constraint on (bar,qux), a function that queries the table, with no_data_found calling another function that runs an autonomous transaction to attempt the insert, with a constraint handler + loop (with a fixed retry count) to handle collisions. — Jeffrey Kemp, Feb 05 '17 at 15:00
Long-running transactions are a definite no-no in any RDBMS I've ever used. — Bob Jarvis - Слава Україні, Feb 05 '17 at 15:19
Proof of concept, open to discussion/criticisim: https://livesql.oracle.com/apex/livesql/file/content_EJAH6LN5T4BIIWB6PTN3OTUMU.html — Jeffrey Kemp, Feb 05 '17 at 15:39
Long-running as in: "batch-job long" with regular commit windows. — Rafael Winterhalter, Feb 06 '17 at 08:18

Vlad Mihalcea · Answer 1 · 2021-01-10T14:26:54.710

2

You can't do it from within 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.

edited Jan 10 '21 at 14:26

answered Feb 05 '17 at 11:24

Vlad Mihalcea

142,745
71
566
911

I tested this and it does work best by running the transaction in parallel. After all, I did however find out that consuming the data to a raw data table and having a dedicated, single-worker that assigns the uuids without potential conflicts is a much faster option. – Rafael Winterhalter Feb 06 '17 at 10:54

score 1 · Accepted Answer · answered Feb 06 '17 at 13:28

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.

Is there a way to execute an SQL merge atomically?

2 Answers2