Investigation of root cause and resource responsible for the deadlock in DB2

DB2DatabaseBig Data Analytics

Problem: A COBOL-DB2 program failed due to deadlock. How will you find the resource due to which the program failed?

Solution

A DEADLOCK condition occurs when two or more applications are stuck, waiting for each other to release the locks on the resources needed by them. A detailed information and logs can be found in the DB2 system job DSNZMSTR job. The DSNZ is the name of the installed DB2 sub-system and it can vary from installation to installation. The SYSOUT of this job continues to display the DB2 level system logs. The logs related to the deadlock are also available in this job.

First, we need to find out the time at which our COBOL-DB2 program has failed due to deadlock. Next, we will check the SYSOUT of the MSTR job during that particular time.

We will get deadlock information as - COBOL-DB2 programs/transactions/processes involved in deadlock, DB2 resource on which deadlock occurred, etc.

Let us see an example of the deadlock condition.

Consider following ORDERS and TRANSACTIONS DB2 tables.

ORDER_ID
ORDER_DATE
TRANSACTION_ID
Z22345
22-10-2020
IRN11236
Z62998
14-09-2020
IRN77812
Z56990
01-09-2020
IRN89023
Z56902
21-09-2020
IRN09663
Z99781
12-08-2020
IRN88112
Z56112
30-10-2020
IRN67091

 

TRANSACTION_ID
TRANSACTION_AMOUNT
IRN11236
6754
IRN77812
451
IRN89023
9087
IRN09663
1156
IRN88112
5908
IRN67091
152


If there are two programs currently in the execution phase — PROG A and PROG B. PROG A is reading TRANSACTIONS and ORDERS DB2 tables and holding a SHARED lock on both the tables. PROG B is also reading TRANSACTIONS and ORDERS DB2 table and it is also holding a SHARED lock on both the tables.

PROG A needs to update the ORDERS table, so it will promote its SHARED lock to the UPDATE lock and then it will wait for PROG B to release its SHARED lock from ORDERS table so that UPDATE lock can be promoted to EXCLUSIVE lock.

At the same time, PROG B needs to update TRANSACTIONS table, so it will promote its SHARED lock to UPDATE lock and will wait for PROG A to release its SHARED lock from TRANSACTIONS table in order to promote its lock from UPDATE to EXCLUSIVE.

In the above scenario, PROG A and PROG B are waiting for each to release the lock but practically they are stuck and entered into a state known as deadlock.

raja
Published on 30-Nov-2020 09:40:48
Advertisements