469,645 Members | 1,971 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,645 developers. It's quick & easy.

X Row locks causing Lock-waits

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
0 3097

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

14 posts views Thread by Ajay | last post: by
2 posts views Thread by rkusenet | last post: by
1 post views Thread by aswinee | last post: by
17 posts views Thread by Dr NoName | last post: by
1 post views Thread by Zri Man | last post: by
3 posts views Thread by DaTurk | last post: by
7 posts views Thread by praveen | last post: by
2 posts views Thread by chsalvia | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.