title: Oracle MERGE deadlock
body: <p>I want to insert rows with a MERGE statement in a specified order to avoid deadlocks. Deadlocks could otherwise happen because multiple transaction will call this statement with overlapping sets of keys. Note that this code is also sensitive to duplicate value exception but I handle that by retrying so that is not my question. I was doing the following:</p>
  
  <pre><code>MERGE INTO targetTable
  USING (
  SELECT ...
  FROM sourceCollection
  ORDER BY &lt;desiredUpdateOrder&gt;
  )
  WHEN MATCHED THEN 
  UPDATE ...
  WHEN NOT MATCHED THEN
  INSERT ...
  </code></pre>
  
  <p>Now I'm still getting the dead lock so I'm becoming unsure whether oracle maintains the order of the sub-query. Does anyone know how to best make sure that oracle locks the rows in targetTable in the same order in this case? Do I have to do a SELECT FOR UPDATE before the merge? In which order does the SELECT FOR UPDATE lock the rows? Oracle UPDATE statement has an ORDER BY clause that MERGE seems to be missing. Is there another way to avoid dead locks other than locking the rows in the same order every time?</p>
  
  <p>[Edit]
  This query is used to maintain a count of how often a certain action has taken place. When the action happens the first time a row is inserted, when it happens a second time the "count" column is incremented. There are millions of different actions and they happen very often. A table lock wouldn't work.</p>