Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Location
    Columbus, OH
    Posts
    55

    Unanswered: DB2 Locklist and unclosed connections

    Hello DB2 friends!

    I am fighting a problem on a DB2 LUW 9.5 db running on AIX 6.1 The application (Content Manager) is struggling with Lock List Full errors. It seems as though unclosed connections are greatly contributing to the problem. There are applications opening connetions, but not closing them. Around 2784 connections, the lock list starts throwing errors. I increased the locklist from 1000 to 2000 earlier in the year to ease the pain. I know that I can further increase, but I am interested to know why it seems open connections are chewing up the locklist. I've attached a db2pd I snapped when the lock list was full. Does anyone know how I can get some knowledge on why this might be occurring?
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2004
    Posts
    306
    If the application is holding thousands of locks for long durations unnecessarily and not releasing them via commit (or rollback) then it's most likely a problem with the application. If it's an out of the box app, then it's probably just a setting rather than a bug.

    The most you can really do is detail evidence of the problem or keep hiding the symptoms (with locklist increases).

    To do the former, gather info on the locking behavior (especially apps holding locks for hours or days) and show it to the CM guys.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If applications are not closing transactions, or not committing often, then that can cause locks to be held too long. Closing an application connection is not necessary, but closing transactions and committing often is highly desirable.

    A locklist value of 2000 is not all that large (8 MB). Try increasing it to 10000 (40 MB). But at the same time set maxlocks to 30 to make sure that one application does not take over the entire locklist.

    However, just because locklist becomes full, does not mean it is a problem unless you getting a lot of SQL0911N errors (locktimeout or deadlock). But you will only get a locktimeout (reason code 68) if your lockwait is set to something other than -1 (which means wait forever unless a deadlock occurs).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Aug 2011
    Location
    Columbus, OH
    Posts
    55
    Thanks for the suggestions! I really appreciate the help and support.

Posting Permissions

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