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.

Answer 1

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.

Answer 2

I don't believe the ORDER BY will affect anything (though I'm more than willing to be proven wrong); I think MERGE will lock everything it needs to.

Assume I'm completely wrong, assume that you get row-by-row locks with MERGE. Your problem still isn't solved as you have no guarantees that your two MERGE statements won't hit the same row simultaneously. In fact, from the information given, you have no guarantees that an ORDER BY improves the situation; it might make it worse.

Despite there being no skip locked rows syntax as there is with UPDATE there is still a simple answer, stop trying to update the same row from within different transactions. If feasible, you can use some form of parallel execution, for instance the DBMS_PARALLEL_EXECUTE subprogram CREATE_CHUNKS_BY_ROWID and ensure that your transactions only work on a specific sub-set of the rows in the table.

As an aside I'm a little worried by your description of the problem. You say there's some duplicate erroring that you fix by rerunning the MERGE. If the data in these duplicates is different you need to ensure that the ORDER BY is done not only on the data to be merged but the data being merged into. If you don't then there's no guarantee that you don't overwrite the correct data with older, incorrect, data.

Answer 3

First locks are not really managed at row level but at block level. You may encounter an ORA-00060 error even without modifying the same row. This can be tricky. Managing this is the request developper's job.

One possible workaround is to organize your table (never do that on huge tables or table with heavy change rates)

https://use-the-index-luke.com/sql/clustering/index-organized-clustered-index

Answer 4

Rather than do a merge, I suggest that you try and lock the row. If successful update it, if not insert new row. By default lock will wait if another process has a lock on the same thing.

CREATE TABLE brianl.deleteme_table
(
    id     INTEGER PRIMARY KEY
  , cnt    INTEGER NOT NULL
);

CREATE OR REPLACE PROCEDURE brianl.deleteme_table_proc (
    p_id   IN deleteme_table.id%TYPE)
    AUTHID DEFINER
AS
    l_id   deleteme_table.id%TYPE;
    -- This isolates this procedure so that it doesn't commit
    -- anything outside of the procedure.
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    -- select the row for update
    -- this will pause if someone already has the row locked.
    SELECT id
      INTO l_id
      FROM deleteme_table
     WHERE id = p_id
    FOR UPDATE;

    -- Row was locked, update it.
    UPDATE deleteme_table
       SET cnt   = cnt + 1
     WHERE id = p_id;

    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        -- we were unable to lock the record, insert a new row
        INSERT INTO deleteme_table (id, cnt)
             VALUES (p_id, 1);

        COMMIT;
END deleteme_table_proc;

CREATE OR REPLACE PROCEDURE brianl.deleteme_proc_test
    AUTHID CURRENT_USER
AS
BEGIN
    -- This resets the table to empty for the test
    EXECUTE IMMEDIATE 'TRUNCATE TABLE brianl.deleteme_table';

    brianl.deleteme_table_proc (p_id => 1);
    brianl.deleteme_table_proc (p_id => 2);
    brianl.deleteme_table_proc (p_id => 3);
    brianl.deleteme_table_proc (p_id => 2);

    FOR eachrec IN (  SELECT id, cnt
                        FROM brianl.deleteme_table
                    ORDER BY id)
    LOOP
        DBMS_OUTPUT.put_line (
            a   => 'id: ' || eachrec.id || ', cnt:' || eachrec.cnt);
    END LOOP;
END;

BEGIN
    -- runs the test;
    brianl.deleteme_proc_test;
END;