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:
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:
A typical example of this is when a transaction opens a cursor with the ORDER BY option and uses an index to avoid the sort. When a row in a page is fetched, DB2 takes a share (S) lock at that page. If the transaction then issues an update without a cursor for the row last fetched, the S-lock is promoted to an exclusive (X) lock.
If two of these transactions run concurrently and both get the S-lock at the same page before taking the X-lock, a deadlock occurs.
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.
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.
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.
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.
To find the access path used by the SQL statements involved in the deadlock, use the EXPLAIN option of DB2 for the corresponding plans.
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.
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.
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:
|
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.