|
Problem IC82268
|
Status: Closed |
DB2 MIGHT ENCOUNTER 'DEADLOCK' ERROR WHEN PERFORMING DROP INDEX AND ALTER TABLE..DETACH PARTITION STATEMENTS SIMULTANEOUSLY. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| 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.
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 |
| 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. *
**************************************************************** |
| Local Fix: |
Perform the two statements in sequence. |
| available fix packs: |
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 |
First fixed in DB2 Version 9.7 fix pack 6. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 27.03.2012
05.06.2012
05.06.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP6
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.6
|
 |
