ORA-00060: deadlock detected while waiting for resource (Deadlock during delete from parent and insert on child)

create table table1(pk number(11) primary key, col1 number(11));

create table table2(pk number(11) primary key, table1_ref_key number(11), col1 number(11),

constraint fk_table2__table1_pk foreign key (table1_ref_key) references table1(pk) on delete cascade);

create table table3(pk number(11) primary key, table1_ref_key number(11), col1 number(11),

constraint fk_table3__table1_pk foreign key (table1_ref_key) references table1(pk) on delete cascade);

create index table2_ref_key on table2(table1_ref_key);

create index table3_ref_key on table3(table1_ref_key);

You will see ORA-00060: deadlock detected while waiting for resource in the following scenario

To validate the FK when you insert a row into table2 Oracle blockd deletes to the parents row in table1. Similarly when you delete a row from table1 Oracle block inserts of the potential children to ensure FK validation. So in the above scenario :

Session 1 preventing the delete from the parent and

Session 2 preventing the insert Into the children

Which cause the DEADLOCK

How to avoid it:

You can avoid the deadlock by issuing before inserting rows into child tables (Table2 and Table3):

select * from table1 where pk = :x for update;

Scenario 1:

If the delete starts first, the select will be blocked. When the delete commits, the inserts will go ahead and the FK validation will fail.

Scenario 2:

If the select for update starts first, the delete will have to wait for this to commit. Once you commit the inserts, the delete will go ahead. The side-effect of this is you'll end up deleting rows you just inserted!

Example Scenario 1 (When delete statement initiate; first):

Example Scenario 2 (When insert statement initiate first):