Deadlocks detection.

Document ID : KB000015163
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

In DB2 a Deadlock or Timeout situation produces a -911 sql error code.

-911   THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR TIMEOUT. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name

The current unit of work was the victim in a deadlock, or experienced a timeout, and had to be rolled back. The reason code indicated whether a deadlock or timeout occurred.

A deadlock situation arises when two (or more) units of work are waiting to acquire a lock on a shared resource, and none of them can proceed because a second unit of work also has a lock on some other resource that is required by the first session. 

Scenario: Unit of work #1 holds resource A, while trying to access resource B; at the same time Unit of work #2 holds resource B while trying to access resource A.
Usually DB2 resolves situations like this automatically by terminating one of the processes and rolling back all the changes it may have made.

Question:

How are deadlocks identified?
Explain how to identify them or what option must be selected?

Environment:
DB2 for Z/OS
Answer:

Assumption: The collection profile in use has been set up to collect the SQL Errors and Text.

SQL Errors             ==> Y
SQL Error Text       ==> Y

A deadlock in DB2 is a -911, just another SQL error code like many others.

Depending on whether you intend to look at the current activity or historical activity you would choose either:

1 SSID current interval data
or
2 SSID historical interval data

In either case you would drill down to the current collection interval in time and change the View Type ==> E

E for sql error. This would display the SQL error codes that have been captured.

In either current or historical display View Type ==> E will display the SQL ERROR Summary Display. Any -911's will be displayed and you can then drill down to the SQL statement.

Having drilled down and seen the -911 you can drill down to the SQL statement(if the collection profile has been set to collect it) and an Enhanced EXPLAIN of it can be done with the "E" option to go to CA Plan Analyzer for DB2 for z/os.

Detector shows the authid that was running and if the collection profile is set to collect error SQL then you can drill down to it.

It shows you just the authid having the -911.

Here is a sample:

19.0 > --------- DETECTOR SQL Error Detail Display -------- yy/mm/hh:mm:ss
Command ==> Scroll ==> PAGE
LINE 1 OF 1
SQLcode ==> -911 SQLstate ==> 40001 DB2 SSID ==> SSID
Opid ==> QAPUT Planname ==> DSNTEP2 Connid ==> BATCH

Interval Date => yy/mm/hh Interval Time => hh:mm:ss Elapsed Time => 01:00
-------------------------------------------------------------------------------

S -SQLCA Message Display, Q -SQL Call Text, D -View Detail

PROGRAM TYPE SQL_CALL STMT# SECT# DATE TIME AUTHID
-------- ---- -------- ------- ----- -------- -------- --------
_ DSN@EP2L PKGE PREPARE 1846 1 yy/mm/hh hh:mm:ss QAPUT

 And the SQL that incurred the deadlock is displayed with Q -SQL Call Text:

19.0 -------------- DETECTOR SQL Error Call Text ------------- yy/mm/hh:mm:ss
Command ==> Scroll ==> PAGE

DB2 SSID ==> SSID Planname ==> DSNTEP2 Connid ==> BATCH
Opid ==> QAPUT Authid ==> QAPUT Corrid ==> PDASD11A

-------------------------------------------------------------------------------

E -Explain SQL, T -Explain Text, I -ISQL ==> _

DROP DATABASE BAPDATST

Additional Information:

CA Database Management for DB2 for z/os , Detector User Guide: View SQL Errors Collection Data

CA Database Management for DB2 for z/os , Detector User Guide: View SQL Error Codes

This information can also be obtained using CA SYSVIEW for DB2 by using the following reports for Deadlocks.

BTCONTSM Summary of Lock Timeouts and Deadlocks Report.
BTLKCNT Lock Timeouts and Deadlocks Report.