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

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.

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.