Lock contention

Suspension

Incoming lock requests are queued. Requests for lock promotion, and requests for a lock by an application process that already holds a lock on the same object, precede requests for locks by new applications. Within those groups, the request order is "first in, first out."

For example, using an application for inventory control, two users attempt to reduce the quantity on hand of the same item at the same time. The two lock requests are queued. The second request in the queue is suspended and waits until the first request releases its lock.

The suspended process resumes running when:

• All processes that hold the conflicting lock release it.
• The requesting process times out or deadlocks and the process resumes to deal with an error condition.

Timeout

An application process encounters a timeout when it terminates because of a suspension that exceeds a preset interval. DB2 terminates the process, issues messages, and returns error codes.

For example, an application process attempts to update a large table space that is being reorganized by the utility REORG TABLESPACE with SHRLEVEL NONE. It is likely that the utility job does not release control of the table space before the application process times out.

DB2 terminates the process, issues two messages to the console, and returns SQLCODE -911 or -913 to the process (SQLSTATEs '40001' or '57033'). Reason code 00C9008E is returned in the SQLERRD(3) field of the SQLCA. Alternatively, you can use the GET DIAGNOSTICS statement to check the reason code. If statistics trace class 3 is active, DB2 writes a trace record with IFCID 0196.

If you are using IMS™, and a timeout occurs, the following actions take place:

• In a DL/I batch application, the application process abnormally terminates with a completion code of 04E and a reason code of 00D44033 or 00D44050.
• In any IMS environment except DL/I batch:
  • DB2 performs a rollback operation on behalf of your application process to undo all DB2 updates that occurred during the current unit of work.
  • For a non-message driven BMP, IMS issues a rollback operation on behalf of your application. If this operation is successful, IMS returns control to your application, and the application receives SQLCODE -911.

    If the operation is not successful, the response depends on the value of GSROLBOK parameter of the IMS PSB properties. If the GSROLBOK value is NO, IMS issues user abend code 0777, and the application does not receive an SQLCODE. If the GSROLBOK value is YES, the application receives a failure indication on the ESS call followed by an internal IMS ROLB, which results in SQLCODE -911 for DB2.

  • For an MPP, IFP, or message driven BMP, IMS issues user abend code 0777, rolls back all uncommitted changes, and reschedules the transaction. The application does not receive an SQLCODE.
  Related information:

  • PSB segment type format
  • PSBGEN statement

  

Commit and rollback operations do not timeout. The STOP DATABASE command, however, can time out, in which case DB2 sends messages to the console. When this happens DB2 retries the STOP DATABASE command as many as 15 times.

Deadlock

A deadlock occurs when two or more application processes each hold locks on resources that the others need and without which they cannot proceed. After a preset time interval, DB2 can roll back the current unit of work for one of the processes or request a process to terminate. In determining which process to roll back or terminate, DB2 assesses many characteristics of the processes that are involved in the deadlock and chooses the one that, if terminated, will cause the least impact relative to the other processes. By choosing a process to roll back or terminate, DB2 frees the locks and allows the remaining processes to continue.

The following figure illustrates a deadlock between two transactions.

Notes:

1. Jobs EMPLJCHG and PROJNCHG are two transactions. Job EMPLJCHG accesses table M, and acquires an exclusive lock for page B, which contains record 000300.
2. Job PROJNCHG accesses table N, and acquires an exclusive lock for page A, which contains record 000010.
3. Job EMPLJCHG requests a lock for page A of table N while still holding the lock on page B of table M. The job is suspended, because job PROJNCHG is holding an exclusive lock on page A.
4. Job PROJNCHG requests a lock for page B of table M while still holding the lock on page A of table N. The job is suspended, because job EMPLJCHG is holding an exclusive lock on page B. The situation is a deadlock.

After a preset time interval (the value of DEADLOCK TIME), DB2 can roll back the current unit of work for one of the processes or request a process to terminate. That frees the locks and allows the remaining processes to continue. If statistics trace class 3 is active, DB2 writes a trace record with IFCID 0172. Reason code 00C90088 is returned in the SQLERRD(3) field of the SQLCA. Alternatively, you can use the GET DIAGNOSTICS statement to check the reason code. (The codes that describe the exact DB2 response depend on the operating environment.)

It is possible for two processes to be running on distributed DB2 subsystems, each trying to access a resource at the other location. In that case, neither subsystem can detect that the two processes are in deadlock; the situation resolves only when one process times out.

Deadlocks and TSO, Batch, and CAF

When a deadlock or timeout occurs in these environments, DB2 attempts to roll back the SQL for one of the application processes. If the ROLLBACK is successful, that application receives SQLCODE -911. If the ROLLBACK fails, and the application does not abend, the application receives SQLCODE -913.

Deadlocks and IMS

If you are using IMS, and a deadlock occurs, the following actions take place:

• In a DL/I batch application, the application process abnormally terminates with a completion code of 04E and a reason code of 00D44033 or 00D44050.
• In any IMS environment except DL/I batch:
  • DB2 performs a rollback operation on behalf of your application process to undo all DB2 updates that occurred during the current unit of work.
  • For a non-message driven BMP, IMS issues a rollback operation on behalf of your application. If this operation is successful, IMS returns control to your application, and the application receives SQLCODE -911.

    If the operation is not successful, the response depends on the value of GSROLBOK parameter of the IMS PSB properties. If the GSROLBOK value is NO, IMS issues user abend code 0777, and the application does not receive an SQLCODE. If the GSROLBOK value is YES, the application receives a failure indication on the ESS call followed by an internal IMS ROLB, which results in SQLCODE -911 for DB2.

  • For an MPP, IFP, or message driven BMP, IMS issues user abend code 0777, rolls back all uncommitted changes, and reschedules the transaction. The application does not receive an SQLCODE.

Related information:

• PSB segment type format
• PSBGEN statement

Deadlocks and CICS

If you are using CICS® and a deadlock occurs, the CICS attachment facility decides whether or not to roll back one of the application processes, based on the value of the ROLBE or ROLBI parameter. If your application process is chosen for rollback, it receives one of two SQLCODEs in the SQLCA:

-911

A SYNCPOINT command with the ROLLBACK option was issued on behalf of your application process. All updates (CICS commands and DL/I calls, as well as SQL statements) that occurred during the current unit of work have been undone. (SQLSTATE '40001')

-913

A SYNCPOINT command with the ROLLBACK option was not issued. DB2 rolls back only the incomplete SQL statement that encountered the deadlock or timed out. CICS does not roll back any resources. Your application process should either issue a SYNCPOINT command with the ROLLBACK option itself or terminate. (SQLSTATE '57033')

Consider using the DSNTIAC subroutine to check the SQLCODE and display the SQLCA. Your application must take appropriate actions before resuming.