DB2 - Problem description
Problem IT37906 | Status: Closed |
DEADLATCH causing APPLICATION HUNG DOING INSERT/SELECT ON TABLE WITH XML COLUMNS | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
An application running inserts/updates and selects on a table with XML columns and compression might hang and not able to be forced off. Root cause is deadlatch. Possible scenarios: Scenario (A): open transaction insert .. select.. db2pd -latches will show a deadlatch on SQLO_LT_SQLB_BPD__bpdLatch_SX S (i.e a two threads waiting on each other) Database Member 0 -- Active -- Up 0 days 00:06:43 -- Date 2014-11-21-12.16.25.721195 Latches: Address Holder Waiter Filename LOC LatchType HoldCount 0x0780000001588440 1029 0 ../include/sqle_workload_disp.h 1389 SQLO_LT_sqeWLDispatcher__m_tunerLatch 1 0x0700000047C2F468 13391 0 /view/db2_v105fp3_aix64_n140813/vbs/engn/sqb/inc/sqlbslat.h 649 SQLO_LT_SQLB_BPD__bpdLatch_SX S 0x0700000053E99768 13391 0 /view/db2_v105fp3_aix64_n140813/vbs/engn/sqb/inc/sqlbslat.h 649 SQLO_LT_SQLB_BPD__bpdLatch_SX S 0x0700000047D5BC28 13391 13134 /view/db2_v105fp3_aix64_n140813/vbs/engn/sqb/inc/sqlbslat.h 649 SQLO_LT_SQLB_BPD__bpdLatch_SX S 0x0700000047D5BC28 13391 13648 /view/db2_v105fp3_aix64_n140813/vbs/engn/sqb/inc/sqlbslat.h 649 SQLO_LT_SQLB_BPD__bpdLatch_SX S 0x0700000047D5BC28 13391 14162 /view/db2_v105fp3_aix64_n140813/vbs/engn/sqb/inc/sqlbslat.h 649 SQLO_LT_SQLB_BPD__bpdLatch_SX S 0x0700000047D5BC28 13391 14419 /view/db2_v105fp3_aix64_n140813/vbs/engn/sqb/inc/sqlbslat.h 649 SQLO_LT_SQLB_BPD__bpdLatch_SX S 0x0700000047D5BC28 13391 14676 /view/db2_v105fp3_aix64_n140813/vbs/engn/sqb/inc/sqlbslat.h 649 SQLO_LT_SQLB_BPD__bpdLatch_SX S 0x0700000047D5BC28 13391 14933 /view/db2_v105fp3_aix64_n140813/vbs/engn/sqb/inc/sqlbslat.h 649 SQLO_LT_SQLB_BPD__bpdLatch_SX S 0x0700000047D5BC28 13391 15190 /view/db2_v105fp3_aix64_n140813/vbs/engn/sqb/inc/sqlbslat.h 649 SQLO_LT_SQLB_BPD__bpdLatch_SX S >>>>>>>>>> 0x0700000047D5BC28 13391 15447 /view/db2_v105fp3_aix64_n140813/vbs/engn/sqb/inc/sqlbslat.h 649 SQLO_LT_SQLB_BPD__bpdLatch_SX S > 0x0700000047D35DE8 15447 13391 /view/db2_v105fp3_aix64_n140813/vbs/engn/sqb/inc/sqlbslat.h 649 SQLO_LT_SQLB_BPD__bpdLatch_SX S | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * XML tables * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Apply v11.1 GA (Fixpack 0) or newer fixpack * **************************************************************** | |
Local Fix: | |
Scenario (A): Do not use compression on Tables with XML columns or when insert - update XML tables , place a commit before doing a select on the table on the same transaction: open transaction insert .. commit open transaciton. select.. intead of : open transaction insert .. select... An alternative to above for Scenario (A) or Scenario (B) is to enable the registry variable below and restart the DB2 instance. This registry variable changes latching mode used by XML components to fix this issue. In future releases of DB2 this will be enabled by default and there is no need to set this registry variable. db2set DB2_PMODEL_SETTINGS=REVERT_SA_BEHAVIOUR:1 db2stop db2start | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * XML tables * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Apply v11.1 GA (Fixpack 0) or newer fixpack * **************************************************************** | |
Comment | |
First fixed in v11.1 GA (Fixpack 0) | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 06.08.2021 06.08.2021 13.08.2021 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |