X Row locks causing Lock-waits

Bruce Pullen

DB2 v7.2 (FP7 - DB2 v7.1.0.68) on AIX 5.2.0.0.

We're seeing unexpected single row (then commit) insert locking
behaviour.

We're seeing Applications that already hold row-level W locks in
lock-wait, waiting to acquire row-level X locks. The lock-waits are
behind applications that have row-level X locks on different rows
(honestly). Both executing and lock-waiting applications have been
granted IX table locks.

The table in question has row-level locking only.

Can someone advise if this is expected, as I thought two applications
could each insert a single row to a table concurrently?

Lock snapshot extract as evidence:

Application handle = 156
Application ID =
0A0A1401.8906.040713113952
Sequence number = 0001
Application name = java
Authorization ID = WEBGFX
Application status = Lock-wait
Status change time = 07-13-2004
13:27:30.153192
Application code page = 1208
Locks held = 8
Total wait time (ms) = 809358

Subsection waiting for lock = 0
ID of agent holding lock = 132
Application ID holding lock =
0A0A1401.B092.040713131053
Node lock wait occurred on = 0
Lock object type = Row
Lock mode = Weak Exclusive (W)
Lock mode requested = Exclusive Lock (X)
Name of tablespace holding lock = SRCH_DATA_SMALL
Schema of table holding lock = GFX
Name of table holding lock = SRCH_RSLT_SET
Lock wait start timestamp = 07-13-2004
13:27:30.153194
Lock is a result of escalation = NO

List Of Locks
Lock Object Name = 345109
Node number lock is held at = 0
Object Type = Row
Tablespace Name = SRCH_DATA_SMALL
Table Schema = GFX
Table Name = SRCH_RSLT_SET
Mode = X
Status = Granted
Lock Escalation = NO

Lock Object Name = 257
Node number lock is held at = 0
Object Type = Table
Tablespace Name = SRCH_DATA_SMALL
Table Schema = GFX
Table Name = SRCH_RSLT_SET
Mode = IX
Status = Granted
Lock Escalation = NO

Application handle = 132
Application ID =
0A0A1401.B092.040713131053
Sequence number = 0001
Application name = java
Authorization ID = WEBGFX
Application status = UOW Executing
Status change time = 07-13-2004
13:38:42.033325
Application code page = 1208
Locks held = 16
Total wait time (ms) = 0

Lock Object Name = 345110
Node number lock is held at = 0
Object Type = Row
Tablespace Name = SRCH_DATA_SMALL
Table Schema = GFX
Table Name = SRCH_RSLT_SET
Mode = W
Status = Granted
Lock Escalation = NO

Lock Object Name = 257
Node number lock is held at = 0
Object Type = Table
Tablespace Name = SRCH_DATA_SMALL
Table Schema = GFX
Table Name = SRCH_RSLT_SET
Mode = IX
Status = Granted
Lock Escalation = NO

Nov 12 '05 #1