Repetitive Deadlock occurring on "Instead of" Trigger
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Repetitive Deadlock occurring on "Instead of" Trigger

  1. #1
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78

    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.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    What are the columns in the PK of material_requests?

    Tamil

  3. #3
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width