IBM Support

IC96464: A TRANSACTION COULD HOLD DATA PARTITION LOCKS EVEN AFTER COMMIT/ROLLBACK

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as fixed if next.

Error description

  • A transaction could still hold data partition locks even after
    a Unit Of Work (UOW) is terminated by a COMMIT statement or
    ROLLBACK statement. It prevents other applications which need to
    acquire the locks.
    The locks will not be released until the current connection is
    terminated.
    
    The problem happens only if the maximum number of "NON table
    locks" is greater than 0, which is the case for large locklists
    or may be controlled by DB2 registry
    variable DB2_MAX_NON_TABLE_LOCKS. Please refer to DB2
    information center for more details about
    DB2_MAX_NON_TABLE_LOCKS.
    
    Even if the maximum number of "NON table locks" is greater than
    0, the problem happens in very rare situations.
    Here is a scenario that could hit the problem through
    serial operations in a single connection:
      1. An IX mode data partition lock was acquired because of some
    DML statement, for example a row was inserted into data
    partition named "part01".
      2. A COMMIT or ROLLBACK was executed, which converted the data
    partition lock to NON mode and cached it in lock list.
      3. Another DML statement was executed, which reused the data
    partition lock cached in lock list and converted it from NON
    mode to IS mode.
         One example of such operations: inserting a row into
    another data partition named "part02", which encountered
    SQL0803N because of key duplication found in the previous data
    partition "part01".
      4. A COMMIT or ROLLBACK was executed, then the transaction
    held the IS mode data partition lock until the connection is
    terminated.
    Please note: above scenario does not always hit the problem. It
    only hits the problem in certain situations, dependent on the
    locks that are acquired in the unit of work and the order in
    which they are acquired.
    
    To identify the problem, please collect following data and
    contact DB2 support team:
      db2pd -db <dbname> -lock showlocks -trans -appl
      db2pd -db <dbname> -fmtlock
    
    Here is a sample 'db2pd -lock showlocks' output which indicates
    the transaction was still holding a data partition lock, even
    after COMMIT/ROLLBACK:
    ===========================================
    Locks:
    Address            TranHdl    Lockname                   Type
    Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
    0x0700000040440DC0 2          FFFA8002000200000000000054
    Partition  .IS  G   2          1   0          0x00 0x40000000
    TbspaceID -6    TableID -32766 PartitionID 2
    ===========================================
    

Local fix

  • 1) To avoid the problem, please disable NON table lock by
    "db2set DB2_MAX_NON_TABLE_LOCKS=0".
    2) To release the persistent data partition locks once the
    problem happens, please terminate current connection and
    establish a new one.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users of version 9.5 on Linux, Unix and Windows          *
    * platforms.                                                   *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * See SYSROUTE APARs to see where this APAR is addressed       *
    ****************************************************************
    

Problem conclusion

Temporary fix

  • 1) To avoid the problem, please disable NON table lock by
    "db2set DB2_MAX_NON_TABLE_LOCKS=0".
    2) To release the persistent data partition locks once the
    problem happens, please terminate current connection and
    establish a new one.
    

Comments

APAR Information

  • APAR number

    IC96464

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    950

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2013-09-27

  • Closed date

    2017-05-08

  • Last modified date

    2017-05-08

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IC96646 IC97483 IC97487

Fix information

Applicable component levels

  • R950 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
08 May 2017