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
DB2 10.5 for Linux, UNIX, and Windows