SEO

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.

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

Sorry Phil, I forgot the update the second part, it is the same table
in the original rpt.
You can tell from the same lock object name.
Lock Object Name : 86079752
Could this be the senario?
On timestamp 1, application 1 try to update tableA - record 1 by
primary key.
On timestamp 2, application 1 get the NS lock on tableA - record 1;
On timestamp 3, application 2 try to update tableA - record 1 by
primary key.
On timestamp 4, application 2 get the NS lock on tableA - record 1;

On timestamp 12, application 1 is ready for update, need to converting
the NS lock on tableA - record 1 to X lock; But there is application 2
holding the NS lock on it, so application 1 has to wait.
On timestamp 14, application 2 is ready for update, need to converting
the NS lock on tableA - record 1 to X lock; But there is application 1
holding the NS lock on it, so application 1 has to wait.

On timestamp 20, DB2 dead lock checker find the loop, and kick off 2.
If this is the senario, then I dont see any way to avoid the deadlock
on update the same object around same period of time.

Phil Sherman wrote:
> 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 : USER1
> > Table Schema : DB2ADMIN
> > Table Name : TEST
> > 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.
> >