Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2010
    Posts
    19

    Unanswered: deadlock between 2 tables during update operation

    Hi,

    We are using DB2 UDB V8 and are experiencing some strange deadlocks between tables during the update operation.

    we are having one "order" table and other "xorder" table and they share foreign key relationship.

    we are using entity beans for doing the CRUP operations on these tables
    and for quite some time we have noticed that update on these tables is resulting into deadlock.

    How can we avoid deadlock scenarios in such cases?

    Thanks
    Ravi

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    First you need to make sure that it is a deadlock that you are experiencing. The error message you get shares the same message with a lock timeout. Only the return code tells you what occurred, 2 = deadlock and 68 = timeout.

    The best way to prevent deadlocks is to always update the table in the same order. Either update the ORDER table first or the XORDER table first, but always do it the same way.

    Andy

  3. #3
    Join Date
    Apr 2010
    Posts
    19

    deadlock between 2 tables during update operation

    Hi,
    Yes they are resulting into deadlocks only we have crossed checked the
    errors.
    How it will be handled in concurrent user scenario.Will it not result into
    deadlock?

    Thanks
    Ravi

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If both applicaitons lock the tables in the same sequence, then there may be a slight lock-wait, but not a deadlock (deadly embrace). A deadlock occurs when 2 applications (or more) already hold some locks, and try to acquire other locks that another application is holding, so it can never be resolved by simply waiting and therefor DB2 will pick a victum and cancel it to allow the other to proceed.

    You can set up (or look at existing) deadlock event monitor to determine which SQL statements are deadlocking (if you have a detailed deadlock monitor set up). I don't recall if V8 has detailed deadlock event monitors, but I think so. You will need to run a program to format the deadlock event files into a report (see the Command Reference manual for details).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Apr 2010
    Posts
    19

    deadlock between 2 tables during update operation

    what is the solution to fix this issue.
    Deadlock happening on 2 update statements.
    Is it that orders and xorders table should be updated in the same order in the entire application.
    From the deadlock monitor we found that deadlock is happening on update of these 2 tables only and when I am checking the application ids they are different for both the tables

    Application ID : GA141A3D.FDDE.1A3D53132446
    Application ID : GA141A3D.F372.19AA63130725
    Table space is also same for the tables.

    Thanks
    Ravi

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes, you should always update the tables in the same order. This should fix the deadlocks.

    Andy

Posting Permissions

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