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

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:
  • lock_escals

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