|
Problem IT31070
|
Status: Closed |
EXPLICT REBIND AND ATTACH/DETACH/ADD PARTITION MAY DEADLOCK
|
| product: |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 |
| Problem description: |
DEADLOCK was occurred explicit rebind and attach/detach/add
partition concurrently.
Program A
ALTER TABLE TABLEA DETACH PARTITION P061 INTO P61;
ALTER TABLE TABLEA ADD PARTITION P061 STARTING FROM ('023')
ENDING AT ('028')
rebind package spserver;
Program B
ALTER TABLE TABLEB DETACH PARTITION P061 INTO P61;
ALTER TABLE TABLEB ADD PARTITION P061 STARTING FROM ('023')
ENDING AT ('028')
rebind package spserver;
Executed Program A and B concurrently, then DEADLOCK was
occurred.
Here is a sample of DEADLCK report.
-------------------------------------------------------
Event ID : 10
Event Type : DEADLOCK
Event Timestamp : 2019-11-09-02.00.59.568645
Partition of detection : 0
-------------------------------------------------------
Deadlock Graph
--------------
Total number of deadlock participants : 2
Participant that was rolled back : 1
Type of deadlock : local
Participant Participant Deadlock Member Application
Handle
Requesting Lock Holding Lock
--------------- --------------- ---------------
------------------
1 2 0 045836
2 1 0 045680
Participant No 1 requesting lock
----------------------------------
Lock Name : 0x000100000A00030BCB1B7C20C3
Lock wait start time : 2019-11-09-02.00.55.139364
Lock wait end time : 2019-11-09-02.00.59.568645
Lock Type : CATALOG
Lock Specifics : loadingID:00000000,loadingBytes:0A00030B
CB1B7C20
Lock Attributes : 00000000
Lock mode requested : Exclusive
Lock mode held : Exclusive
Lock Count : 0
Lock Hold Count : 0
Lock rrIID : 0
Lock Status : Waiting
Lock release flags : 00000000
Tablespace TID :
Tablespace Name :
Table FID :
Table Schema :
Table Name :
Participant No 2 requesting lock
----------------------------------
Lock Name : 0x0000000E000000000050002252
Lock wait start time : 2019-11-09-02.00.55.360624
Lock wait end time : 2019-11-09-02.00.59.568645
Lock Type : ROW
Lock Specifics : ROWID=34,DATA_PARTITION_ID=0,PAGEID=80
Lock Attributes : 00040000
Lock mode requested : Share
Lock mode held : Exclusive
Lock Count : 0
Lock Hold Count : 0
Lock rrIID : 0
Lock Status : Waiting
Lock release flags : 00000000
Tablespace TID : 0
Tablespace Name : SYSCATSPACE
Table FID : 14
Table Schema : SYSIBM
Table Name : SYSPLANDEP
Attributes Requester Requester
--------------------- ------------------------------
------------------------------
Participant No 1 2
Application Handle 045836 045680
Application ID *N0.db2inst1.191113004057
XX.X.XXX.XXXX.38685.191108170055
Application Name db2bp db2bp
Authentication ID DB2INST1 DB2INST1
Requesting AgentID 89240 105695
Coordinating AgentID 89240 105695
Agent Status Recompiling UOW
Executing
Application Action No action No action
Lock timeout value 60 60
Lock wait value 5000 5000
Workload ID 1 107
Workload Name SYSDEFAULTUSERWORKLOAD
BATUSER1_WL
Service subclass ID 13 22
Service superclass SYSDEFAULTUSERCLASS
BAT_MED_CLASS
Service subclass SYSDEFAULTSUBCLASS
SYSDEFAULTSUBCLASS
Current Request Rebind Execute
Immediate
TEntry state 2 2
TEntry flags1 00000000 00000020
TEntry flags2 00000200 00000200
Lock escalation no no
Client userid
Client wrkstnname
Client applname TESTPGM
Client acctng
Utility ID |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 11.1 or later version that is fixed *
* fixpack. *
**************************************************************** |
| Local Fix: |
|
| Solution |
|
| Workaround |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 11.1 or later version that is fixed *
* fixpack. *
**************************************************************** |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 25.11.2019
28.06.2020
28.06.2020 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
