Find locked rows in a deadlock 
Author Message
 Find locked rows in a deadlock

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



Tue, 10 Aug 2004 07:31:11 GMT
 Find locked rows in a deadlock

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

Quote:

> 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



Wed, 11 Aug 2004 13:24:38 GMT
 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



Sat, 14 Aug 2004 02:35:32 GMT
 Find locked rows in a deadlock
I am sorry, Steve!
Like you said, I can't figure out which particular row cause the other application wait. I
can only narrow down it to three rows. I know you can do that too.

I think event monitor will give you more information. You know, I am so lazy each time I
just asked my customer to mail me the snapshot. It looks like it is best if we can get
wait list /wait chain from the snapshot.

Another thing is I never met "Oject type" is "Key Value". I did some test one month ago. I
wonder why I didn't see this from my snapshot? I viewed the manual, didn't find the
information for this.

Best Regards,
Fan Ruo Xin

Quote:

> 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



Sat, 14 Aug 2004 10:29:28 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. row locks and updates causing deadlocks

2. No DeadLocks ...Lock file locks

3. Find PK value of locked row.

4. No Deadlocks...Lock file locks

5. Where can i find LOCKED rows in SQL 7.0

6. how to find out page/row lock

7. How to find and remove exclusive lock on table or row

8. Deadlock in Index (where index is B+ tree) Locking with Intention Locks

9. Page lock to simulate row level lock

10. How to Find Rows x to Row x+50


 
Powered by phpBB® Forum Software