INSERT into unique column same value from two sessions (Oracle)

Question

I have an issue: I have table T with one column with unique constraint

CREATE TABLE T (ID NUMBER,
                UNIQUE (ID));

Session 1 done insert into that table

INSERT INTO T(id) VALUES(1);

Session 2 is trying to MERGE the same value to that table

 MERGE INTO t
 USING (SELECT 1 col FROM dual) s
    ON (t.id = s.col)
  WHEN NOT MATCHED THEN 
INSERT (id) VALUES (col);

At that moment Session 2 is blocked and waiting for Session 1 to be committed or rollbacked. Now I run in Session 1

COMMIT;

At that moment an error occurred in Session 2

ORA-00001: unique constraint violated

Is there any options how can I avoid it?

P.S. the problem is that I have INSERT into some table and MERGE (using UNIQUE columns in ON section) at the same table. This INSERT and MERGE are called separately in two different sessions. And sometimes MERGE falls because of situation described upper. I hope I described it understandably

Answer 1

Your example is a subset of the phantom reads problem. Phantom reads and your problem are just a property of relational databases. I recommend reading chapter 7 of Kleppmann's Designing Data-Intensive Applications.

Your options are not to be taken lightly:

1. Redesign the application with optimistic locks.
2. Change the database isolation level to SERIALIZABLE, which will both slow individual transactions down and reduce the database's ability to run transactions in parallel.

In my experience, most designers choose the third option of living with the problem. Depending on your non-functional requirements, it can be better to keep your application simple rather than theoretically correct.