Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010
    Location
    The Netherlands
    Posts
    20

    Question Unanswered: Deadlock parallel trigger

    Can someone explain me the following situation:

    We have a trigger on a table after insert
    Code:
    CREATE TRIGGER INS_VER AFTER INSERT ON VERIFICATION
    REFERENCING NEW AS N_ROW FOR EACH ROW MODE DB2SQL 
    WHEN ( NOT EXISTS ( SELECT 1 FROM HIGHEST_VERIFICATION WHERE ID_VERIFICATION = N_ROW.ID ) )
    BEGIN ATOMIC 
    INSERT INTO HIGHEST_VERIFICATION SELECT nextval FOR HIGHEST_VERIFICATION_SEQ AS id, N_ROW.ID, N_ROW.STATUS FROM VERIFICATION s WHERE s.id = N_ROW.ID_VERIFICATION;
    END
    Column ID in table HIGHEST_VERIFICATION is defined as primary key and there 's no other index on the table.

    While two parallel processes insert a record in table VERIFICATION a deadlock occurs.

    After creating an index on column ID_VERFICATION in table HIGHEST_VERIFICATION the deadlock doesn't occur anymore.

    Why does the deadlock occur without the index?

    Thanks in advance, Tinie

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    probably ..
    because no index is available - reading is different - locking is different....
    when idx exists : read on index and directly find data = different locking
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Nov 2010
    Location
    The Netherlands
    Posts
    20
    You're right, it's what i figured out myself also.

    But why is there a locking problem if the records don't even exist at the moment in the table.
    It would be great if someone would guide me to some IBM documentation where this locking mechanism is explained.

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    all info is explained in infocenter or pdf guides
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Tags for this Thread

Posting Permissions

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