I am developing an application and can't get passed the deadlock exceptions that I can reliably create with a multi-threaded test. I am running DB 7.2.0 on WinXP.

I know there's been a lot of discussion about how NS locks and type-1 indexes can cause deadlocks and here's a great description of the problem: http://publib.boulder.ibm.com/infoce...n/c0009699.htm.

I've tried the DB2_RR_TO_RS solution and it doesn't work for me and I'm not sure I'm having the same problem. My application opens a transaction for writing, executes an INSERT and (only if it fails) executes a SELECT using the same primary key value used in the INSERT. Then the transaction is committed (or rolled back if the insert had failed).

The summary of what my test executes and what locks are held is below (with the application id and lock object names). The data being inserted and queried by these threads is all exactly the same and the row already exists for the given data (at least for the indexed data):

Executing INSERT... (Thread3) LOCAL.DB2.060214020932
Executing INSERT... (Thread2) LOCAL.DB2.060214020947
INSERT done: false (Thread3)
Executing SELECT... (Thread3)
Executing INSERT... (Thread4) LOCAL.DB2.060214020948
Executing INSERT... (Thread8) LOCAL.DB2.060214020949
Executing INSERT... (Thread9) LOCAL.DB2.060214020950

LOCAL.DB2.060214020932 Holds
15179780 NS (Row)
15179781 X (Row)
237459371 X (Key Value)

LOCAL.DB2.060214020947 Holds
15179782 X (Row)

LOCAL.DB2.060214020947 Wants
237459371 X (Key Value)

LOCAL.DB2.060214020932 Wants
15179782 X (Row)

All the threads above failed with deadlock exceptions.

As you can see 932 (Thread 3) has lock that 947 (Thread 2) wants and 947 has a lock that 932 wants and thus the deadlock. I'm not much of a DBA so I don't understand the difference here between a "Row" lock and a "Key Value" lock. I also don't understand how Thread 2 was able to lock anything. When Thread 3 executes the insert, it should lock the only row in the table. When Thread 2 executes its insert, it should block waiting for the lock but not only does it block, but its given an X lock on something that Thread 3 later wants. I don't get that. It's obvious that a deadlock can occur if you've got two transactions that operate on two or more pieces of data that are shared between the two, but in this case, there's only one piece of data. A single key value being inserted (attempted) into the table.

I've tried everything I can think of including changing the isolation level of the queries. I was able to get around the problem by removing the index and replacing it with a unique constraint but that's not really a solution I want.

Here's a fuller lock snapshot but I've chopped out stuff I don't think is necessary. I can provide the whole thing as well as a statement and transaction log.

Application handle = 56
Application ID = *LOCAL.DB2.060214020932
Sequence number = 0001
Application name = java.exe
Authorization ID = DB2USER
Application status = Lock-wait
Status change time = Not Collected
Application code page = 1252
Locks held = 6
Total wait time (ms) = Not Collected

List Of Locks
Lock Object Name = 15179780
Node number lock is held at = 0
Object Type = Row
Tablespace Name = USERSPACE1
Table Schema = DB2USER
Table Name = ACE_LOCKS
Mode = NS
Status = Granted
Lock Escalation = NO

Lock Object Name = 237459371
Node number lock is held at = 0
Object Type = Key Value
Tablespace Name = USERSPACE1
Table Schema = DB2USER
Table Name = ACE_LOCKS
Mode = X
Status = Granted
Lock Escalation = NO

Lock Object Name = 15179781
Node number lock is held at = 0
Object Type = Row
Tablespace Name = USERSPACE1
Table Schema = DB2USER
Table Name = ACE_LOCKS
Mode = X
Status = Granted
Lock Escalation = NO


Application handle = 57
Application ID = *LOCAL.DB2.060214020947
Sequence number = 0001
Application name = java.exe
Authorization ID = DB2USER
Application status = Lock-wait
Status change time = Not Collected
Application code page = 1252
Locks held = 4
Total wait time (ms) = Not Collected

List Of Locks
Lock Object Name = 15179782
Node number lock is held at = 0
Object Type = Row
Tablespace Name = USERSPACE1
Table Schema = DB2USER
Table Name = ACE_LOCKS
Mode = X
Status = Granted
Lock Escalation = NO

Lock Object Name = 3339
Node number lock is held at = 0
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = DB2USER
Table Name = ACE_LOCKS
Mode = IX
Status = Granted
Lock Escalation = NO

Oops, a little typo. When 932 wants lock 15179782, its requested it for NS mode:

60) Deadlocked Connection ...
Appl Id: *LOCAL.DB2.060214020947
Appl Seq number: 0001
Appl Id of connection holding the lock: *LOCAL.DB2.060214020932
Seq. no. of connection holding the lock: 0001
Lock wait start time: 02-13-2006 20:09:47.157637
Requesting lock as part of escalation: FALSE
Deadlock detection time: 02-13-2006 20:09:54.994691
Table of lock waited on : ACE_LOCKS
Schema of lock waited on : DB2USER
Tablespace of lock waited on : USERSPACE1
Type of lock: Keyvalue
Mode of lock: X
Mode application requested on lock: X
Node lock occured on: 0
Lock object name: 237459371
Application Handle: 57

61) Deadlocked Connection ...
Appl Id: *LOCAL.DB2.060214020932
Appl Seq number: 0001
Appl Id of connection holding the lock: *LOCAL.DB2.060214020947
Seq. no. of connection holding the lock: 0001
Lock wait start time: 02-13-2006 20:09:47.168766
Requesting lock as part of escalation: FALSE
Deadlock detection time: 02-13-2006 20:09:54.994691
Table of lock waited on : ACE_LOCKS
Schema of lock waited on : DB2USER
Tablespace of lock waited on : USERSPACE1
Type of lock: Row
Mode of lock: X
Mode application requested on lock: NS
Node lock occured on: 0
Lock object name: 15179782
Application Handle: 56