Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    306

    Unanswered: Automatic Locklist/Maxlocks and escalations

    Hey all,

    Has anyone seen lock escalations on systems with STMM and locklist/maxlocks set to Automatic?

    How can this be? Is the HWM for locks so high it's eating all the instance memory?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Lock Escalation is not necessarily bad. If there a SQL or utility that needs to lock all the rows in a table (eventually) it is a lot more efficient to escalate to table level lock than to keep track of each row lock.

    That is one reason I don't like STMM. I would prefer that escalation take place rather than eat up too much memory for locklist. If lock contention results from this, then I can find out the culprits and deal with the underlying problems rather than sweep them under the rug.

    STMM is fools gold.
    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
    Jul 2004
    Posts
    306
    I guess that's my question though, it seems STMM still allows the escalation, but I'm not sure how...

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by meehange View Post
    I guess that's my question though, it seems STMM still allows the escalation, but I'm not sure how...
    Presumably, STMM is smart enough to know that it cannot completely exhaust the system memory, and there is a finite limit as to how high locklist can go on a particular system. At that point (when locklist cannot be increased any more, or STMM determines it should not be increased any more) lock escalation may occur. Your assumption that lock escalation is always bad (or even usually bad) may not be correct. If you are getting unwanted lock escalation (that is actually causing -911's) and locklist is already large, then you probably need to fix the SQL that is causing it, rather than trying to increase locklist indefinitely.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jul 2004
    Posts
    306
    I didn't say I thought lock escalation was bad Although I am getting some exclusive escalations too which might be causing some problems.
    This is a new site so I'm just getting used to the environments and they have some concurrency complaints ...

    I'm just confused how it's happening with STMM on for a system with LOTS of memory...

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by meehange View Post
    I didn't say I thought lock escalation was bad Although I am getting some exclusive escalations too which might be causing some problems.
    This is a new site so I'm just getting used to the environments and they have some concurrency complaints ...

    I'm just confused how it's happening with STMM on for a system with LOTS of memory...
    Some tables have a very large number of rows, and there is no such thing as infinite DB2 memory. Also, STMM does not always work. Check the diagnosis log and the STMM log and see if there are any errors with STMM failing to get requested memory from OS. I have seen this happen before, especially in earlier fixpacks.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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