Oracle MERGE statements executed in parallel transactions: How to prevent duplicate keys?
<p>Let us imagine we have some nice, simple table:</p>

<pre><code>CREATE TABLE blah (
    some_key VARCHAR(32),
    some_value VARCHAR(32)
);
</code></pre>

<p>For some reason we do not want to define a primary key or a unique index on the <code>some_key</code> column.</p>

<p>We now execute to <code>MERGE</code> statements in parallel transactions (i.e. the second one is started before the first one committed):</p>

<pre><code>MERGE INTO blah blah
USING (SELECT 'some_key' some_key, 'a_value' some_value FROM DUAL) rec
ON (blah.some_key = rec.some_key)
WHEN MATCHED THEN
UPDATE SET blah.some_value = rec.some_value
WHEN NOT MATCHED THEN
INSERT (blah.some_key, blah.some_value) VALUES (rec.some_key, rec.some_value);
</code></pre>

<p>and:</p>

<pre><code>...
USING (SELECT 'some_key' some_key, 'another_value' some_value FROM DUAL) rec
...
</code></pre>

<p>After committing both a <code>SELECT</code>shows the following:</p>

<pre><code>| SOME_KEY | SOME_VALUE    | 
|----------|---------------|
| some_key | a_value       |
| some_key | another_value |
</code></pre>

<p>I understand the mechanics behind this (i.e. the two merge statements not "seeing" each other), but this is somehow clearly not the desired result.</p>

<p>Of course I can define a <code>UNIQUE</code> key. Then the second staements fails - which is also explainable but somehow unexpected from the <code>MERGE</code>statement.</p>

<p>Is there any way to make <code>MERGE</code> do a "real" <code>MERGE</code> without any duplicate "keys"?</p>