title: Oracle MERGE statements executed in parallel transactions: How to prevent duplicate keys?
body: <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>