|
Problem IC81032
|
Status: Closed |
DDLS ON NICKNAME CAUSE DEADLOCK |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
When you rollback a transaction including DDLs on nickname,
Federation Server will try to flush catalog cache about that
nickname. This flushing wrongly requests lock of FEDServerNm in
X mode. This behavior could cause deadlock and the deadlock can
not be automatically resovled by DB2 because the transaction is
doing rollback. The lock request can also happen when the DDL on
nickname is automatically rollbacked because of error.
When nnstat is running with method 1, Federation Server will
create or drop nickname with name ending with "_DB2II". If error
happens when nnstat create or drop nickname, it will request
that lock too and possibly cause deadlock.
When deadlock happens, you can see below error message reported
in db2diag.log:
2011-12-26-02.44.33.229005-300 I2314012968E474 LEVEL: Error
PID : 11676 TID : 47258607282496PROC :
db2sysc 1
INSTANCE: nyqxt160 NODE : 001 DB : ATHENA
EDUID : 115 EDUNAME: db2dlock (ATHENA) 1
FUNCTION: DB2 UDB, lock manager, sqlpKillIfWaiting, probe:670
DATA #1 : <preformatted>
Cannot kill deadlock victim (with DLpriority 50 tidhdl 123
tentryState 4 holder_hdl 34) because it is doing rollback or
commit.
From the db2pd output, lock contention on FEDServerNm can be
seen as below:
94081 [001-28545] 34 5B160000000000000000000073
FEDServerNm ..S ..X C 312021 db2ts MSDB2
10.172.100.232.15750.1112260741
The stack of hanging agent can be like below one:
semtimedop + 0x000a
sqloWaitEDUWaitPost + 0x0199
_Z8sqlplfndP9sqeBsuEduP14SQLP_LOCK_INFOP8SQLP_LCBP9SQLP_LHSHP15S
QLP_LTRN_CHAINP8SQLP_LRBSA_P8SQLP_AWBbb + 0x112c
_Z7sqlplrqP9sqeBsuEduP14SQLP_LOCK_INFO + 0x0cd9
_Z16sqlqgFlushServerPhPvS_ + 0x00be
_ZN14UnfencedServer20flush_cached_objectsEv + 0x0080
_ZN15UnfencedWrapper20flush_cached_serversEv + 0x007b
_ZN15UnfencedWrapper20flush_cached_objectsEv + 0x0091
_Z16sqlqgFedRollbackP8sqeAgentP12SQLQG_F2PC_Tm + 0x0997
_Z8sqlpxrbkP8sqeAgentP15SQLXA_CALL_INFOPiP9SQLP_GXIDPP11sqlo_xla
tch + 0x07d7
_Z8sqlrkrbkP8sqlrr_cbP15SQLXA_CALL_INFOi + 0x025f
_Z12sqlrrbck_dpsP8sqlrr_cbiiiP15SQLXA_CALL_INFOP9SQLP_GXIDb +
0x0a22
_Z8sqlrrbckP8sqlrr_cbiiiiP15SQLXA_CALL_INFO + 0x0546
_Z21sqlrr_rds_common_postP14db2UCinterfaceiil + 0x1797
_Z14sqlrr_execimmdP14db2UCinterfaceP16db2UCprepareInfo + 0x014c
_Z19sqljs_ddm_excsqlimmP14db2UCinterfaceP13sqljDDMObject +
0x00fa
_Z21sqljsParseRdbAccessedP13sqljsDrdaAsCbP13sqljDDMObjectP14db2U
Cinterface + 0x03d5
_Z10sqljsParseP13sqljsDrdaAsCbP14db2UCinterfaceP8sqeAgentb +
0x035b
_Z19sqljsDrdaAsSPDriverP14db2UCconHandleP17SQLCC_COMHANDLE_TPPhl
+ 0x0278
_Z24sqlerInvokeFencedRoutineP13sqlerFmpParms + 0x0713
_Z18sqlriInvokeInvokerP10sqlri_ufobb + 0x0753
_Z9sqlricallP8sqlrr_cb + 0x0122
_Z15sqlriSectInvokeP8sqlrr_cbP12sqlri_opparm + 0x00eb
_Z29sqlrr_process_execute_requestP8sqlrr_cbi + 0x1948
_Z13sqlrr_executeP14db2UCinterfaceP9UCstpInfo + 0x0115
_Z19sqljs_ddm_excsqlsttP14db2UCinterfaceP13sqljDDMObject +
0x04e7
_Z21sqljsParseRdbAccessedP13sqljsDrdaAsCbP13sqljDDMObjectP14db2U
Cinterface + 0x007d
_Z10sqljsParseP13sqljsDrdaAsCbP14db2UCinterfaceP8sqeAgentb +
0x035b |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* DDLS ON NICKNAME CAUSE DEADLOCK *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fixpack 6. *
**************************************************************** |
| Local Fix: |
Below operations can significantly reduce the chance of hitting
this deadlock:
1) Check and turn off the health indicators for Federation:
db.fed_servers_op_status
db.fed_nicknames_op_status
To turn off the two health indicators, using below SQL
statements:
UPDATE ALERT CONFIGURATION FOR DATABASE ON FEDDB USING
db.fed_servers_op_status SET THRESHOLDSCHECKED NO
UPDATE ALERT CONFIGURATION FOR DATABASE ON FEDDB USING
db.fed_nicknames_op_status SET THRESHOLDSCHECKED NO
2) Turn off the auto_runstats database configuration parameter
by issuing below SQL statement:
UPDATE DB CFG FOR FEDDB USING AUTO_RUNSTATS OFF
3) Execute reverse federated DDL instead of rollback federated
DDL |
| available fix packs: |
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
First fixed in DB2 Version 9.7 Fixpack 6. |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC84333 IT02338 IT02342
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 25.01.2012
07.07.2014
07.07.2014 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP6
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.6
|
 |
| 9.7.0.10
|
|
