Deadlocks occur when two applications are unable to complete because
each is holding a lock that prevents the other from continuing it's own
updates. Your deadlock report doesn't show all of the locks - it only
shows the ones that each application is waiting on. Note that the
participants are waiting on different tables.
This problem can be avoided by having all applications that update a set
of n tables always perform the updates on the tables in the same order.
The conversion to X lock should occur when that application is ready to
do the update. UPDATE statements use the NS locks during the retrieval
portion of the update and convert to X at update time. This minimizes
the X locks when an UPDATE needs to scan many rows to satisfy the
predicates which limit the updates to a few rows.
Phil Sherman
shenanwei@gmail.com wrote:
> DB2 V8.2 on AIX, type II index is created.
> I see this from deadlock event monitor.
>
> 5) Deadlocked Connection ...
> Participant no.: 2
> Lock wait start time: 09/18/2006 23:04:09.911774
> .....
> Deadlocked Statement:
> Type : Dynamic
> Operation: Execute
> Section : 384
> Creator : NULLID
> Package : SYSLH202
> Cursor : SQL_CURLH202C384
> Cursor was blocking: FALSE
> Text : update db2admin.test set ... where key = ?
> List of Locks:
>
> Lock Name : 0x00060003052179080000000052
> Lock Attributes : 0x00000000
> Release Flags : 0x40000003
> Lock Count : 1
> Hold Count : 0
> Lock Object Name : 86079752
> Object Type : Row
> Tablespace Name : USER1
> Table Schema : DB2ADMIN
> Table Name : TEST
> Mode : X - Exclusive
> Status : Converting
> Current Mode : NS - Share (and Next Key Share)
>
> 7) Deadlocked Connection ...
> Participant no.: 1
> Lock wait start time: 09/10/2006 23:04:09.914971
> .....
> Deadlocked Statement:
> Type : Dynamic
> Operation: Execute
> Section : 384
> Creator : NULLID
> Package : SYSLH202
> Cursor : SQL_CURLH202C384
> Cursor was blocking: FALSE
> Text : update db2admin.test set ... where key = ?
> .....
> Lock Name : 0x000600030568B7070000000052
> Lock Attributes : 0x00000000
> Release Flags : 0x40000003
> Lock Count : 1
> Hold Count : 0
> Lock Object Name : 90748679
> Object Type : Row
> Tablespace Name : S04K_EMP
> Table Schema : EZK100C
> Table Name : EMPLOYEE_CONTRACT
> Mode : X - Exclusive
> Status : Converting
> Current Mode : NS - Share (and Next Key Share)
> .....
> Could any guru tell me what the database is trying to do?
> Looks like 2 application is trying to update the same records.
>