Oracle foreign key is not indexed causing deadlocks

-- Create a table that acts as a child table

CREATE TABLE fk_t as select *from user_objects;

Delete from fk_t where object_id is null;

Commit

-- creates a table that is the parent table

CREATE TABLE pk_t as select *from user_objects;

Delete from pk_t where object_id is null;

Commit

-- Create a primary key for a parent table

ALTER TABLE pk_t add constraintpk_pktable primary key (OBJECT_ID);

-- Create a foreign key for a child table

ALTER TABLE fk_t addconstraint fk_fktable foreign Key (object_id) references pk_t (object_id);

--session1: performs a delete operation by adding a row-s (SX) lock on 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 discover that the second DELETE statement waits

Delete from fk_t whereobject_id=200;

Delete from pk_t whereobject_id=200;

-- back to SES Sion1: A deadlock happens immediately

Delete from pk_t whereobject_id=100;

Error in Session2:

Sql> Delete from pk_table where object_id=200;
Delete from pk_table where object_id=
*
An error occurred on line 1th:
ORA-00060: Deadlock detected while waiting for resource

When the foreign key column of a child table is indexed, the deadlock is eliminated because the parent table record is not required to add a table-level lock to the child table.

-- index a foreign key

Create INDEX ind_pk_object_id on fk_t (object_id) nologging;

-- Repeat the above operation 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 Sessi On1 No deadlock occurs

Delete from pk_t whereobject_id=100;