
Find locked rows in a deadlock
Hi Fan Rou (my apologies if I'm addressing you incorrectly here),
Thanks for the reply. Yes, I do have DB2_RR_TO_RS=Yes.
I'm confused on what you're telling me here. Below is my lock
snapshot. Application 719 has 3 Row locks held with MODE=X, and 3 Key
Value locks also with MODE=X. Application 720 is waiting on a lock
held by 719. From the information shown, I can't determine which of
the 3 row locks held by 719 is causing the lock wait on 720.
Thanks.
Steve Toeniskoetter
Application handle = 719
Application ID =
0A5907D5.DC9C.020221202702
Sequence number = 0001
Application name = pmserver
Authorization ID = XXXXXX
Application status = Lock-wait
Status change time = 02-21-2002
15:32:54.264977
Application code page = 819
Locks held = 10
Total wait time (ms) = 1613
Subsection waiting for lock = 0
ID of agent holding lock = 720
Application ID holding lock =
0A5907D5.DC9D.020221202701
Node lock wait occurred on = 0
Lock object type = Row
Lock mode = Exclusive Lock (X)
Lock mode requested = Update Lock (U)
Name of tablespace holding lock = PJRESBUD
Schema of table holding lock = XXXXXX
Name of table holding lock = PS_Z_PROJ_RES_BUD
Lock wait start timestamp = 02-21-2002
15:32:54.264980
Lock is a result of escalation = NO
List Of Locks
Lock Object Name = 2272721903
Node number lock is held at = 0
Object Type = Key Value
Tablespace Name = PJRESBUD
Table Schema = XXXXXX
Table Name = PS_Z_PROJ_RES_BUD
Mode = X
Status = Granted
Lock Escalation = NO
Lock Object Name = 2382848
Node number lock is held at = 0
Object Type = Row
Tablespace Name = PJRESBUD
Table Schema = XXXXXX
Table Name = PS_Z_PROJ_RES_BUD
Mode = X
Status = Granted
Lock Escalation = NO
Lock Object Name = 3676796911
Node number lock is held at = 0
Object Type = Key Value
Tablespace Name = PJRESBUD
Table Schema = XXXXXX
Table Name = PS_Z_PROJ_RES_BUD
Mode = X
Status = Granted
Lock Escalation = NO
Lock Object Name = 2356740
Node number lock is held at = 0
Object Type = Row
Tablespace Name = PJRESBUD
Table Schema = XXXXXX
Table Name = PS_Z_PROJ_RES_BUD
Mode = X
Status = Granted
Lock Escalation = NO
Lock Object Name = 3677769455
Node number lock is held at = 0
Object Type = Key Value
Tablespace Name = PJRESBUD
Table Schema = XXXXXX
Table Name = PS_Z_PROJ_RES_BUD
Mode = X
Status = Granted
Lock Escalation = NO
Lock Object Name = 2356483
Node number lock is held at = 0
Object Type = Row
Tablespace Name = PJRESBUD
Table Schema = XXXXXX
Table Name = PS_Z_PROJ_RES_BUD
Mode = X
Status = Granted
Lock Escalation = NO
Lock Object Name = 4
Node number lock is held at = 0
Object Type = Table
Tablespace Name = PJRESBUD
Table Schema = XXXXXX
Table Name = PS_Z_PROJ_RES_BUD
Mode = IX
Status = Granted
Lock Escalation = NO
Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal V Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO
Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal V Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO
Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal P Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO
Application handle = 720
Application ID =
0A5907D5.DC9D.020221202701
Sequence number = 0001
Application name = pmserver
Authorization ID = XXXXXX
Application status = Lock-wait
Status change time = 02-21-2002
15:32:54.265211
Application code page = 819
Locks held = 18
Total wait time (ms) = 1613
Subsection waiting for lock = 0
ID of agent holding lock = 719
Application ID holding lock =
0A5907D5.DC9C.020221202702
Node lock wait occurred on = 0
Lock object type = Row
Lock mode = Exclusive Lock (X)
Lock mode requested = Update Lock (U)
Name of tablespace holding lock = PJRESBUD
Schema of table holding lock = XXXXXX
Name of table holding lock = PS_Z_PROJ_RES_BUD
Lock wait start timestamp = 02-21-2002
15:32:54.265213
Lock is a result of escalation = NO
.... there are 18 locks here that I'm not listing since they look just
like the locks for application 719
Quote:
> Hi, Steve,
> Lock snapshot does not show the RID of the target row which the
> application want to lock it. Because we can figure out which row your
> application is waiting for.
> For example, from the snapshot like the following:
> Lock mode = Exclusive Lock (X)
> Lock mode requested = Next Key Share (NS)
> One application need a NS lock (actually, my saying is not enough right,
> it involves a latch here, but you can know what I mean. You can find the
> concept of latch from some RDBMS books), but the target object already
> locked by another application with a X lock. The two locks are not
> competible, so the first application have to be in the waiting queue. And
> from the other lock information. I can figure out which row the first
> application want to access.
> BTW, have you set DB2_RR_TO_RS=Yes?
> Regards,
> Fan Ruo Xin
> > Hello,
> > DB2 AIX EE v7.2. This is a Peoplesoft application. I've got two
> > applications that are both doing the same update statement with
> > commits every 100 updates. The update statement is fully qualified on
> > the unique key, and none of the key values are being updated. There
> > is a splitter job that divides the keys into ranges so that the first
> > application does not update any rows that the second application
> > updates, or at least that is how it is supposed to work.
> > A snapshot for locks on the database shows the two applications in a
> > deadlock situation. The first application shows the row locks it is
> > holding, and shows that it is waiting on a row the second application
> > has locked, and the second application shows its locks and that it is
> > waiting on the first. There is no lock escalation going on.
> > My question is, is there some way to see which RID each of the
> > applications is waiting on. I see the RIDs of all the rows that are
> > currently locked by each application, but I can't find the the RID of
> > the target row.
> > Any help would be greatly appreciated.
> > Steve Toeniskoetter
> > American Electric Power