Detector for Db2 for z/OS and Sysview Performance Management Option for DB2: Deadlocks detection.
Article ID: 15163
Updated On:
Products
Issue/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.
How are deadlocks identified?
Explain how to identify them or what option must be selected?
Resolution
Assumption: The Detector for Db2 for z/OS 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 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:
20.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 ==> xxxxx 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 xxxxx
And the SQL that incurred the deadlock is displayed with Q -SQL Call Text:
20.0 -------------- DETECTOR SQL Error Call Text ------------- yy/mm/hh:mm:ss
Command ==> Scroll ==> PAGE
DB2 SSID ==> SSID Planname ==> DSNTEP2 Connid ==> BATCH
Opid ==> xxxxx Authid ==> xxxxx Corrid ==> xxxxxxxx
-------------------------------------------------------------------------------
E -Explain SQL, T -Explain Text, I -ISQL ==> _
DROP DATABASE BAPDATST
Additional Information
Detector User Guide: View SQL Errors Collection Data
Detector User Guide: View SQL Error Codes
This information can also be obtained using Sysview for Db2 by using the following reports for Deadlocks.
BTCONTSM Summary of Lock Timeouts and Deadlocks Report.
BTLKCONT Lock Timeouts and Deadlocks Report.
Feedback
