Lock mode converting from NS to X, when will this happen?

[shenanwei]

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.

[Phil Sherman]

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
sh*******@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.

[shenanwei]

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
sh*******@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.