Diagnosing a lock wait problem

A lock wait occurs when a transaction tries to obtain a lock on a resource that is already held by another transaction. When the duration of the lock wait time is extended, this results in a slow down of SQL query execution. You likely have a lock wait problem if you are experiencing long or unexpected lock wait times and no lock timeouts.

Before you begin

In general, to be able to objectively assess that your system is demonstrating abnormal behavior which can include processing delays and poor performance, you must have information that describes the typical behavior (baseline) of your system. A comparison can then be made between your observations of suspected abnormal behavior and the baseline. Collecting baseline data, by scheduling periodic operational monitoring tasks, is a key component of the troubleshooting process. For more detailed information about establishing the baseline operation of your system, see: Operational monitoring of system performance.

For instructions about how to monitor lock wait locking events, see: Monitoring locking events.

About this task

Diagnosis
A lock wait occurs when one transaction (composed of one or more SQL statements) tries to acquire a lock whose mode conflicts with a lock held by another transaction. Excessive lock wait time often translates into poor response time, so it is important to monitor. The amount of lock wait time is best normalized to one thousand transactions because lock wait time on a single transaction is typically quite low and a normalized measurement is easier to handle.
There are different qualities of lock wait that must be taken into consideration when attempting to confirm diagnosis of each of them. The following list shows the three different qualities of lock wait and how best to diagnose them:
  • Long individual lock wait
    • Check for peak lock wait time from service class and workload. Set up the locking event monitor on workload to obtain that value.
  • Long lock wait time but short individual lock waits
    • Typically a result of a lock convoy. Use the db2pd -locks wait command to detect wait chains.
  • Types of lock being waited on
    • Checking this might help determine the problem. Find the agent that is waiting on the lock to get information about the lock type. Use the lock type information to determine if something obvious is occurring. For example, a package lock might indicate a BIND/REBIND command or DDL colliding with a user of that package; an internal c (catalog cache) lock might indicate a DDL colliding with a statement compilation.
Indicative signs
Look for the following indicative signs of lock waits:
  • The number of lock waits is increasing (increasing lock_waits monitor element value)
  • A high percentage of active agents waiting on locks (for example, 20%, or more, of the total active agents). For information about how to obtain this information, see the next section, What to monitor.
  • An increasing value of lock wait time (lock_wait_time monitor element) captured at database or workload level
What to monitor
Unlike many other types of Db2® monitor data, locking information is very transient. Apart from lock_wait_time, which is a running total, most other lock information goes away when the locks themselves are released. Thus, lock and lock wait event data are most valuable if collected periodically over a period of time, so that the evolving picture can be better understood.
To collect information about active agents waiting on locks, use the WLM_GET_SERVICE_CLASS_AGENTS table function. Agents waiting for locks are indicated by agents with the following attribute-value pairs:
  • EVENT_OBJECT = LOCK
  • EVENT_TYPE = ACQUIRE
You can also use application snapshot, the lock administrative views, or the lock wait option of the db2pd -wlocks command to obtain information about active agents waiting on locks.
These are the key indicator monitoring elements:
  • lock_waits value is increasing
  • long lock_wait_time value

If you have observed one or more of the indicative signs listed here, then you are likely experiencing a problem with lock waits. Follow the link in the What to do next section to resolve this issue.

What to do next

After having diagnosed that lock waits are likely causing the problem you are experiencing, take steps to resolve the issue: Resolving lock wait problems