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' );