Repetitive Deadlock occurring on "Instead of" Trigger

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.

What are the columns in the PK of material_requests?

Tamil

Here is the format of the table MATERIAL_REQUESTS, with the PK being REC_ID.



SQL> desc material_requests;
Name Null? Type
----------------------------------------- -------- ----------------------------
REC_ID NOT NULL NUMBER(9)
REC_TS DATE
QUANTITY NUMBER(9)
MATERIAL_LOOKUPS_REC_ID NUMBER(9)
MATERIALTYPE_REC_ID NUMBER(9)
TIMESTAMP DATE
PROCESSED CHAR(1)
STATUS VARCHAR2(2)
OPERATIVES_REC_ID NUMBER(9)



Looking at the coding on the trigger: there are potential updates/inserts taking place against the foreign key (or other columns) in MATERIAL_LOOKUPS table, that is then queried later in the trigger to form the basis of values to finally insert to MATERIAL_REQUESTS.

There are also no integrity constraints on other fields that should link with other tables [OPERATIVES_REC_ID and MATERIALTYPE_REC_ID]



begin
if (:new.Status is not null) then
update material_lookups
set materialtype_rec_id =
(select rec_id from materialtype
where code = :new.materialtype_code),
status = :new.status,
location_changed_date = :new.timestamp
where code = :new.code;
else
update material_lookups
set materialtype_rec_id =
(select rec_id from materialtype
where code = :new.materialtype_code),
location_changed_date = :new.timestamp
where code = :new.code;
end if;

if sql%rowcount = 0 then
insert into material_lookups
(CODE, MATERIALTYPE_REC_ID, STATUS)
select :new.code, rec_id, :new.status from materialtype
where code = :new.materialtype_code;
end if;

if (:new.device_id is null) then
insert into material_requests (quantity,material_lookups_rec_id, materialtype_rec_id, timestamp, status)
select :new.quantity, ml.rec_id, mt.rec_id, :new.timestamp, :new.status
from material_lookups ml,
materialtype mt
where ml.code = :new.code
and mt.code = :new.materialtype_code;

else
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;
end if;


end;


T.