Env: DB2 8.2/AIX 5.3

Lock snapshot shows the following information but I am not sure what would have caused this deadlock. Any thought on this?

You need to look EVERYWHERE in the app where Tab1 and Tab2 are both modified. They should be modified in the same order (e.g. Tab1, then Tab2). Somewhere in the application the order is backwards. This is what needs fixing.

Andy

You need to look at the deadlock event monitor output - the monitor should be running by default, unless you dropped or disabled it. The name is DB2DETAILDEADLOCK.

DB2DETAILEDDEADLOCK has been disabled so that is not helping me here.

By the way, how are you saying that it should be modified in same order to avoid this deadlock ? I would appreciate if you can provide more info on this.

In my understanding of Regarding Next Key Share lock this is obtained for rows on the same index page if CS or RS isolation level is used. For example if a table has 500 rows (50 rows per page). If a rows in updated with X lock rest of 49 rows on the same page will have NKS lock. Is this a correct understanding ? Would you pls correct me if I'm wrong.

You are using V8 which is long out of service, and I do not remember how the locking works. But it really does not matter. What is happening is this:

Application A is locking a resource on Tab1
Application B is locking a resource on Tab2
Application A wants to lock the same resource on Tab2 but cannot so it goes to lock wait
Application B want to lock the resource on Tab1 <<-- DB2 detects a deadlock with this.

It does not matter what the lock types are specifically. You need to determine what statements are causing the deadlock. The previously mentioned event monitor will provide that. Then the application need to be changed so that the order is changed. In the example, change Application B to try to access Tab1 first then Tab2.

Andy

Thanks for sharing this. I am enabling deadlock event monitor to find the needle from the deadlock haystack.

Once you have determined the deadlocking statements, sometimes you can prevent deadlocks with better indexes. If one or both statements are doing a tablespace scan, an index that will prevent the scan may avoid the deadly embrace. I emphasize: sometimes.