Oracle MERGE deadlock

Question

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:

MERGE INTO targetTable
USING (
SELECT ...
FROM sourceCollection
ORDER BY <desiredUpdateOrder>
)
WHEN MATCHED THEN 
UPDATE ...
WHEN NOT MATCHED THEN
INSERT ...

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?

[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.

You might try ordering the target table as you want by merging into a SELECT, as in: MERGE INTO (SELECT * FROM targetTable ORDER BY <desiredUpdateOrder>) USING .... Not sure if this will solve your problem but probably worth a shot. Share and enjoy. — Bob Jarvis - Reinstate Monica
Is performance critical (response time, in particular)? If not, have you considered serializing the code (take an application lock before running the merge, then release on commit)? — Jeffrey Kemp
Does the maintenance of the target table absolutely have to be synchronous with the changes, or could it be delayed? Have you considered a materialised view for storing this information? — David Aldridge

David Aldridge David Aldridge · Accepted Answer · 2013-05-17T06:59:33

Controlling the order in which the target table rows are modified requires that you control the query execution plan of the USING subquery. That's a tricky business, and depends on what sort of execution plans your query is likely to be getting.

If you're getting deadlocks then I'd guess that you're getting a nested loop join from the source collection to the target table, as a hash join would probably be based on hashing the source collection and would modify the target table roughly in target-table rowid order because that would be full scanned -- in any case, the access order would be consistent across all of the query executions.

Likewise, if there was a sort-merge between the two data sets you'd get consistency in the order in which target table rows are accessed.

Ordering of the source collection seems to be desirable, but the optimiser might not be applying it so check the execution plan. If it is not then try inserting your data into a global temporary table using APPEND and with an ORDER BY clause, and then selecting from there without an order by clause, and explore the us of hints to entrench a nested loop join.

Oracle MERGE deadlock

2 Answers