I have a third party application which is written with foreign keys contraints for which there appears to be a complete absence of foreign keys indexes.

I am seeing massive trace files generated throughout the day for a deadlocking "instead of" trigger.

The piece of code which appears to be deadlocking is:

insert into material_requests
(quantity,operatives_rec_id,material_lookups_rec_id, materialtype_rec_id, timestamp, status)
select
:new.quantity,
hu.operatives_rec_id,
ml.rec_id, mt.rec_id,
:new.timestamp,
:new.status
from
material_lookups ml,
materialtype mt,
hhc_useage hu,
hhc h
where
ml.code = :new.code
and hu.date_to is null
and hu.hhc_rec_id = h.rec_id
and h.device_id = :new.device_id
and mt.code = :new.materialtype_code;

The target table "MATERIAL_REQUESTS" has one foriegn key on "MATERIAL_LOOKUPS_REC_ID" that's related to "REC_ID" on the queried table "MATERIAL_LOOKUPS".

The deadlock graph from the trace file contains:


Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TM-00000ff0-00000000 6 26 S SSX 30 27 S SSX
TM-00000ff0-00000000 30 27 S SSX 6 26 S SSX
session 26: DID 0001-0006-00000002 session 27: DID 0001-001E-00000002
session 27: DID 0001-001E-00000002 session 26: DID 0001-0006-00000002
Rows waited on:
Session 27: no row
Session 26: no row
===================================================

I'm thinking this deadlock is due to an unindexed foreign key problem, but haven't ever really looking looked into such issues much before. Do I presume the target table foreign key column is the one that need indexing? (I.e. MATERIAL_REQUESTS.MATERIAL_LOOKUPS_REC_ID)


- T.