Here is a much simpler version:
——————————-
Create table t1 (c1 number primary);
session1:
insert into t1 values (1);
session2:
insert into t1 values (1);
— would hang
v$lock would reveal session 1 blocking session2 on TX lock
why? because you have a constraint where you are asking Oracle to check uniqueness of data (unique index/unique constraint / primary key).
So Oracle looks as session-2’s data and checks the existing data..it sees session-1’s data uncommitted. Now Oracle doesn’t know if session-1 would commit or rollback. so it has to wait. this is by design.its always been like this.(so session 2 has to enqueue behind session 1 )
if session-1 commits, session would fail since unique key constraint is violated..if session-1 rolls back, session-2 would go through fine.
Its a lock based on “data” . It doesn’t matter what your statement is..(“insert into table values..” or “insert based on select” )..the behavior would be the same.
You don’t see the 2 sessions blocking each other (inserting the same key) when you don’t have any consistency checks (unique index/constraint). Oracle knows it can allow any data..so no locks based on data.
Reference : http://hemantoracledba.blogspot.com/2007/10/inserts-holding-locks-inserts-waiting.html