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 188.8.131.52):
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.
I have two table with the same columns in different databases. Both table have records.i want to insert the records of table2 in table1 but i want to ignore those records which are already in table 1. As well i want to store all ignored records in a new table. Example:
create table dest
(id number primary key,
create table src
insert into src values(1,’ABC’);
insert into src values(2,’GHB’);
insert into src values(3,’DUP’);
insert into src values(3,’DUP’);
merge into dest
(select id,col1 from src) src on(dest.id=src.id)
when not matched then
then update set dest.col1=src.col1;
Error report – SQL Error: ORA-00001: unique constraint (SCOTT.SYS_C0010807) violated 00001. 00000 – “unique constraint (%s.%s) violated” *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key.
It sounds what you might need in this situation is an INSERT with the LOG ERRORS clause (http://docs.oracle.com/database/121/SQLRF/statements_9014.htm#BGBEIACB). The first step is to create the error log table, using the package DBMS_ERRLOG (specifying the table you want to the error log table to be based on; i.e. DEST):
This creates a table named ERR$_DEST. We can then run a simple INSERT with the LOG ERROR clause:
The error entries from the INSERT will then be in the error log table:
This is an excellent subject for a new post. Many thanks!
Why not just handle the exception after the MERGE statement?
Yes, handling the exception thrown by the MERGE may very well be a solution. It does depend on the specifics of the scenario though. In the post I used a MERGE of just a single row so that would work well. If multiple rows were being merged then the solution will need to be different as there could be a mix of inserts and updates going on. In that scenario the best solution might be to repeat the MERGE operation.
Pingback: [Solved] Oracle – Prevent unique constraint on first insert of merge – Ten-tools.com