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