Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Parallel UNIQUE value insert blocking after DELETE behavior

David BalažicJun 24 2026 — edited Jun 24 2026

Currently using Oracle database 11.2 but updating real soon now to 19c

If i INSERT a row with a certain primary key value in one session and then INSERT the same primary key in second session (which will block), then DELETE the added row in first session, will the second session unlock immediately or only after the first session ends the transaction (either rollback or commit)?

I tested on version 11.2.0.4.0 and there the second session is unblocked only after the first session transaction is committed (or rolled back).

But, is this guarantied behavior?

Could it proceed after the first session deletes the inserted row?

Code:

-- session 1:
CREATE TABLE tab1(
     mykey VARCHAR2 (50) PRIMARY KEY
);

insert into tab1 (mykey) values ( '123' );
-- wait here and execute the line for session 2 below, then continue here
delete from tab1 where mykey = '123';
-- session 2 is still blocked
commit;
-- now session 2 is released

-session 2:
insert into tab1 (mykey) values ( '123' );
This post has been answered by Cookiemonster76 on Jun 25 2026
Jump to Answer
Comments
Post Details
Added on Jun 24 2026
14 comments
208 views