A lock escalation occurs when, in the interest of reducing
memory that is allocated to locks (lock space), numerous row-level
locks are escalated into a single, memory-saving table lock. This
situation, although automated and saves memory space devoted to locks,
can reduce concurrency to an unacceptable level. You likely have a
lock escalation problem if you are experiencing a higher than typical
number of lock waits and the administration notification log entries
indicate that lock escalations are occurring.
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".
About this task
- Diagnosis
- Lock escalation from multiple row-level locks to a single table-level
lock can occur for the following reasons:
- The total amount of memory consumed by many row-level locks held
against a table exceeds the percentage of total memory allocated for
storing locks
- The lock list runs out of space. The application that caused the
lock list to be exhausted will have its locks forced through the lock
escalation process, even though the application is not the holder
of the most locks.
The threshold percentage of total memory allocated for storing
locks, that has to be exceeded by an application for a lock escalation
to occur, is defined by the maxlocks database
configuration parameter and the allocated memory for locks is defined
by the locklist database configuration parameter.
In a well-configured database, lock escalation is rare. If lock escalation
reduces concurrency to an unacceptable level, you can analyze the
problem and decide on the best course of action.
- Lock escalation is less of an issue, from the memory space perspective,
if self tuning memory manager (STMM) is managing the memory for locks
that is otherwise only allocated by the locklist database
configuration parameter. STMM will automatically adjust the memory
space for locks if it ever runs out of free memory space.
- Indicative signs
- Look for the following indicative signs of lock escalations:
- Lock escalation message entries in the administration notification
log
- What to monitor
- Due to the relatively transient nature of locking events, lock
event data is most valuable if collected periodically over a period
of time, so that the evolving picture can be better understood.
- Check this monitoring element for indications that lock escalations
might be a contributing factor in the SQL query performance slow down:
If you have observed one or more of the indicative
signs listed here, then you are likely experiencing a problem with
lock escalations. Follow the link in the "What to do next" section
to resolve this issue.
What to do next
After
having diagnosed that lock escalations are likely causing the problem
you are experiencing, take steps to resolve the issue: Resolving lock escalation problems
DB2 Version 10.1 for Linux, UNIX, and Windows