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.
|
|
|