Results 1 to 4 of 4

Thread: lock wait issue

  1. #1
    Join Date
    Feb 2002
    Location
    Hamilton
    Posts
    150

    Unanswered: lock wait issue

    We have a database that is being accessed by multiple applications and we notice that the applications get hosed up because the number of lock waits start to increase.

    Memory Pool Type = Lock Manager Heap
    Locks held currently = 0
    Lock waits = 23
    Lock list memory in use (Bytes) = 10080
    Lock escalations = 4
    Lock Timeouts = 0
    Memory Pool Type = Lock Manager Heap

    db2 list applications show detail

    CONNECT Auth Id Application Name Appl. Application Id Seq# Number of Coordinating DB Coordinator Status Status Change Time DB Name DB Path
    Handle Agents partition number pid/thread
    ------------------------------ -------------------- ---------- ------------------------------ ---- ---------- ---------------- --------------- ------------------------------ -------------------------- -------- --------------------
    RBCPB1 db2jcc_application 184 GAE5463F.AD02.070323154538 0009 1 0 194424 UOW Waiting Not Collected RBCCQARI /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/rbccqari/rbcpb1/NODE0000/SQL00001/
    RBCPB1 db2jcc_application 170 GAE54640.O3E2.070323154147 0044 1 0 44970 UOW Waiting Not Collected RBCCQARI /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/rbccqari/rbcpb1/NODE0000/SQL00001/
    RBCPB1 db2jcc_application 154 GAE5463F.A2CB.070323153223 0106 1 0 219232 UOW Waiting Not Collected RBCCQARI /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/rbccqari/rbcpb1/NODE0000/SQL00001/
    RBCPB1 db2jcc_application 200 GAE54640.P0C0.070323155900 0002 1 0 102760 UOW Waiting Not Collected PLDB /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/pldb/rbcpb1/NODE0000/SQL00001/
    RBCPB1 db2jcc_application 199 GAE5463F.B2BE.070323155325 0058 1 0 45790 UOW Executing Not Collected PLDB /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/pldb/rbcpb1/NODE0000/SQL00001/
    RBCPB1 db2jcc_application 182 GAE5463F.AC4F.070323154431 0022 1 0 249034 UOW Waiting Not Collected PLDB /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/pldb/rbcpb1/NODE0000/SQL00001/
    RBCPB1 db2jcc_application 173 GAE54640.O483.070323154236 0010 1 0 167720 UOW Waiting Not Collected PLDB /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/pldb/rbcpb1/NODE0000/SQL00001/
    RBCPB1 db2jcc_application 153 GAE54640.EBA3.070323153313 0105 1 0 127560 UOW Waiting Not Collected PLDB /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/pldb/rbcpb1/NODE0000/SQL00001/
    SYMARIQ ariqstart 61 GAE54805.P11D.0EB4F2215959 0349 1 0 32848 UOW Waiting Not Collected PLDB /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/pldb/rbcpb1/NODE0000/SQL00001/
    SYMARIQ ariqstart 60 GAE54805.P11A.0EB4F2215957 1377 1 0 297500 UOW Waiting Not Collected PLDB /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/pldb/rbcpb1/NODE0000/SQL00001/
    SYMARIQ ariqstart 59 GAE54805.P118.0EB4F2215955 0488 1 0 304730 UOW Waiting Not Collected PLDB /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/pldb/rbcpb1/NODE0000/SQL00001/
    SYMARIQ db2bp 58 GAE54805.P111.06E2B2215955 0001 1 0 344210 Connect Completed Not Collected PLDB /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/pldb/rbcpb1/NODE0000/SQL00001/

    $

    What could we do to improve this performance?

    DB2 8.1 on AIX 5.2

    thanks
    Mark

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In the db config, change the following parms:

    db2 update db cfg using LOCKLIST 4096
    db2 update db cfg using MAXLOCKS 40

    These take effect after all connections from the database are gone.

    If this does not work, then your applications may not be commiting frequently enough.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2007
    Location
    Pune, India
    Posts
    27
    Eldho Mathew
    IBM DB2 UDB LUW Software Engineer

  4. #4
    Join Date
    Jan 2007
    Location
    Pune, India
    Posts
    27
    Lock Timeouts = 0 wht is your LOCKTIMEOUT value?

    Also set "LOCK_TIMEOUT" to be 10 (seconds) rather than -1.
    Eldho Mathew
    IBM DB2 UDB LUW Software Engineer

Posting Permissions

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