Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2009
    Posts
    31

    Unanswered: Need help with "escalation of locks on table to lock intent "X" locks was successful

    Hi Everyone ,

    I need help with one of my production database that is causing "ADM5502W" issue very frequently during the index rebuild phase, which is captured in the db2diag.log. I checked for LOCK WAIT but there were none .The db configs params are set as below

    MAXLOCKS = 10
    MAXAPPLS = 500
    LOCKLIST = 1024
    LOCK TIME OUT = -1

    Could anyone please suggest me if I need to change any of the above params. This is really urgent and I need to fix this asap. I will appreciate any help which will help me tune this db

    Thanks

    Sam

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    A "W" message is a warning and not necessarily a problem. You are likely to always get this message when you have an index rebuild, and DB2 will run faster when the lock escalation from row to table occurs. So long as no other application is having unacceptable lock waits while trying to access this table when this is happening, then there is no problem.

    I typically set those parms to something like this for an OLTP application, but it probably will not eliminate the warning or the escalation during index rebuild:

    MAXLOCKS = 40
    LOCKLIST = 4096
    LOCK TIME OUT = 30 (-1 means wait forever for a lock to be freed before issuing a -911 RC 68).
    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
    Jena, Germany
    Posts
    2,721
    Lock escalation occurs when a transaction is holding many locks on rows and the lock heap is getting tight, i.e. it can't hold much more locks. At that point, DB2 figures that it is better to grab a lock on the table and release all the row locks. Since each lock requires some memory (about 40-80 bytes), DB2 can potentially free up a lot of memory that way in the lock heap. This process is called lock escalation.

    The advantage is less pressure on the lock heap and, with fewer locks being held, the check whether a row is locked can be done faster too. The disadvantage is that the table lock is on a coarser granularity, meaning that concurrency is reduced - if lock escalation occurs for write-operations, the impact can be dramatic because the whole table is essentially blocked for all other transactions. Another issue is that lock escalation needs some time, which slows down the transaction in who's context the escalation is done. That may not be acceptable for all transactions.

    Thus, you have a few options:
    • increase the size of the lock heap to have more memory available in general
    • use the LOCK TABLE statement in your applications to grab a table lock right away instead of waiting for the lock escalation
    • commit more ofter in your application to release locks earlier and, thus, reduce the pressure on the lock heap
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze View Post
    Thus, you have a few options:
    • increase the size of the lock heap to have more memory available in general
    • use the LOCK TABLE statement in your applications to grab a table lock right away instead of waiting for the lock escalation
    • commit more ofter in your application to release locks earlier and, thus, reduce the pressure on the lock heap
    If the lock escalation is being done during "index rebuild phase" (of some utility like reorg or load) then it is unlikely that he needs to do any of the above things (and in the last two cases, unlikely he can do them).

    The correct option is to learn that warning messages are for information only and should usually be ignored.
    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
  •