TM enqueue with mode=3

There is yet another case when INSERT statement can be blocked.
According to this Oracle doesn`t acquire table locks on the child table during insert into parent table. But I`ve discovered something else.

Suppose you have a MASTER table and a DETAIL table with UNINDEXED foreign key constraint which references the MASTER table. Foreign key has been created with ON DELETE CASCADE option. And there are some rows in both tables.

Session 1 does the following:
delete from master where id=some_value;

There is no commit here.

Session 2:
delete from master where id=other_value;

Now Session 2 is stuck with TM enqueue request in mode=5 for DETAIL table. This is well known behavior.

But when Session 3 tries to insert a row into the MASTER table, it is stuck too with TM enqueue request in mode=3 for DETAIL table. By the way, mode=3 is used on Oracle version 11.2.0.2, and 10.2.0.4 shows mode=2 in this case.

Here is useful SQL to reproduce the case.

--Setup

drop table detail;
drop table master;
create table master as select * from all_objects where 1=2;
alter table master add constraint master_pk primary key (object_id);
create table detail as select * from all_objects where 1=2;
alter table detail add constraint detail_fk foreign key (object_id) references master on delete cascade;
insert into master select * from all_objects;
insert into detail select * from master;
commit;

--For Session 1
delete from master where object_id=100;

--For Session 2
delete from master where object_id=101;

--For Session 3

insert into master
  (owner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time, timestamp, status, temporary, generated, secondary/*for 11g, namespace, edition_name*/)
values
  ('a', 'x', null, 1000000, null, null, sysdate, sysdate, null, null, null, null, null/*, 1, null*/);



--For monitoring

select l.sid,
       l.type,
       l.lmode,
       l.request,
       l.block,
       o.owner object_owner,
       o.object_Name,
       q.sql_text
  from sys.all_objects o, v$lock l, v$session s, v$sql q
 where l.sid = s.sid
   and o.object_id(+) = l.id1
   and s.sql_id = q.sql_id(+)
 order by l.sid, o.object_Name;