DB2 Locking, Part 5: Lock Suspensions, Timeouts, and Deadlocks

The longer a lock is held, the greater the potential impact to other applications. When an application requests a lock that is already held by another process, and the lock cannot be shared, that application is suspended. A suspended process temporarily stops running until the lock can be acquired. Lock suspensions can be a significant barrier to acceptable performance and application availability.

When an application has been suspended for a pre-determined period of time, it will be terminated. When a process is terminated because it exceeds this period of time, it is said to timeout. In other words, a timeout is caused by the unavailability of a given resource. For example, consider the scenario depicted in Figure 1.

Figure 1. A Timeout Occurs

If Program 2, holding no other competitive locks, requests a lock currently held by Program 1, DB2 tries to obtain the lock for a period of time. Then it quits trying. This example illustrates a timeout. This timeout scenario is also applicable to row locks, not just page locks.

The length of time a user waits for an unavailable resource before being timed out is determined by the IRLMRWT DSNZPARM parameter. You also can set this period of time by using the RESOURCE TIMEOUT field on the DB2 installation panel DSNTIPI.

When a lock is requested, a series of operations is performed to ensure that the requested lock can be acquired (see Figure 2). Two conditions can cause the lock acquisition request to fail: a deadlock or a timeout.

Figure 2. Processing a Lock Request

A deadlock occurs when two separate processes compete for resources held by one another. DB2 performs deadlock detection for both locks and latches. For example, consider the following processing sequence for two concurrently executing application programs:

Figure 3. A Deadlock Occurs

A deadlock occurs when Program 1 requests a lock for a data page held by Program 2, and Program 2 requests a lock for a data page held by Program 1. A deadlock must be resolved before either program can perform subsequent processing. DB2’s solution is to target one of the two programs as the victim of the deadlock and deny that program’s lock request by setting the SQLCODE to -911. This deadlocking scenario is also applicable to row locks, not just page locks. A graphic depiction of a deadlock is shown in Figure 4.

Figure 4. The Deadlock

The length of time DB2 waits before choosing a victim of a deadlock is determined by the DEADLOK IRLM parameter. You also can set this parameter using the RESOURCE TIMEOUT field on the DB2 installation panel DSNTIPJ.