|
Problem IT38284
|
Status: Closed |
DEADLATCH causing APPLICATION HUNG DOING INSERT/SELECT ON TABLE WITH XML COLUMNS
|
| product: |
DB2 FOR LUW / DB2FORLUW / B50 - 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.
There are two 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: *
* Concurrent XML workloads *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Will be fixed in next release of DB2. *
* DB2_PMODEL_SETTINGS=REVERT_SA_BEHAVIOUR:1 *
* will be replaced with more meaningful registry variable name *
* DB2_XML_LATCH_REVERT=NO (default value is to use new *
* latching *
* mode). *
**************************************************************** |
| 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.
db2set DB2_PMODEL_SETTINGS=REVERT_SA_BEHAVIOUR:1
db2stop
db2start
To append value, separate each variable with semi-colon.
For example, if DB2_PMODEL_SETTINGS="MAX_BACKGROUND_SYSAPPS:300,
then append using:
db2set
DB2_PMODEL_SETTINGS="MAX_BACKGROUND_SYSAPPS:300;REVERT_SA_BEHAVI
OUR:1"
NOTE: In next release of DB2,
DB2_PMODEL_SETTINGS=REVERT_SA_BEHAVIOUR:1
will be replaced with more meaningful registry variable name
DB2_XML_LATCH_REVERT=NO whose default value is to use new
latching
mode and does not need to be set. |
| Solution |
|
| Workaround |
****************************************************************
* USERS AFFECTED: *
* Concurrent XML workloads *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Will be fixed in next release of DB2. *
* DB2_PMODEL_SETTINGS=REVERT_SA_BEHAVIOUR:1 *
* will be replaced with more meaningful registry variable name *
* DB2_XML_LATCH_REVERT=NO (default value is to use new *
* latching *
* mode). *
**************************************************************** |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 07.09.2021
07.09.2021
31.05.2022 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
