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

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

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

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).

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

Yes, you should always update the tables in the same order. This should fix the deadlocks.

Andy