DB2 - L

Expand all | Collapse all

lock escalation and increasing lock limits

  • 1.  lock escalation and increasing lock limits

    Posted 11 days ago
    So, we get a lot of lock escalation messages (apps not committing enough ----no surprise!)
    But now one of my managers wants to "get rid of" the lock escalations by increasing user lock and tables lock limits. According to my manager "all the locks being taken and then thrown away with the escalation is contributing to MIPS usage".
    I feel this is the wrong path to take as the applications will just obtain more locks.........
    Any thoughts or advice?
    thanks
    Bill


    ------------------------------
    williamgiannelliMe
    ------------------------------


  • 2.  RE: lock escalation and increasing lock limits

    Posted 10 days ago
    Well ...

    If I am not wrong, CPU cost goes with each individual lock managed. Since your manager does not seem to be concerned about concurrency, that application might as well jump to lock escalation much sooner, and that would save some MIPS usage. So, manager's vote is for lowering lock limits. Increasing them would only mean (a lot?) more row/page locks to handle before relief.

    Please note, none of that is my advise anyway. Applications should manage this kind of s**t. Plus, managing individual locks is "the" cost of running concurrent applications - not an extra cost. Lock escalation is just an anomaly to be corrected (usually on the application side).

    I would hope this helps, but most certainly it won't...


    ------------------------------
    Jaime Fernandez
    ------------------------------



  • 3.  RE: lock escalation and increasing lock limits

    Posted 10 days ago
    Why not let the application code issue a: LOCK TABLE t1 IN EXCLUSIVE MODE. Minimum number of locks taken, lots of CPU saved :)


    ------------------------------
    Jørn Thyssen
    Rocket Software
    2021 IBM Champion
    ------------------------------



  • 4.  RE: lock escalation and increasing lock limits

    Posted 10 days ago
    Edited by Mark Wieczorkowski 10 days ago
    The objects that are getting Escalation messages...are any of those objects getting deadlocks/timeouts (-911, 913) as a result? Or are the broader locks causing contention with any other processing? 

    If not...then the Lock Escalation really isn't a problem.

    Yes, taking all of those locks and then releasing them for escalation is a waste of resources...better if the application either pre-locked the table, as J0rn suggested, or reduced their ISO level. Either would reduce costs. But those are application fixes.

    You could also see if you can get away with changing LOCKSIZE for the tablespaces without causing concurrency issues. If you're doing ROW-level locking and you can get away with PAGE (sounds like you can, if the escalations aren't causing timeouts), maybe look into that. Of course, application needs to approve as well.

    You know what will INCREASE MIPS costs? Bumping the LOCKMAX for the tablespace from 1000 to 10000...because then instead of taking and releasing 1000 locks and taking a 1001st, you'll be taking and releasing up to 10000 locks. Each lock costs the same...the whole point of lock escalation is to eliminate the locking and unlocking and reduce the cost thereof.

    Thus, your manager's line of reasoning doesn't really make sense. Your manager must think that locks that aren't escalated aren't released. If that were true, you'd have some angry customers.

    You also need to be aware of NUMLKUS if you start increasing LOCKMAXes. If you increase LOCKMAX enough that you start hitting NUMLKUS, your applications will start throwing -904s...again, angry customers.

    Increasing LOCKMAX makes sense if escalation is causing concurrency issues, and if there's no other readily available fix. It's not a particularly good fix for, "these messages are bothering me".

    -Mark

    ------------------------------
    MarkWieczorkowski...
    ------------------------------



  • 5.  RE: lock escalation and increasing lock limits

    Posted 10 days ago
    We also have a # of applications that escalate.  It doesn't seem to be problematic.  But as we can we are asking if this escalation is during their batch cycle?  If so, and they are already escalating to a higher level lock, Share or Exclusive, during batch, we have suggested that the application include a LOCK TABLE statement in their code.  It has worked successfully in the applications that we have had make the coding change.   ​

    ------------------------------
    MarkMadsenDiscover
    ------------------------------



  • 6.  RE: lock escalation and increasing lock limits

    Posted 10 days ago
    Edited by Charles Brown 10 days ago
    @Jaime Fernandez       your language, kindly sir 

    ------------------------------
    CharlesBrownBMI
    ------------------------------



  • 7.  RE: lock escalation and increasing lock limits

    Posted 10 days ago

    ... debating on MIPS usage over memory usage. MIPS will always win the debate. That being said,  I think your manager's method of circumventing the lock issue is less costly and of course  a viable and timely approach than fixing the application 

     



    ------------------------------
    CharlesBrownBMI
    ------------------------------