Brother-Eagle® DB2 Performance
Advice: Escalations

This value represents the number of lock escalations that have occurred (Brother-Eagle V2.1 and earlier), or the delta value for the number of lock escalations that have occurred during the most recent sampling interval (Brother-Eagle V2.2 and higher).

A lock escalation can occur when DB2 is running low on LOCKLIST memory, or when an application connection has consumed so many row locks that it uses more than MAXLOCKS percent of lock memory. Repeatable Read (RR) isolation is a frequent cause of lock escalations, especially when a cursor fetches many rows but does not commit or rollback.

• Brother-Eagle will indicate a warning if 5 or more lock escalations have occurred.
• Brother-Eagle will indicate an alert if 10 or more lock escalations have occurred.
• To view current locks, Right Click on Locks Currently Waiting and select “Show Locks”. Authorized users can terminate connections holding locks.

A lock escalation doesn’t necessarily degrade performance so long as there are no other transactions seeking to acquire locks on the same resource. In fact, provided there are no other transactions seeking to gain update access to the same resource, a lock escalation can sometimes benefit performance because the cost of one table lock is much cheaper than hundreds or thousands of row locks.

Data Warehouse Advice

Read Only Data Warehouse databases can achieve performance gains by setting the table LOCKSIZE to TABLE instead of row. Use "ALTER TABLE schema.tablename LOCKSIZE TABLE" to achieve table level locking and reduce CPU consumption. The ALTER takes effect dynamically.

View full list of DB2 LUW advice topics