I am looking for a way to connect a tuple of values with a random UUID in a non-blocking way and without the possibility of a transaction failure due to concurrency restrictions.
The table I need to change contains several values that must be described by the UUID. In this example, a table is named fooand declares two columns of a row barand quxthat point to a single field uuid. (bar, qux)required to be unique throughout the table. UUIDs are unique in nature.
I wonder if SQL (using Oracle 12c) is capable of doing something like atomic:
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 to (bar, qux)be associated with a random UUID. This UUID must be the same for any concurrent transaction, and I do not want competing requests to fail due to the parallel insertion of another (random) UUID.
As a background: these inserts are part of a rather lengthy transaction, which for the most part is independent of each other, but has this common identifier table, the values of which should not contradict. Many programming languages offer CAS, and this will be what I am going to do in this case, but I would not know about a familiar function in SQL.
, ( ) , , . ( Oracle.) JDBC, VM.