Oracle Foreign Key Without Indexing Causes Deadlock Example

--Create 1 table as a child table

create table fk_t as select *from user_objects;

delete from fk_t where object_id is null;

commit;

--Create 1 table with this table as the parent table

create table pk_t as select *from user_objects;

delete from pk_t where object_id is null;

commit;

--Create the primary key of the parent table

alter table PK_t add constraintpk_pktable primary key (OBJECT_ID);

--Create foreign keys for child tables

alter table FK_t addconstraint fk_fktable foreign key (OBJECT_ID) references pk_t (OBJECT_ID);

--session1: Perform a delete operation, at which time an Row-S (SX) lock is added to both the child table and the parent table

delete from fk_t whereobject_id=100;

delete from pk_t where object_id=100;

--session2: Perform another delete operation and find that the second delete statement is waiting at this time

delete from fk_t whereobject_id=200;

delete from pk_t whereobject_id=200;

--Back to session1: Deadlock occurs immediately

delete from pk_t whereobject_id=100;

Error reported in session2:

SQL > delete from pk_table where object_id=200;
delete from pk_table where object_id=200
*
Error on Line 1:

ORA-00060: Deadlock detected while waiting for resource

When an index is added to the foreign key column of the child table, the deadlock is eliminated because deleting the parent table record does not require a table-level lock on the child table.

--Indexing foreign keys

create index ind_pk_object_id on fk_t(object_id) nologging;

--Repeat the above session1

delete from fk_t whereobject_id=100;

delete from pk_t whereobject_id=100;

--session2

delete from fk_t whereobject_id=200;

delete from pk_t whereobject_id=200;

--Back to session1 there will be no deadlock

delete from pk_t whereobject_id=100;