PK53452: MSGDSNT376I TIMEOUT WITH USE AND KEEP EXCLUSIVE LOCKS.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• MSGDSNT376I timeout or contention when using "release locks at
  close cursor" CLI attribute and SQL statements with the
  "USE AND KEEP EXCLUSIVE LOCKS" clause.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All Distributed Data Facility (DDF) users    *
  *                 that have accessed a remote cursor on a      *
  *                 DB2 for z/OS server, where the cursor is     *
  *                 declared with the USE AND KEEP EXCLUSIVE     *
  *                 LOCKS lock-clause in the isolation-clause    *
  *                 of the SELECT statement.                     *
  ****************************************************************
  * PROBLEM DESCRIPTION: MSGDSNT376I timeout can occur for a     *
  *                      remote UPDATE request against a table   *
  *                      that had previously been accessed via   *
  *                      a remote cursor where the cursor is     *
  *                      declared with the isolation-clause      *
  *                      lock-clause USE AND KEEP EXCLUSIVE      *
  *                      LOCKS of the SELECT statement.          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  MSGDSNT376I was issued for a plan that was denied a lock.
  The USE AND KEEP EXCLUSIVE LOCKS lock-clause of the
  isolation-clause was specified.  The application, such as a CLI
  application that has specified the SQL_CC_RELEASE value for the
  SQL_ATTR_CLOSE_BEHAVIOR, which drives the DRDA setting to
  release read locks (RRL) when the cursor is closed, namely
  QRYCLSRLS=TRUE.  In this discussion, we will use the generic
  term 'Release Read Locks' or 'RRL' to indicate this application
  option.
  
  The following scenario demonstrates the problem:
  1. Indicate RRL for the application
  2. Declare a Cursor that uses the USE AND KEEP EXCLUSIVE LOCKS
     lock-clause:
     SELECT * FROM T1 FOR READ ONLY
     WITH RS USE AND KEEP EXCLUSIVE LOCKS  <-- keep X locks
  3. Open the Cursor
  4. Fetch from the Cursor
  5. Close the Cursor
  6. Update the same table, T1    <-- deadlock/timeout occurred
                                      because RRL was specified
                                      for the application and
                                      the X locks were kept for
                                      RRL
  

Problem conclusion

• Code was changed in DB2 to not obey the Release lock at close
  cursor time if the USE AND KEEP EXCLUSIVE LOCKS lock-clause
  was specified. This reduces timeouts and deadlocks and in
  addition improves performance since locks are no longer
  transferred from the cursor to the agent when the cursor
  is closed.
  

Temporary fix

Comments

APAR Information

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

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

  UK32289 UK32290

Modules/Macros

• DSNXRBMN DSNXRBMX DSNXRCUB DSNXREOJ DSNXRFF
  DSNXRGBJ DSNXRHJ1 DSNXRHJ2 DSNXRINS DSNXRIWS DSNXROJ1 DSNXRRID
  DSNXRSFJ DSNXRSFN DSNXRSGB DSNXRSJ  DSNXRSTD DSNXRSTP DSNXRTSC
  DSNXRT1J DSNXRT3J DSNXRUID DSNXSBUC DSNXSIND DSNXSING
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK32289

     UP07/12/29 P F712

• R910 PSY UK32290

     UP07/12/29 P F712

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.