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

 

Session 1

Session 2

insert into table1(pk, col1) values (1, 123);

1 row created.

 

COMMIT; -- commit here

 

insert into table2(pk, table1_ref_key, col1) values (1, 1, 123);

1 row created.

-- no commit yet

 

 

delete from table1 where pk=1;

-- Waiting

insert into table3(pk, table1_ref_key, col1) values (1, 1, 123);

-- Waiting

 

 

ORA-00060: deadlock detected while waiting for resource

 

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

 

Session 1

Session 2

insert into table1(pk, col1) values (1, 123);

1 row created.

 

COMMIT; -- commit here

 

 

delete from table1 where pk=1;

1 row deleted.

select * from table1 where pk =1 for update;

 

-- Waiting

 

 

 

 

select * from table1 where pk =1 for update;

 

no rows selected

 

COMMIT; -- commit here

insert into table2(pk, table1_ref_key, col1) values (1, 1, 123);
insert into table2(pk, table1_ref_key, col1) values (1, 1, 123)
*
ERROR at line 1:
ORA-02291: integrity constraint (WORK.FK_TABLE2__TABLE1_PK) violated - parent
key not found

 

 

insert into table3(pk, table1_ref_key, col1) values (1, 1, 123);
insert into table3(pk, table1_ref_key, col1) values (1, 1, 123)
*
ERROR at line 1:
ORA-02291: integrity constraint (WORK.FK_TABLE3__TABLE1_PK) violated - parent
key not found

 

 

 Example Scenario 2 (When insert statement initiate first):

 

Session 1

Session 2

insert into table1(pk, col1) values (1, 123);

1 row created.

 

COMMIT; -- commit here

 

select * from table1 where pk =1 for update;

 

        PK       COL1

---------- ----------

         1        123

 

insert into table2(pk, table1_ref_key, col1) values (1, 1, 123);

1 row created.

-- no commit yet

 

 

delete from table1 where pk=1;

-- Waiting

select * from table1 where pk =1 for update;

 

        PK       COL1

---------- ----------

         1        123

insert into table3(pk, table1_ref_key, col1) values (1, 1, 123);

1 row created.

-- delete command still waiting

COMMIT; -- commit here

 

 

delete from table1 where pk=1;

1 row created.


Comments