IT28601: LOAD REPLACE CAN DEADLOCK WITH APPLICATIONS ON SYSIBM.SYSTABLES

Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• LOAD REPLACE can encounter DEADLOCKS since the upgrade from 10.5
  to 11.1. This is induced on column organized tables (BLU)
  processing and does not impact row organized tables.
  
  Event monitoring would show the LOAD job having "Lock Type:
  CATALOG"  while attempting to acquire a ROW lock usually held by
  applications on SYSIBM.SYSTABLES during prepare phase of the
  statement. This can be confirmed by setting up lock event
  monitor and using db2evmonfmt to format the deadlock events
  capture.
  
  Per example:
  
  -------------------------------------------------------
  Event ID               : 73
  Event Type             : DEADLOCK
  Event Timestamp        : 2018-12-17-12.47.01.228055
  Partition of detection : 0
  -------------------------------------------------------
  
  Deadlock Graph
  --------------
  Total number of deadlock participants : 2
  Participant that was rolled back      : 1
  Type of deadlock                      : local
  
  Participant     Participant     Deadlock Member Application
  Handle
  Requesting Lock Holding Lock
  --------------- --------------- ---------------
  ------------------
  1               2               0               028475
  2               1               0               028312
  
  
  Participant No 2 requesting lock
  ----------------------------------
  Lock Name            : 0x000100000A0003129248D380C3
  Lock wait start time : 2018-12-17-12.46.39.976047
  Lock wait end time   : 2018-12-17-12.47.01.228055
  Lock Type            : CATALOG
  Lock Specifics       : loadingID:00000000,loadingBytes:0A000312
  9248D380
  Lock Attributes      : 00000000
  Lock mode requested  : Exclusive
  Lock mode held       : Exclusive
  Lock Count           : 0
  Lock Hold Count      : 0
  Lock rrIID           : 0
  Lock Status          : Waiting
  Lock release flags   : 00000000
  Tablespace TID       :
  Tablespace Name      :
  Table FID            :
  Table Schema         :
  Table Name           :
  
  Participant No 1 requesting lock
  ----------------------------------
  Lock Name            : 0x00000098000000000045000552
  Lock wait start time : 2018-12-17-12.46.34.623845
  Lock wait end time   : 2018-12-17-12.47.01.228055
  Lock Type            : ROW
  Lock Specifics       : ROWID=5,DATA_PARTITION_ID=0,PAGEID=69
  Lock Attributes      : 00000000
  Lock mode requested  : Share (CS/RS)
  Lock mode held       : Exclusive
  Lock Count           : 0
  Lock Hold Count      : 0
  Lock rrIID           : 0
  Lock Status          : Waiting
  Lock release flags   : 00000000
  Tablespace TID       : 0
  Tablespace Name      : SYSCATSPACE
  Table FID            : 152
  Table Schema         : SYSIBM
  Table Name           : SYSTABLES
  
  
  Attributes            Requester                        Requester
  
  --------------------- ------------------------------
  ------------------------------
  Participant No        2                                1
  
  Application Handle    028312                           028475
  
  Application ID        *LOCAL.db2inst1.181218023046
  xxx.xx.x.xx.xxxxx.181217174634
  Application Name      db2bp                            db2bp
  
  Authentication ID     USER1                          USER2
  
  Requesting AgentID    72042                            186381
  
  Coordinating AgentID  72042                            186381
  
  Agent Status          Data Fast Load                   Compiling
  
  Application Action    Performing Load                  No action
  
  Lock timeout value    7200                             7200
  
  Lock wait value       0                                0
  
  Workload ID           1                                1
  
  Workload Name         SYSDEFAULTUSERWORKLOAD
  SYSDEFAULTUSERWORKLOAD
  Service subclass ID   13                               13
  
  Service superclass    SYSDEFAULTUSERCLASS
  SYSDEFAULTUSERCLASS
  Service subclass      SYSDEFAULTSUBCLASS
  SYSDEFAULTSUBCLASS
  Current Request       Fetch                            Prepare
  
  TEntry state          2                                1
  
  TEntry flags1         00020020                         00000000
  
  TEntry flags2         00000208                         00000200
  
  Lock escalation       no                               no
  
  Client userid                                          xxxxx,
  xxxx
  Client wrkstnname
  xxx.xx.x.xxx
  Client applname       CLP
  /dbawork/xxxxx/xxxx/TEMP//content/package[@name= xx - Log
  Client acctng                                          xxxxxxxx
  Utility ID
  

Local fix

• To circumvent the issue, increase CATALOGCACHE_SZ
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All using LOAD                                               *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Db2 version 11.1 m4 fp6                           *
  ****************************************************************
  

Problem conclusion

• First fixed in Db2 version 11.1 m4 fp6
  

Temporary fix

Comments

APAR Information

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

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

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RB10 PSY

     UP