DB2 FOR LUW / DB2FORLUW / A10 - DB2

Operations performing table scans using write locks might 
deadlock with REORG TABLE RECLAIM EXTENTS ALLOW WRITE ACCESS. 
Examples of operations that might perform a table scan while 
using write locks include: 
- SELECT with the FOR UPDATE CLAUSE 
- DELETE statements 
- UPDATE statements 
 
Deadlock might happen if REORG is started (e.g due to AUTO_REORG 
= ON) when TBSCAN is already in progress. REORG TABLE with the 
RECLAIM EXTENTS option on an MDC table will scans the table, 
acquires S-lock on all MDC blocks which are empty and applicable 
for truncation. In the last phase of REORG Z-lock on the table 
is required to truncate those blocks. In this scenario in 
deadlock event monitor we will see REORG waiting for Z-lock to 
be acquired (converted to super exclusive): 
 
Participant No 1 requesting lock 
---------------------------------- 
Lock Name            : 0x00911727000000000000000054 
Lock wait start time : 2014-12-09-13.31.29.319061 
Lock wait end time   : 2014-12-09-13.31.40.754218 
Lock Type            : TABLE 
Lock Specifics       : 
Lock Attributes      : 10202101 
Lock mode requested  : Super Exclusive 
Lock mode held       : Intent Exclusive 
Lock Count           : 1 
Lock Hold Count      : 0 
Lock rrIID           : 0 
Lock Status          : Converting 
Lock release flags   : 40000000 
Tablespace TID       : 2 
Tablespace Name      : USERSPACE1 
Table FID            : 9 
Table Schema         : DB2INST1 
Table Name           : EMPMDC 
 
 
and a query requesting IX-lock on one of the blocks which REORG 
already holds in shared mode: 
 
Participant No 2 requesting lock 
---------------------------------- 
Lock Name            : 0x00911727000000004AD0000062 
Lock wait start time : 2014-12-09-13.31.31.345977 
Lock wait end time   : 2014-12-09-13.31.40.754218 
Lock Type            : MDC_BLOCK 
Lock Specifics       : DATA_PARTITION_ID=0,PAGEID=19152 
Lock Attributes      : 00400000 
Lock mode requested  : Intent Exclusive 
Lock mode held       : Share 
Lock Count           : 0 
Lock Hold Count      : 0 
Lock rrIID           : 0 
Lock Status          : Waiting 
Lock release flags   : 00000000 
Tablespace TID       : 2 
Tablespace Name      : USERSPACE1 
Table FID            : 9 
Table Schema         : DB2INST1 
Table Name           : EMPMDC 
 
For scenario like this it is not necessary for TBSCAN to acquire 
IX-lock on an empty block. 
This is specific to REORG TABLE and REORG INDEXES is not 
affected.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 10.1 Fix Pack 5 or higher.            * 
****************************************************************

Retry the statement that was rolled back due to the deadlock.

Fixed in DB2 version 10.5 Fix Pack 5.

not known / see Local fix

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

12.01.2015
27.07.2015
27.07.2015