When having two simultaneous merges into a table with a unique constraint in different sessions, the second one throws ORA-00001.
Steps to reproduce:
create table tab1 (col1 number);
create unique index ind1 on tab1(col1);
--1st session:
merge into tab1 d
using (
select 1 col1 from dual
) s on (s.col1 = d.col1)
when not matched then insert (col1) values(s.col1);
--second session:
merge into tab1 d
using (
select 1 col1 from dual
) s on (s.col1 = d.col1)
when not matched then insert (col1) values(s.col1);
--second session now hangs
--first session:
commit;
--second session:
--throws ora-00001
As far as I know, this might be how Oracle behaves since Merge statment exists and might be considered to work as expected.
My objection to this is that merge behaves differently depending on first session being commited or not before the second session starts it's merge. The second session obviously knows it cannot just insert and hangs, waits for the first session to finish. And the second session is only blocked (hangs) if it is working with the same key of unique index. So, again, the second session is obviously aware that there is an uncommited session working on the same record. So when the firts session commits the record to the table (results in insert), the second session already desided that it too should insert and violates the constraint.
But why? Shouldn't the second session re-evaluate what the correct actions is?
If we tried this with [merge when matched] then the second session hangs until first one commits but even though the constrained columns is updated, there is no issue for the second session to update the same record.
Does it make sense or do you think "it works as intended" and shouldn't be addressed?
OK..can of worms here :-)
First of all, its important to understand the concept of "write consistency" and a "restart". That in itself is a big topic, but you can read about it here
https://asktom.oracle.com/pls/apex/asktom.search?tag=write-consistency Once you've digested that, there's a long standing issue with MERGE in that in some circumstances it does *not* obey the write consistency rules fully.
That bug, last time I checked, was marked as "not feasible to fix". Make of that what you will.
*Some* of these issues are resolved via MERGE aborting its current work. For some examples and variations of that see the video below