The Lock List Utilization alarm becomes active when the amount of lock list storage used, expressed as a percentage, approaches its maximum size, as defined by the LOCKLIST database configuration parameter.
Lock list is the memory allocated for locks in a database. Once the lock list is full, database performance degrades. Lock escalation increases, reducing concurrency on shared objects in the database. Additionally, because applications are waiting on a limited number of table locks, more deadlocks between applications can occur, causing transactions to be rolled back.
The thresholds are predefined as follows:
Threshold 1—The zero to 69 percent range. The lock list utilization percentage is very low. No alarms are raised.
Threshold 2—The 70 to 79 percent range. This raises a Low severity alarm stating, The lock list utilization percentage is moderate.
Threshold 3—The 80 to 89 percent range. This raises a Medium severity alarm stating, The lock list utilization percentage is high.
Threshold 4—The 90 to 100 percent range. This raises a High severity alarm stating, The lock list utilization percentage is very high.
The underlying metricsfor this alarm can be adjusted to suit your environment by using the Metric Editor.
Perform frequent COMMITs to release locks.
When performing many updates, lock the entire table before updating (using the SQL LOCK TABLE statement). This tactic uses only one lock and keeps others from interfering with the updates. However, it does reduce concurrency of the data.
Use the LOCKSIZE parameter of the ALTER TABLE statement to control how locking is performed for a specific table.
Access the Databases drilldown. The Locking tab allows you to profile the database’s lock list utilization.
Access the Client Applications drilldown. The Locking tab identifies those applications holding locks as well as those waiting for locks.