Example of deadlock caused by Oracle foreign key not indexed

-- Create a table as a sub-table

Create table fk_t as select * from user_objects;

Delete from fk_t where object_id is null;

Commit;

-- Create a 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 a foreign key for the sub-table

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

-- Session1: execute a delete operation. At this time, a Row-S (SX) Lock is added to the sub-table and parent table.

Delete from fk_t whereobject_id = 100;

Delete from Maid where object_id = 100;

-- Session2: execute another delete operation and find that the second Delete statement is waiting.

Delete from fk_t whereobject_id = 200;

Delete from pk_t whereobject_id = 200;

-- Return to session1: the deadlock occurs immediately

Delete from pk_t whereobject_id = 100;

Session2 reports the following error:

SQL> delete from Maid where object_id = 200;
Delete from pk_table where object_id = 200
*
Row 3 has an error:

ORA-00060: deadlock detected while waiting for resources

When an index is added to the foreign key column of the child table, the deadlock is eliminated because the table lock is not required to delete the parent table record.

-- Create an index for the 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;

-- No deadlock will occur when session1 is returned.

Delete from pk_t whereobject_id = 100;