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
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.
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