Diagnosing a lock escalation problem
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
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
- 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.
- 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 nextsection 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