Handling deadlocks in the CICS DB2 environment

Deadlocks can occur in a CICS® DB2® system between two or more transactions or between one transaction and another DB2 user. Deadlocks can involve two resources or only one resource -- see Two deadlock types.

This section covers deadlocks only within DB2. If DB2 resources are involved in this type of deadlock, one of the partners in the deadlock times out according to the user-defined IRLM parameters. Other possible deadlocks are where resources outside DB2 are involved.

Deadlocks are expected to occur, but not too often. You should give special attention to deadlock situations if:

The IRLM component of the DB2 subsystem performs deadlock detection at user-defined intervals. One of the partners in the deadlock is the victim and receives a -911 or a -913 return code from DB2. The actual return code is determined by the DROLLBACK parameter for the DB2CONN (if a transaction is using a pool thread) or the DB2ENTRY used by the transaction. The other partner continues processing after the victim is rolled back.

To solve deadlock situations, you must perform a number of activities. Solving deadlocks means applying changes somewhere in the system to reduce the deadlock likelihood.

The following steps are often necessary for solving a deadlock situation:

  1. Detect the deadlock (see Deadlock detection).
  2. Find the resources involved (see Finding the resources involved).
  3. Find the SQL statements involved (see Finding the SQL statements involved).
  4. Find the access path used (see Finding the access path used).
  5. Determine why the deadlock occurred (see Determining why the deadlock occurred).
  6. Make changes to avoid it (see Making changes).

Two deadlock types

A deadlock within DB2 can occur when two transactions are both holding a lock wanted by the other transaction. In a DB2 environment, two deadlock types can occur when:

Deadlock detection

In a normal production environment running without DB2 performance traces activated, the easiest way to get information about a deadlock is to scan the MVS™ log to find the messages shown in Figure 53.

Figure 53. Deadlock messages
 
   DSNT375I PLAN p1 WITH CORRELATION ID id1
   AND CONNECTION ID id2 IS DEADLOCKED with
   PLAN p2 WITH CORRELATION ID id3
   AND CONNECTION ID id4.
 
   DSNT501I DSNILMCL RESOURCE UNAVAILABLE
            CORRELATION-ID=id1,CONNECTION-ID=id2
            REASON=r-code
            TYPE name
            NAME name
 

From these messages, both partners in the deadlock are identified. The partners are given by both plan name and correlation ID.

Also, a second message identifies the resource that the victim could not obtain. The other resource (whether it is the same or not) is not displayed in the message.

Finding the resources involved

To find the other resources involved in a deadlock, you may have to activate a DB2 performance trace and recreate the deadlock. Suppose that the reason for solving the deadlock is that the number of deadlocks is too high. Normally recreating the deadlock after the trace is started is a minor problem.

You should limit the DB2 performance trace to the two plans indicated in the MVS log message. The "AUTH RCT" parameter specifies the CICS transaction ID; so limiting the trace to the two transaction IDs (authorization IDs) involved can also be reasonable. The performance trace to be started should include class(06) for general locking events and class(03) for SQL events. The Database 2 Performance Monitor (DB2PM) is a useful tool to format the trace output. The DB2PM lock contention report and the lock suspension report can assist in determining the resources involved in the deadlock.

If the output from the DB2PM reports is too large, you can develop a user program to analyze the output from the traces. The goal is to find the resources involved in the deadlock and all the SQL statements involved.

Finding the SQL statements involved

A deadlock can involve many SQL statements. Often solving the deadlock requires finding all SQL statements. If the resources involved are identified from the lock traces, you can find the involved SQL statements in an SQL trace report by combining the timestamps from both traces.

Finding the access path used

To find the access path used by the SQL statements involved in the deadlock, use the EXPLAIN option of DB2 for the corresponding plans.

Determining why the deadlock occurred

Identifying both the SQL statements and the resources involved in the deadlock and finding the access path should show you why the deadlock occurred. This knowledge is often necessary to be able to develop one or more solutions. However, the process can be time-consuming.

Making changes

In general, a deadlock occurs because two or more transactions both want the same resources in opposite order at the same time and in a conflicting mode. The actions taken to prevent a deadlock must deal with these characteristics.

Table 17 shows a list of preventive actions and the corresponding main effects.

Table 17. Deadlock prevention
Actions Spread Resources Change the Locking Order Decrease Concurrency Change Locking Mode
Increase Index Freespace X
Increase Index Subpage Size X
Increase TS Freespace X
Change Clustering Index X X
Reorg the Table Space X X X
Add an Index X X (1)
Drop an Index X
Serialize the Transactions X
Use additional COMMITS X
Minimize the Response Time X
Change Isolation Level (2) X X
Redesign Application X X X X
Redesign Database X X X X
Notes:
  1. Due to changes in access path.
  2. Cursor stability is usually better than repeatable read.

To choose the right action, you must first understand why the deadlock occurred. Then you can evaluate the actions to make your choices. These actions can have several effects. They can:

It is therefore important that you carefully monitor the access path used by the affected transactions, for example by the EXPLAIN facility in DB2. In many cases, solving deadlocks is an iterative process.

Related concepts
Problem determination for CICS DB2
Thread TCBs (task control blocks)
Wait types for CICS DB2
Messages for CICS DB2
Trace for CICS DB2
Dump for CICS DB2
DB2 thread identificationTransaction abend codes for CICS DB2
Execution Diagnostic Facility (EDF) for CICS DB2
[[ Contents Previous Page | Next Page Index ]]