ORA-00001 unique key violated with MERGE

I arrived at work one morning to find a system generated email telling me that a user session had thrown a unique constraint violation exception. Curious given the code had been running in production for 6 months…

The line number for the PL/SQL package that had thrown the exception showed that it came from a MERGE statement. The fact that it was a MERGE that had thrown an ORA-00001 immediately points to a concurrency issue. At first the developer of the code didn’t understand how a MERGE could throw ORA-00001 until I showed them the following example.

Starting with a simple two column table with 10 rows (running under Oracle 12.1.0.2):

CREATE TABLE merge_test
    (id     NUMBER(6) NOT NULL
    ,val    NUMBER(6) NOT NULL
    ,CONSTRAINT merge_test_pk PRIMARY KEY (id))
/

INSERT INTO merge_test
SELECT ROWNUM
,      ROWNUM
FROM   dual
CONNECT BY ROWNUM <= 10
/

COMMIT
/

EXEC dbms_stats.gather_table_stats ('','merge_test')

we run the following MERGE in two sessions, operating against an existing row in the table:

MERGE INTO merge_test dest
USING (SELECT 5    AS id
       ,      20   AS val
       FROM   dual) src
ON (dest.id = src.id)
WHEN MATCHED THEN UPDATE
SET dest.val = src.val
WHEN NOT MATCHED THEN INSERT
   (id, val)
VALUES
   (src.id, src.val)

The first session immediately reports:

1 row merged.

while the second session, attempting to obtain a lock on the same row, is blocked. Once the first session issues a commit or rollback then the second session also reports 1 row merged.

Now let’s repeat this exercise using an ID value for a row that does not exist in the table:

MERGE INTO merge_test dest
USING (SELECT 20   AS id
       ,      20   AS val
       FROM   dual) src
ON (dest.id = src.id)
WHEN MATCHED THEN UPDATE
SET dest.val = src.val
WHEN NOT MATCHED THEN INSERT
   (id, val)
VALUES
   (src.id, src.val)

Session 1 reports the 1 row merged while session 2, after session 1 commits, reports:

MERGE INTO merge_test dest
*
ERROR at line 1:
ORA-00001: unique constraint (DEVELOPER.MERGE_TEST_PK) violated

The reason for this is all related to Oracle’s multi-versioning read consistency model. At the point in time that session 2 executed the MERGE session 1 had not committed the data so session 2 cannot “see” the row that session 1 is about to insert. As a result the MERGE that session 2 executes also attempts to insert a new row. Oracle then blocks session 2 as it detects a uniqueness conflict with the row that session 1 is inserting. Oracle does not immediately report the exception as it needs to wait until session 1 commits or rollbacks its transaction. When session 1 commits the transaction then session 2 throws the ORA-00001 exception. If session 1 had issued a rollback then session 2 would have been permitted to insert the new row.

The solution to this problem is to simply remove the MERGE and replace it with an INSERT/UPDATE combination:

BEGIN
   INSERT INTO merge_test (id, val)
   VALUES (20, 20);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      UPDATE merge_test
      SET    val = 20
      WHERE  id = 20;
END;

With this code session 2 will block on its attempt to perform the INSERT. Once session 1 issues a commit then session 2 intercepts the DUP_VAL_ON_INDEX exception that is thrown, which is then caught and the operation is changed to an UPDATE.

Back to the production problem, a scan of the application log files and database entries did indeed show that there were two near simultaneous request that would have resulted in the MERGE being run for the same key, confirming the diagnosis. Another problem solved and, for the developer of the code, another lesson learned.