Interpreting snapshot for locks

DBA-Jr · #1 (**permalink**) 05-10-07, 23:29

Greeting All.
I am having a dead lock or timeout problem. Orginally LOCKTIMEOUT = -1. later on, it was chaged to 180. But the error code remained the same - SQLCODE = -911 with reason code of 68 (timeout). The lock contention was between applicaiton handle 635 and 26. They belong to one application user.

My questions:
1. What were actually going on when 635 was converting a S lock to an X lock for object type "Internal P Lock", which was held by 26? Was "Internal P Lock" related to dynamic bind?

2. when LOCKTIMEOUT = -1, why could DB2 report timeout? Doesn't "-1" mean wait for ever?

Appreciate you advice.

The snapshot:

Code:

 
Database path                              = /db1/conv3401/db2inst1/NODE0000/SQL00001/
Input database alias                       = CONV3401
Locks held                                 = 9
Applications currently connected           = 9
Agents currently waiting on locks          = 1
Snapshot timestamp                         = 05-10-2007 09:51:41.250477
Application handle                         = 27
Application ID                             = GA0A0734.G524.0112767708F8
Sequence number                            = 0001
Application name                           = db2jccServlet.Engine
CONNECT Authorization ID                   = SUBVAL
Application status                         = Connect Completed
Status change time                         = Not Collected
Application code page                      = 1208
Locks held                                 = 0
Total wait time (ms)                       = 0
 
Application handle                         = 26
Application ID                             = GA0A0734.G523.0112767708AA
Sequence number                            = 0001
Application name                           = db2jccServlet.Engine
CONNECT Authorization ID                   = SUBVAL
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 1208
Locks held                                 = 2
Total wait time (ms)                       = 0
List Of Locks
 Lock Name                   = 0xA6B2A69FA4A17C7D9176505041
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal P Lock
 Mode                        = S
 Lock Name                   = 0x96A09A989DA09A7D8E8A6C7441
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal P Lock
 Mode                        = S
 
Application handle                         = 22
Application ID                             = GA0A0734.G522.01127676F39B
Sequence number                            = 0001
Application name                           = db2jccServlet.Engine
CONNECT Authorization ID                   = SUBVAL
Application status                         = Connect Completed
Status change time                         = Not Collected
Application code page                      = 1208
Locks held                                 = 0
Total wait time (ms)                       = 0
 
Application handle                         = 635
Application ID                             = GA0A0734.G521.01127676F33D
Sequence number                            = 0001
Application name                           = db2jccServlet.Engine
CONNECT Authorization ID                   = SUBVAL
Application status                         = Lock-wait
Status change time                         = Not Collected
Application code page                      = 1208
Locks held                                 = 5
Total wait time (ms)                       = 34591
  Subsection waiting for lock              = 0
  ID of agent holding lock                 = 26
  Application ID holding lock              = GA0A0734.G523.0112767708AA
  Lock name                                = 0xA6B2A69FA4A17C7D9176505041
  Lock attributes                          = 0x00000000
  Release flags                            = 0x40000000
  Lock object type                         = Internal P Lock
  Lock mode                                = Share Lock (S)
  Lock mode held                           = Share Lock (S)
  Lock mode requested                      = Exclusive Lock (X)
  Lock wait start timestamp                = 05-10-2007 09:51:06.659422
List Of Locks
 Lock Name                   = 0x00000005000009090000000052
 Lock Attributes             = 0x00000020
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 2313
 Object Type                 = Row
 Tablespace Name             = SYSCATSPACE
 Table Schema                = SYSIBM
 Table Name                  = SYSPLAN
 Mode                        = X
 Lock Name                   = 0x0000006300008A0827D43AA043
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal C Lock
 Mode                        = S
 Lock Name                   = 0xA6B2A69FA4A17C7D9176505041
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 2
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal P Lock
 Mode                        = X
 Status                      = Converting
 Current Mode                = S
 Lock Name                   = 0x96A09A989DA09A7D8E8A6C7441
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal P Lock
 Mode                        = S
 Lock Name                   = 0x00000005000000000000000054
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 5
 Object Type                 = Table
 Tablespace Name             = SYSCATSPACE
 Table Schema                = SYSIBM
 Table Name                  = SYSPLAN
 Mode                        = IX
 
Application handle                         = 20
Application ID                             = GA0A0298.E111.070510145210
Sequence number                            = 0009
Application name                           = Monitor.exe
CONNECT Authorization ID                   = DB2INST1
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 1252
Locks held                                 = 0
Total wait time (ms)                       = 0
 
Application handle                         = 16
Application ID                             = GA0A0734.G51C.011276757538
Sequence number                            = 0030
Application name                           = db2jccServlet.Engine
CONNECT Authorization ID                   = ADV32
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 1208
Locks held                                 = 0
Total wait time (ms)                       = 0
 
Application handle                         = 15
Application ID                             = GA0A0734.G518.0112767548AA
Sequence number                            = 0009
Application name                           = db2jccServlet.Engine
CONNECT Authorization ID                   = ADVADM
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 1208
Locks held                                 = 0
Total wait time (ms)                       = 0
 
Application handle                         = 14
Application ID                             = GA0A0734.G517.01127675481E
Sequence number                            = 0011
Application name                           = db2jccServlet.Engine
CONNECT Authorization ID                   = ADVADM
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 1208
Locks held                                 = 2
Total wait time (ms)                       = 0
List Of Locks
 Lock Name                   = 0xA6B2A69FA4A17C7D9176505041
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal P Lock
 Mode                        = S
 Lock Name                   = 0x00030174000000000000000054
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000001
 Lock Count                  = 2
 Hold Count                  = 0
 Lock Object Name            = 372
 Object Type                 = Table
 Tablespace Name             = USERSPACE08K01
 Table Schema                = O_ADVADM
 Table Name                  = R_IN_UNID
 Mode                        = IX
 
Application handle                         = 709
Application ID                             = GA0A0298.CA11.070510143726
Sequence number                            = 0002
Application name                           = QuestCentral.exe
CONNECT Authorization ID                   = DB2INST1
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 1252
Locks held                                 = 0
Total wait time (ms)                       = 0

DBA-Jr · #2 (**permalink**) 05-15-07, 13:44

Can you please advise?

sathyaram_s · #3 (**permalink**) 05-15-07, 18:53

Quote:

1. What were actually going on when 635 was converting a S lock to an X lock for object type "Internal P Lock", which was held by 26? Was "Internal P Lock" related to dynamic bind?

Internal P Lock are package locks. A share lock is taken when a package is being executed. When rebinding (explicit or implict), dropping etc. an exclusive Internal P lock is taken. Therefore, in your case, 26 may be trying a rebind.

Quote:

2. when LOCKTIMEOUT = -1, why could DB2 report timeout? Doesn't "-1" mean wait for ever?

IIRC, because catalog is locked in one of your applications in lockwait scenario, DB2 timesout even if your locktimout parm is set to -1.

HTH
Sathyaram

DBA-Jr · #4 (**permalink**) 05-16-07, 11:13

Thanks you so much Sathyaram for your explanation! I have one more question though. As I know, the user was connected to the database through JDBC and issuing some dynamic SQLs. There is no static application plan/package running. What would cause a rebind? Was it because a system plan was invalid at run time? If so, would a manual rebind help? Thanks.

sathyaram_s · #5 (**permalink**) 05-16-07, 18:38

Look at the application snapshot to see what package it was and then decide what to do ...

Sathyaram

Internet Services

Web Development

Digital Marketing

Consumer Tech