|
Problem IC68452
|
Status: Closed |
"COPY NO" LOADS IN RECOVERABLE DATABASES RESULTS IN DEADLOCK WITH DROP TABLE COMMAND. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
"COPY NO" Loads in recoverable databases can conflict with DROP
TABLE command in very rare timing scenarios, resulting in
Deadlock. The table being LOADed and the table being dropped
would need to be in the same tablespace for this problem to
occur. Also, the table names may be different.
Deadlock event monitor explains following behavior:
LOAD with "COPY NO":
- Holds lock on SYSIBM.SYSDATAPARTITIONS.
- Waits for SYSIBM.SYSTABLES.
DROP:
- Holds lock on SYSIBM.SYSTABLES.
- Waits for SYSIBM.SYSDATAPARTITIONS.
Deadlock can be seen in db2diag.log as follows :
2009-06-10-04.03.55.995750+120 I8813013E598 LEVEL: Error
PID : 24366 TID : 183228164448 PROC :
db2sysc 0
INSTANCE: XXXXXXXX NODE : 000 DB :
XXXXXXX
APPHDL : 0-2132 APPID:
*LOCAL.XXXXXXXXX.090610022252
AUTHID : XXXXXXXX
EDUID : 40 EDUNAME: db2agent (DBFORCEP) 0
FUNCTION: DB2 UDB, catalog services, sqlrlCatalogScan::fetch,
probe:40
RETCODE : ZRC=0x80100002=-2146435070=SQLP_LDED "Dead lock
detected"
DIA8002C A deadlock has occurred, rolling back
transaction.
DATA #1 : Hex integer, 4 bytes
0x0000000B
2009-06-10-04.03.55.996244+120 I8813612E574 LEVEL: Error
PID : 24366 TID : 183228164448 PROC :
db2sysc 0
INSTANCE: XXXXXXXX NODE : 000 DB : XXXXXX
APPHDL : 0-2132 APPID:
*LOCAL.XXXXXXXXX.090610022252
AUTHID : XXXXXXXX
EDUID : 40 EDUNAME: db2agent (DBFORCEP) 0
FUNCTION: DB2 UDB, database utilities, DIAG_ERROR, probe:0
DATA #1 : String, 139 bytes
LOADID: 40.2009-06-10-04.03.51.114120.0 (2;93)
Failed to get TS Contents , -2146435070, 0x2, Detected in
file:sqluCCatalogInfo.C, Line:3164
This deadlock problem can occur even if the registry variable
DB2_LOAD_COPY_NO_OVERRIDE is used. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users prior to DB2 V97 FP4. *
****************************************************************
* PROBLEM DESCRIPTION: *
* "COPY NO" LOADS IN RECOVERABLE DATABASES RESULTS IN DEADLOCK *
* WITH DROP TABLE COMMAND. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 V97 FP4. *
**************************************************************** |
| Local Fix: |
(i) We should not run "COPY NO" Loads in recoverable databases
and DROP TABLE in parallel.
(ii) Also, this problem does not occur if 'COPY YES' or
'NONRECOVERABLE' is specified explicitly in the LOAD
command. |
| available fix packs: |
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 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 |
Problem is first fixed in DB2 V97 FP4. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 05.05.2010
09.05.2011
09.05.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP4
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.4
|
 |
