> 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

> 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

> 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

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