IBM Support

IC82268: DB2 MIGHT ENCOUNTER 'DEADLOCK' ERROR WHEN PERFORMING DROP INDEX AND ALTER TABLE..DETACH PARTITION STATEMENTS SIMULTANEOUSLY.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When concurrently performing DROP INDEX and ALTER TABLE...DETACH
    PARTITION statements affecting the same table, the two
    statements may lead to a deadlock among the system catalog
    tables. The target index for DROP INDEX is defined for a table
    that is the same as the target table of the ALTER TABLE
    statement.
    
    To identify the problem, you need to use DB2 event monitor to
    collect detailed information for deadlocks. When the deadlock
    error occurs, in the event monitor output you will see the
    following entries:
    =============================================
    
      Deadlock ID:   1
      Participant no.: 2
      Participant no. holding the lock: 1
      Appl Id: *LOCAL.svtdbm9.110226130946
      Appl Seq number: 00041
      Tpmon Client Applname: CLP drop.clp
      Appl Id of connection holding the lock:
    *LOCAL.svtdbm9.110226130942
      Seq. no. of connection holding the lock: 00001
      Lock wait start time: 02/26/2011 08:09:30.481822
      Lock Name       : 0x0000000500000000003F000052
      Lock Attributes : 0x00000000
      Release Flags   : 0x40000000
      Lock Count      : 1
      Hold Count      : 0
      Current Mode    : U   - Update
      Deadlock detection time: 02/26/2011 08:09:40.166573
      Table of lock waited on      : SYSTABLES
      Schema of lock waited on     : SYSIBM
      Data partition id for table  : 0
      Tablespace of lock waited on : SYSCATSPACE
      Type of lock: Row
      Mode of lock: NS  - Share (CS/RS)
      Mode application requested on lock: X   - Exclusive
    
      Isolation level         : Cursor Stability
      Stmt first use time     : 02/26/2011 08:09:30.476267
      Stmt last use time      : 02/26/2011 08:09:30.476267
      Statement text          : DROP INDEX "DLTEST_T40_IX1"
    
    
      Deadlock ID:   1
      Participant no.: 1
      Participant no. holding the lock: 2
      Appl Id: *LOCAL.svtdbm9.110226130942
      Appl Seq number: 00007
      Appl Id of connection holding the lock:
    *LOCAL.svtdbm9.110226130946
      Seq. no. of connection holding the lock: 00001
      Lock wait start time: 02/26/2011 08:09:30.513920
      Lock Name       : 0x0000000500000000003F000052
      Lock Attributes : 0x00000000
      Release Flags   : 0x40000000
      Lock Count      : 2
      Hold Count      : 0
      Current Mode    : NS  - Share (CS/RS)
      Deadlock detection time: 02/26/2011 08:09:40.167378
      Table of lock waited on      : SYSTABLES
      Schema of lock waited on     : SYSIBM
      Data partition id for table  : 0
      Tablespace of lock waited on : SYSCATSPACE
      Type of lock: Row
      Mode of lock: U   - Update
      Mode application requested on lock: X   - Exclusive
    
      Isolation level         : Cursor Stability
      Stmt first use time     : 02/26/2011 08:09:30.400240
      Stmt last use time      : 02/26/2011 08:09:30.400240
      Statement text          : alter table dltest_t40 detach
    partition PART2 into DLTEST_T40_D
    

Local fix

  • Perform the two statements in sequence.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users running DB2 Version 9.7 on Linux, Unix and Windows *
    * performing DROP INDEX and ALTER TABLE..DETACH PARTITION      *
    * concurrently.                                                *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * When concurrently performing DROP INDEX and ALTER            *
    * TABLE...DETACH                                               *
    * PARTITION statements affecting the same table, the two       *
    * statements may lead to a deadlock among the system catalog   *
    * tables. The target index for DROP INDEX is defined for a     *
    * table                                                        *
    * that is the same as the target table of the ALTER TABLE      *
    * statement.                                                   *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Follow the recommendation specified in "Local Fix" or update *
    * to DB2 Version 9.7 fix pack 6.                               *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 Version 9.7 fix pack 6.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC82268

  • 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-03-27

  • Closed date

    2012-06-05

  • Last modified date

    2012-06-05

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

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
05 June 2012