2017/01/18

MERGE statement can fail with ORA-00001: unique constraint violated

How can MERGE statement fail with ORA-00001: unique constraint violated if all other things have been done right? I mean both source and target tables have primary key constraints and all columns from that constraints are mentioned in ON clause.
There is a tiny window of possibility. Suppose the first session inserts a row into source table with commit, then the second session starts long-running MERGE into the target table. While it is running, the first session inserts the same row into target table and commits it. When the second session reaches that new row, it gets ORA-00001.
When the second session started MERGE statement, the first thing it did was that it outer-joined the source and target tables. At that moment questioned row existed in source table, but did not exist in target table (or invisible due to uncommited transaction), so, MERGE decided to put the row into insert branch of MERGE statement.

Here is a test-case for reproducing this effect.

First session:
--preparation
drop table t1 purge;
drop table t2 purge;

create table t1 (
 OBJECT_ID     NUMBER,
 OBJECT_NAME   VARCHAR2(128),
 OBJECT_TYPE   VARCHAR2(23),
 filler        char(1000) default 'X'
 );

insert into t1 select OBJECT_ID,OBJECT_NAME,OBJECT_TYPE, 'Y' from all_objects where rownum < 5000;
create table t2 as select * from t1 where 1=2;
alter table t1 add constraint t1_pk primary key (object_id);
alter table t2 add constraint t2_pk primary key (object_id);

--start of the experiment
insert into t1 (object_id, object_name, object_type) values (100000,'test_obj', 'test_type');
commit;
insert into t2 (object_id, object_name, object_type) values (100000,'test_obj', 'test_type');

Second session:
merge into t2 trg
 using t1 src
 on (trg.object_id=src.object_id)
 when matched then update set
 trg.object_name=src.object_name
 when not matched then insert
 (trg.object_id,trg.object_name, trg.object_type)
 values

 (src.object_id,src.object_name,src.object_type);

First session:
commit;

Second session:
merge into t2 trg
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.T2_PK) violated

No comments: