Results 1 to 12 of 12

Thread: lock escalation

  1. #1
    Join Date
    Mar 2008
    Posts
    136

    Unanswered: lock escalation

    ADM5500W DB2 is performing lock escalation. The total number of locks
    currently held is "1034", and the target number of locks to hold is "517".

    whats meaing of this???

    how i can solve it???

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Increase your locklist size to 4096 pages. If you still are getting these errors frequently, keep on doubling the locklist size (8192 next time).

    db2 connect to <database-name>;
    db2 update db cfg using locklist 4096;
    db2 update db cfg using maxlocks 60; (this is optional and determines what percent of the locklist can be used by any one application)

    When DB2 runs out of space for holding locks in the locklist, the it escalates locks to the table level.
    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
    A little bit of more detailed background: Locks are data structures that indicate which part of the system (row, page, table, tablespace, database, ...) is protected in some way. Since DB2 has to remember this information, it has to store it in memory. Ideally, you would like to have very fine-grained locking in order to allow maximum concurrency, i.e. blocking the whole database all the time an application accesses a small part of it doesn't make much sense. So if you have an application that deals with a lot of data, it potentially has to have many locks. Those locks accumulate and need space in memory. The lock list is this in-memory space.

    It would be bad if DB2 cannot handle the situation where the free space in the lock list is nearly exhausted because - overall - too many locks did accumulate. So DB2 does a "lock escalation". That means, many row-level locks held by one transaction on a table are escalated to a single table-level lock, for example. Instead of having 1000s or millions of locks (per row), there is just one, relieving the pressure on the lock list.

    Of course, such an escalation has a few drawbacks: it reduces concurrency, it may not be possible (due to locks of other SQL sessions), and it impacts performance negatively. The performance impact is due to the fact that the 1000s or millions of locks have to be released, which just takes time.
    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
    Locks are data structures that indicate which part of the system (row, page, table, tablespace, database, ...)
    These are DB2 for z/OS lock levels. For DB2 Linux, UNIX, Windows, the lock levels are row or table.
    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
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You're sure? DB2 LUW must also have mechanisms to block access to tablespaces and databases.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    You're sure? DB2 LUW must also have mechanisms to block access to tablespaces and databases.
    Block access, yes. But that is not same as a lock or lock escalation.

    In DB2 LUW lock escalation only happens from ROW to TABLE. You can explicitly lock a table, but that is not lock escalation. You can also effectively block access to tablespaces and databases, but that is not lock escalation either.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Yes, you're right. There is no escalation on tablespace and database levels. I was just saying that locks [i]exists[i] on those levels. Sorry for the confusion.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    Yes, you're right. There is no escalation on tablespace and database levels. I was just saying that locks [i]exists[i] on those levels. Sorry for the confusion.
    They are no locks at the tablespace or database level. There are things like the QUIESCE command, or setting a tablespace in some status other than normal (not available, copy pending, etc), but there are no locks at those levels.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Pawan Kumar
    ADM5500W DB2 is performing lock escalation. The total number of locks currently held is "1034", and the target number of locks to hold is "517".
    If your SELECT query is accessing a substantial part of the table (say, more than 20%),
    consider issueing a "'LOCK TABLE my_table IN SHARE MODE" statement before the SELECT, or consider using "WITH UR".

    If on the other hand it's an INSERT/UPDATE/DELETE statement, consider issueing a "'LOCK TABLE my_table IN EXCLUSIVE MODE" statement before that statement.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Marcus_A
    They are no locks at the tablespace or database level. There are things like the QUIESCE command, or setting a tablespace in some status other than normal (not available, copy pending, etc), but there are no locks at those levels.
    Marcus, what would you call this blocking mechanism (via tablespace/database status) if not "lock"? Sure, it is not maintained in the LOCKLIST, but it is used to control access to the respective object and provides locking functionality.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Marcus_A
    They are no locks at the tablespace or database level. There are things like the QUIESCE command, or setting a tablespace in some status other than normal (not available, copy pending, etc), but there are no locks at those levels.
    Marcus, what would you call this blocking mechanism (via tablespace/database status) if not "lock"? Sure, it is not maintained in the LOCKLIST, but it is used to control access to the respective object and provides locking functionality. I guess we are just talking on two different levels: concepts vs. specific implementation in DB2.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would call it QUIESCE. Anyway, it has nothing to do with lock escalation.
    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
  •