As of v9.7 Fix Pack 1, detaching a partition from a data
partitioned table schedules a database manager task to complete
the detach process asynchronously after all remaining statements
referencing the data partitioned table have completed their
execution.
This task may deadlock with concurrently executing DML or DDL
statements being compiled which reference the data partitioned
table. Entries in the db2diag.log may appear as follows:
--------------------------<snip>--------------------------------
2010-08-10-04.53.29.177231+540 I3851231A554 LEVEL: Error
PID : 442600 TID : 19379 PROC : db2sysc
0
INSTANCE: dwadm NODE : 000 DB : LMDW
APPHDL : 0-11168 APPID: *N0.DB2.100809204623
AUTHID : BDUSR
EDUID : 19379 EDUNAME: db2taskp (LMDW) 0
FUNCTION: DB2 UDB, catalog services,
sqlrlCatalogScan::postUpdateCacheHandlin, probe:10
RETCODE : ZRC=0x80100002=-2146435070=SQLP_LDED "Dead lock
detected"
DIA8002C A deadlock has occurred, rolling back
transaction.
2010-08-10-04.53.29.187665+540 I3851786A538 LEVEL: Error
PID : 442600 TID : 19379 PROC : db2sysc
0
INSTANCE: dwadm NODE : 000 DB : LMDW
APPHDL : 0-11168 APPID: *N0.DB2.100809204623
AUTHID : BDUSR
EDUID : 19379 EDUNAME: db2taskp (LMDW) 0
FUNCTION: DB2 UDB, catalog services, sqlrlCatalogScan::update,
probe:80
RETCODE : ZRC=0x80100002=-2146435070=SQLP_LDED "Dead lock
detected"
DIA8002C A deadlock has occurred, rolling back
transaction.
2010-08-10-04.53.29.191663+540 I3852325A542 LEVEL: Error
PID : 442600 TID : 19379 PROC : db2sysc
0
INSTANCE: dwadm NODE : 000 DB : LMDW
APPHDL : 0-11168 APPID: *N0.DB2.100809204623
AUTHID : BDUSR
EDUID : 19379 EDUNAME: db2taskp (LMDW) 0
FUNCTION: DB2 UDB, catalog services,
sqlrlAlterDropCatalogChange, probe:300
RETCODE : ZRC=0x80100002=-2146435070=SQLP_LDED "Dead lock
detected"
DIA8002C A deadlock has occurred, rolling back
transaction.
2010-08-10-04.53.29.195661+540 I3852868A544 LEVEL: Error
PID : 442600 TID : 19379 PROC : db2sysc
0
INSTANCE: dwadm NODE : 000 DB : LMDW
APPHDL : 0-11168 APPID: *N0.DB2.100809204623
AUTHID : BDUSR
EDUID : 19379 EDUNAME: db2taskp (LMDW) 0
FUNCTION: DB2 UDB, catalog services,
sqlrlAlterDetachCatalogChange, probe:100
RETCODE : ZRC=0x80100002=-2146435070=SQLP_LDED "Dead lock
detected"
DIA8002C A deadlock has occurred, rolling back
transaction.
2010-08-10-04.53.29.199664+540 I3853413A729 LEVEL: Error
PID : 442600 TID : 19379 PROC : db2sysc
0
INSTANCE: dwadm NODE : 000 DB : LMDW
APPHDL : 0-11168 APPID: *N0.DB2.100809204623
AUTHID : BDUSR
EDUID : 19379 EDUNAME: db2taskp (LMDW) 0
FUNCTION: DB2 UDB, AIC, apdTaskProcessor, probe:1128
MESSAGE : ZRC=0x80100002=-2146435070=SQLP_LDED "Dead lock
detected"
DIA8002C A deadlock has occurred, rolling back
transaction.
DATA #1 : String, 60 bytes
APD ERROR DURING PROCESSING: poolID/objectID/partitionID !!!
DATA #2 : unsigned integer, 2 bytes
65530
DATA #3 : unsigned integer, 2 bytes
33002
DATA #4 : unsigned integer, 2 bytes
5
2010-08-10-04.53.29.203663+540 I3854143A1013 LEVEL: Warning
PID : 442600 TID : 19379 PROC : db2sysc
0
INSTANCE: dwadm NODE : 000 DB : LMDW
APPHDL : 0-11168 APPID: *N0.DB2.100809204623
AUTHID : BDUSR
EDUID : 19379 EDUNAME: db2taskp (LMDW) 0
FUNCTION: DB2 UDB, AIC, apdTaskProcessorCleanup, probe:194
MESSAGE : ZRC=0x80100002=-2146435070=SQLP_LDED "Dead lock
detected"
DIA8002C A deadlock has occurred, rolling back
transaction.
CALLED : DB2 UDB, AIC, apdTaskProcessor
RETCODE : ZRC=0x82A90066=-2102853530=ABP_SUSPEND_TASK_PRO
"Suspend the task processor"
DATA #1 : String, 28 bytes
Source Table Schema and Name
DATA #2 : String, 8 bytes
LM_DW
DATA #3 : String, 13 bytes
DY_STK_201008
DATA #4 : String, 12 bytes
Partition ID
DATA #5 : unsigned integer, 2 bytes
5
DATA #6 : String, 28 bytes
Target Table Schema and Name
DATA #7 : String, 8 bytes
LM_DW
DATA #8 : String, 19 bytes
DY_STK_201008_PART5
--------------------------</snip>---------------------------
In a deadlock event, the APD process will always be chosen as
the victim, and hence, rescheduled to complete its task at a
later time. If the same process continually encounters
deadocks, then the newly detached target table will not be
accessible. |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* As of v9.7 Fix Pack 1, detaching a partition from a data *
* partitioned table schedules a database manager task to *
* complete *
* the detach process asynchronously after all remaining *
* statements *
* referencing the data partitioned table have completed their *
* execution. *
* *
* This task may deadlock with concurrently executing DML or *
* DDL *
* statements being compiled which reference the data *
* partitioned *
* table. Entries in the db2diag.log may appear as follows: *
* *
* --------------------------<snip>------------------------------
* *
* 2010-08-10-04.53.29.177231+540 I3851231A554 LEVEL: Error *
* PID : 442600 TID : 19379 PROC : db2sysc *
* 0 *
* INSTANCE: dwadm NODE : 000 DB : LMDW *
* APPHDL : 0-11168 APPID: *N0.DB2.100809204623 *
* AUTHID : BDUSR *
* EDUID : 19379 EDUNAME: db2taskp (LMDW) 0 *
* FUNCTION: DB2 UDB, catalog services, *
* sqlrlCatalogScan::postUpdateCacheHandlin, probe:10 *
* RETCODE : ZRC=0x80100002=-2146435070=SQLP_LDED "Dead lock *
* detected" *
* DIA8002C A deadlock has occurred, rolling back *
* transaction. *
* *
* 2010-08-10-04.53.29.187665+540 I3851786A538 LEVEL: Error *
* PID : 442600 TID : 19379 PROC : db2sysc *
* 0 *
* INSTANCE: dwadm NODE : 000 DB : LMDW *
* APPHDL : 0-11168 APPID: *N0.DB2.100809204623 *
* AUTHID : BDUSR *
* EDUID : 19379 EDUNAME: db2taskp (LMDW) 0 *
* FUNCTION: DB2 UDB, catalog services, *
* sqlrlCatalogScan::update, *
* probe:80 *
* RETCODE : ZRC=0x80100002=-2146435070=SQLP_LDED "Dead lock *
* detected" *
* DIA8002C A deadlock has occurred, rolling back *
* transaction. *
* *
* 2010-08-10-04.53.29.191663+540 I3852325A542 LEVEL: Error *
* PID : 442600 TID : 19379 PROC : db2sysc *
* 0 *
* INSTANCE: dwadm NODE : 000 DB : LMDW *
* APPHDL : 0-11168 APPID: *N0.DB2.100809204623 *
* AUTHID : BDUSR *
* EDUID : 19379 EDUNAME: db2taskp (LMDW) 0 *
* FUNCTION: DB2 UDB, catalog services, *
* sqlrlAlterDropCatalogChange, probe:300 *
* RETCODE : ZRC=0x80100002=-2146435070=SQLP_LDED "Dead lock *
* detected" *
* DIA8002C A deadlock has occurred, rolling back *
* transaction. *
* *
* 2010-08-10-04.53.29.195661+540 I3852868A544 LEVEL: Error *
* PID : 442600 TID : 19379 PROC : db2sysc *
* 0 *
* INSTANCE: dwadm NODE : 000 DB : LMDW *
* APPHDL : 0-11168 APPID: *N0.DB2.100809204623 *
* AUTHID : BDUSR *
* EDUID : 19379 EDUNAME: db2taskp (LMDW) 0 *
* FUNCTION: DB2 UDB, catalog services, *
* sqlrlAlterDetachCatalogChange, probe:100 *
* RETCODE : ZRC=0x80100002=-2146435070=SQLP_LDED "Dead lock *
* detected" *
* DIA8002C A deadlock has occurred, rolling back *
* transaction. *
* *
* 2010-08-10-04.53.29.199664+540 I3853413A729 LEVEL: Error *
* PID : 442600 TID : 19379 PROC : db2sysc *
* 0 *
* INSTANCE: dwadm NODE : 000 DB : LMDW *
* APPHDL : 0-11168 APPID: *N0.DB2.100809204623 *
* AUTHID : BDUSR *
* EDUID : 19379 EDUNAME: db2taskp (LMDW) 0 *
* FUNCTION: DB2 UDB, AIC, apdTaskProcessor, probe:1128 *
* MESSAGE : ZRC=0x80100002=-2146435070=SQLP_LDED "Dead lock *
* detected" *
* DIA8002C A deadlock has occurred, rolling back *
* transaction. *
* DATA #1 : String, 60 bytes *
* APD ERROR DURING PROCESSING: poolID/objectID/partitionID !!! *
* DATA #2 : unsigned integer, 2 bytes *
* 65530 *
* DATA #3 : unsigned integer, 2 bytes *
* 33002 *
* DATA #4 : unsigned integer, 2 bytes *
* 5 *
* *
* 2010-08-10-04.53.29.203663+540 I3854143A1013 LEVEL: Warning *
* PID : 442600 TID : 19379 PROC : db2sysc *
* 0 *
* INSTANCE: dwadm NODE : 000 DB : LMDW *
* APPHDL : 0-11168 APPID: *N0.DB2.100809204623 *
* AUTHID : BDUSR *
* EDUID : 19379 EDUNAME: db2taskp (LMDW) 0 *
* FUNCTION: DB2 UDB, AIC, apdTaskProcessorCleanup, probe:194 *
* MESSAGE : ZRC=0x80100002=-2146435070=SQLP_LDED "Dead lock *
* detected" *
* DIA8002C A deadlock has occurred, rolling back *
* transaction. *
* CALLED : DB2 UDB, AIC, apdTaskProcessor *
* RETCODE : ZRC=0x82A90066=-2102853530=ABP_SUSPEND_TASK_PRO *
* "Suspend the task processor" *
* DATA #1 : String, 28 bytes *
* Source Table Schema and Name *
* DATA #2 : String, 8 bytes *
* LM_DW *
* DATA #3 : String, 13 bytes *
* DY_STK_201008 *
* DATA #4 : String, 12 bytes *
* Partition ID *
* DATA #5 : unsigned integer, 2 bytes *
* 5 *
* DATA #6 : String, 28 bytes *
* Target Table Schema and Name *
* DATA #7 : String, 8 bytes *
* LM_DW *
* DATA #8 : String, 19 bytes *
* DY_STK_201008_PART5 *
* *
* --------------------------</snip>--------------------------- *
* *
* In a deadlock event, the APD process will always be chosen *
* as *
* the victim, and hence, rescheduled to complete its task at a *
* later time. If the same process continually encounters *
* deadocks, then the newly detached target table will not be *
* accessible. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fix Pack 4. *
**************************************************************** |
