DB2 FOR LUW / DB2FORLUW / 970 - DB2

A deadlock may occur when running multiple SQL PL compiled 
compound statements or PL/SQL anonymous blocks concurrently. 
 
For example, 
PL/SQL anonymous block: 
begin 
  StoredPreocedure(1); 
end; 
/ 
 
SQL/PL anonymous block: 
begin 
  call StoredPreocedure(1); 
end

**************************************************************** 
* USERS AFFECTED:                                              * 
* All.                                                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* Abstract: DEADLOCK MAY OCCUR WHEN RUNNING MULTIPLE           * 
* COMPILEDCOMPOUND STATEMENTS CONCURRENTLY.Description:A       * 
* deadlock may occur when running multiple SQL PL              * 
* compiledcompound statements or PL/SQL anonymous blocks       * 
* concurrently.For example,PL/SQL anonymous                    * 
* block:beginStoredPreocedure(1);end;/SQL/PL anonymous         * 
* block:begincall StoredPreocedure(1);endSince the issue is    * 
* timing dependent, the affected statementmay succeed if you   * 
* run it again.                                                * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Update to version 9.7 fix pack 2 or later.                   * 
****************************************************************

Since the issue is timing dependent, the affected statement may 
succeed if you run it again. 
 
There are other workarounds which will avoid the deadlock: 
 
If the anonymous block or compound statement contains a single 
call and no other statements, you can use a CALL statement 
instead.  For example, instead of 
 
begin 
  StoredPreocedure(1); 
end; 
/ 
 
you could run the call directly: 
 
call StoredPreocedure(1); 
 
In the case where the anonymous block contains additional 
statements,  converting the anonymous block to an SQL procedure, 
then calling the procedure will avoid the deadlock scenario.

DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 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 9a 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 10 for Linux, UNIX, and Windows
 

This problem is first fixed in version 9.7 fix pack 2.

Since the issue is timing dependent, the affected statement may 
 
 
succeed if you run it again.

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

28.02.2010
25.05.2010
25.05.2010

9.7.FP2