Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005
    Posts
    108

    Unanswered: Interpreting snapshot for locks

    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

  2. #2
    Join Date
    Mar 2005
    Posts
    108
    Can you please advise?

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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.
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Mar 2005
    Posts
    108
    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.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Look at the application snapshot to see what package it was and then decide what to do ...

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •