|
Problem IC76234
|
Status: Closed |
TWO DROP PROCEDURE STATEMENTS ON DIFFERENT BUT SIMILAR PROCEDURE NAME MAY DEADLOCK |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Two DROP PROCEDURE statements on different but similar procedure
name may deadlock. Deadlock can happen between locks acquired
for the SYSIBM.SYSPLANDEP and SYSIBM.SYSDEPENDENCIES catalog
tables. In other cases, deadlock can happen between different
locks acquired for the SYSIBM.SYSDEPENDENCIES catalog table.
User can find these associated locks and tables involved in the
deadlock from the deadlock event monitor outputs:
In scenario 1:
Participant No 2 requesting lock
-------------------------------------
Lock Name : 0x0000220035002B000000000052
Lock wait start time : 2011-02-11-11.31.26.716862
Lock wait end time : 2011-02-11-11.31.36.318150
Lock Type : ROW
Lock Specifics : ROWID=53,DATA_PARTITION_ID=0,PAGEID=43
Lock Attributes : 00000000
Lock mode requested : Exclusive
Lock mode held : Update
Lock Count : 1
Lock Hold Count : 0
Lock rrIID : 0
Lock Status : Converting
Lock release flags : 40000000
Tablespace TID : 0
Tablespace Name : SYSCATSPACE
Table FID : 34
Table Schema : SYSIBM
Table Name : SYSDEPENDENCIES
Participant No 1 requesting lock
-------------------------------------
Lock Name : 0x00000E00950024000000000052
Lock wait start time : 2011-02-11-11.31.26.704689
Lock wait end time : 2011-02-11-11.31.36.318150
Lock Type : ROW
Lock Specifics : ROWID=149,DATA_PARTITION_ID=0,PAGEID=36
Lock Attributes : 00000010
Lock mode requested : Share
Lock mode held : Exclusive
Lock Count : 1
Lock Hold Count : 0
Lock rrIID : 1
Lock Status : Waiting
Lock release flags : 40000000
Tablespace TID : 0
Tablespace Name : SYSCATSPACE
Table FID : 14
Table Schema : SYSIBM
Table Name : SYSPLANDEP
In scenario 2:
Participant No 2 requesting lock
-------------------------------------
Lock Name : 0x000022007F002A000000000052
Lock wait start time : 2011-02-11-10.12.35.996778
Lock wait end time : 2011-02-11-10.12.43.116030
Lock Type : ROW
Lock Specifics : ROWID=127,DATA_PARTITION_ID=0,PAGEID=42
Lock Attributes : 00000010
Lock mode requested : Update
Lock mode held : Exclusive
Lock Count : 1
Lock Hold Count : 0
Lock rrIID : 1
Lock Status : Waiting
Lock release flags : 40000000
Tablespace TID : 0
Tablespace Name : SYSCATSPACE
Table FID : 34
Table Schema : SYSIBM
Table Name : SYSDEPENDENCIES
Participant No 1 requesting lock
-------------------------------------
Lock Name : 0x0000220080002A000000000052
Lock wait start time : 2011-02-11-10.12.35.980359
Lock wait end time : 2011-02-11-10.12.43.116030
Lock Type : ROW
Lock Specifics : ROWID=128,DATA_PARTITION_ID=0,PAGEID=42
Lock Attributes : 00000010
Lock mode requested : Update
Lock mode held : Exclusive
Lock Count : 1
Lock Hold Count : 0
Lock rrIID : 2
Lock Status : Waiting
Lock release flags : 40000000
Tablespace TID : 0
Tablespace Name : SYSCATSPACE
Table FID : 34
Table Schema : SYSIBM
Table Name : SYSDEPENDENCIES |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* DROP PROCEDURE may deadlock with DROP PROCEDURE with *
* different but similar procedure name *
****************************************************************
* PROBLEM DESCRIPTION: *
* DROP PROCEDURE may deadlock with DROP PROCEDURE with *
* different but similar procedure name Deadlock can happen *
* between locks acquired for the SYSIBM.SYSPLANDEP and *
* SYSIBM.SYSDEPENDENCIES catalog tables. In other cases, *
* deadlock can happen between different locks acquired for the *
* SYSIBM.SYSDEPENDENCIES catalog table. User can find these *
* associated locks and tables involved in the deadlock from *
* the deadlock event monitor outputs: Participant No 2 *
* requesting lock *
* ---------------------------------- *
* Lock Name : 0x0000220035002B000000000052 *
* Lock wait start time : 2011-02-11-11.31.26.716862 *
* Lock wait end time : 2011-02-11-11.31.36.318150 *
* Lock Type : ROW *
* Lock Specifics : ROWID=53,DATA_PARTITION_ID=0,PAGEID=43 *
* Lock Attributes : 00000000 *
* Lock mode requested : Exclusive *
* Lock mode held : Update *
* Lock Count : 1 *
* Lock Hold Count : 0 *
* Lock rrIID : 0 *
* Lock Status : Converting *
* Lock release flags : 40000000 *
* Tablespace TID : 0 *
* Tablespace Name : SYSCATSPACE *
* Table FID : 34 *
* Table Schema : SYSIBM *
* Table Name : SYSDEPENDENCIES *
* *
* Participant No 1 requesting lock *
* ---------------------------------- *
* Lock Name : 0x00000E00950024000000000052 *
* Lock wait start time : 2011-02-11-11.31.26.704689 *
* Lock wait end time : 2011-02-11-11.31.36.318150 *
* Lock Type : ROW *
* Lock Specifics : *
* ROWID=149,DATA_PARTITION_ID=0,PAGEID=36 *
* Lock Attributes : 00000010 *
* Lock mode requested : Share *
* Lock mode held : Exclusive *
* Lock Count : 1 *
* Lock Hold Count : 0 *
* Lock rrIID : 1 *
* Lock Status : Waiting *
* Lock release flags : 40000000 *
* Tablespace TID : 0 *
* Tablespace Name : SYSCATSPACE *
* Table FID : 14 *
* Table Schema : SYSIBM *
* Table Name : SYSPLANDEP *
* ------------------------------------------------------------ *
* ------------------------------------------------------------ *
* ------------------------------------------------------------ *
* ---------- Participant No 2 requesting lock *
* ---------------------------------- *
* Lock Name : 0x000022007F002A000000000052 *
* Lock wait start time : 2011-02-11-10.12.35.996778 *
* Lock wait end time : 2011-02-11-10.12.43.116030 *
* Lock Type : ROW *
* Lock Specifics : *
* ROWID=127,DATA_PARTITION_ID=0,PAGEID=42 *
* Lock Attributes : 00000010 *
* Lock mode requested : Update *
* Lock mode held : Exclusive *
* Lock Count : 1 *
* Lock Hold Count : 0 *
* Lock rrIID : 1 *
* Lock Status : Waiting *
* Lock release flags : 40000000 *
* Tablespace TID : 0 *
* Tablespace Name : SYSCATSPACE *
* Table FID : 34 *
* Table Schema : SYSIBM *
* Table Name : SYSDEPENDENCIES *
* *
* Participant No 1 requesting lock *
* ---------------------------------- *
* Lock Name : 0x0000220080002A000000000052 *
* Lock wait start time : 2011-02-11-10.12.35.980359 *
* Lock wait end time : 2011-02-11-10.12.43.116030 *
* Lock Type : ROW *
* Lock Specifics : *
* ROWID=128,DATA_PARTITION_ID=0,PAGEID=42 *
* Lock Attributes : 00000010 *
* Lock mode requested : Update *
* Lock mode held : Exclusive *
* Lock Count : 1 *
* Lock Hold Count : 0 *
* Lock rrIID : 2 *
* Lock Status : Waiting *
* Lock release flags : 40000000 *
* Tablespace TID : 0 *
* Tablespace Name : SYSCATSPACE *
* Table FID : 34 *
* Table Schema : SYSIBM *
* Table Name : SYSDEPENDENCIES Thanks, Katy *
****************************************************************
* RECOMMENDATION: *
* First fixed in DB2 v9.7 fixpak 5 *
**************************************************************** |
| Local Fix: |
Run the DROP PROCEDURE statements sequentially or only perform
concurrent DROP on two procedures with very different naming. |
| available fix packs: |
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 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
|
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 06.05.2011
02.01.2012
02.01.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
