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):