Unnecessary Locking on Parent Table due to missing index on foreign key column in Child Table

If index not exists on foreign key column of a child table then it will create locks on the parent table when DML operation performed on child table.

Demonstration:

Create following sample tables. order_header is parent and order_detail is child table.


create table order_header (header_id number, customer varchar2(20))

alter table order_header add constraint pk_order_header  primary key (header_id)

create table order_detail (header_id number references order_header (header_id) on delete cascade , line_id number);

insert into order_header values (1,'a1');
insert into order_header values  (2,'a2');
insert into order_header values  (3,'a3');

insert into order_detail values  (1,1);
insert into order_detail values  (2,1);

commit;


Now, open two sessions to the database,

From session-1, delete a record from child table.
From session-2, delete a record from parent table. This transaction is going to be locked until the transaction in session-1 is either committed or rolled back.

To avoid this lock it is recommend to create a index on child table column.

create index order_detail_idx on order_detail (header_id);

Now, if we try the same delete statements, we wont see locking any more....