DB2 FOR LUW / DB2FORLUW / 970 - DB2

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 
===========================================

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to the latest fix pack.                              * 
****************************************************************

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.

DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem is first fixed in DB2 UDB version 9.7 fix pack 9

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

04.10.2013
23.12.2013
23.12.2013

9.7.FP9