IBM Support

IC81032: DDLS ON NICKNAME CAUSE DEADLOCK

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error 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
    

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
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * DDLS ON NICKNAME CAUSE DEADLOCK                              *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 9.7 Fixpack 6.                        *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 Version 9.7 Fixpack 6.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC81032

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-01-25

  • Closed date

    2014-07-07

  • Last modified date

    2014-07-07

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

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

    IC84333 IT02338 IT02342

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC81032

Modified date: 07 July 2014