PI81933: RETRIEVE THE CATALOG RECORD AND CONCURRENT CREATE INDEX MAY CAUSE DEADLOCKS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Retrieve the catalog record and concurrent CREATE INDEX may
  cause deadlocks
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All DB2 12 for z/OS users of CREATE INDEX.                   *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Deadlock can occur when executing                            *
  * CREATE INDEX and DML Prepares                                *
  * concurrently.                                                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  When executing CREATE INDEX, an S-lock is obtained on the
  SYSTABLESPACE row for the underlying base table space.
  DML Prepares will also attempt to obtain locks on SYSTABLESPACE.
  Users may receive console message DSNT501I, reason code
  00C90088 and resource type 00000304 on SYSTABLESPACE
  when executing CREATE INDEX and DML Prepares concurrently.
  The deadlock occurs because when CREATE INDEX first fetches
  the SYSTABLESPACE row, an S-lock is obtained. Later on in
  CREATE INDEX processing, SYSTABLESPACE row is updated
  and tries to upgrade the S-lock to an X-lock. This may result
  in a deadlock with any other concurrent processes that may
  have already obtained an S-lock on the same SYSTABLESPACE
  row.
  In addition, there is a timing window between CREATE INDEX and
  long prepare of INSERT, where the new row is inserted into the
  table but not into the new index, and might cause DB2 04E ABEND
  with RC00C90206 at location DSNIDM  .DSNIIDIS:5002 on a
  subsequent UPDATE of the table, or other unexpected symptoms.
  

Problem conclusion

• DB2 has been modified so that CREATE INDEX obtains an X-lock on
  SYSTABLESPACE earlier. Deadlocks on SYSTABLESPACE will no
  longer occur when processing CREATE INDEX and DML Prepares
  concurrently.
  Although this solution will alleviate deadlocks between
  CREATE INDEX and DML Prepares, additional timeouts or deadlocks
  may occur when running concurrently with DROP or ALTER INDEX.
  The corresponding APAR on DB2 11 is PI77479.
  ADDITIONAL KEYWORDS:
  SQLCREATE
  SQLINDEX
  RC00C90088
  SQLCODE911
  MSGDSNT501I
  

Temporary fix

Comments

APAR Information

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

  PI77479

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

  UI48334

Modules/Macros

• DSNXICIX
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI48334

     UP17/07/13 P F707

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.