Skip to Main Content
  • Questions
  • ORA-00001 on merge, concurent transactions


Question and Answer

Connor McDonald

Thanks for the question.

Asked: November 29, 2022 - 2:38 pm UTC

Last updated: March 18, 2024 - 4:41 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

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:

--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?

and Connor said...

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

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


  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment


additonal question

Stranger, March 15, 2024 - 12:43 am UTC

How to second session knows whether the row that first session write is commited or don’t ??

Connor McDonald
March 18, 2024 - 4:41 am UTC

Not entirely I know what you're asking but with any blocked insert

- you try to write to a unique key which must go in a particular spot in a particular index block (because that's what indexes are)

- you see an active lock on that entry so you wait.

- when the lock clears, you now obtain the lock and you'll either see no entry (you can insert) and an entry now present (without any active transaction associated with it) and thus you get a duplicate key violation

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library