Lock escalation

Lock escalation is the process of converting many fine-grain locks to fewer coarse-grain locks, which reduces memory overhead at the cost of decreasing concurrency.

It is the act of releasing a large number of fine-grain row, MDC, LOB, or XML locks which are held by an application process on a single table, to acquire a table lock, or other coarse-grain lock such as block or LOB locks, of mode S or X instead.

Lock escalation occurs when an application exceeds the MAXLOCKS threshold or the database approaches the LOCKLIST limit. The database manager writes messages (AM5500W/ADM5501I) to the administration notification log which identifies the table for which lock escalation occurred, and some information to help you identify what plan or package was running when the escalation occurred.

The benefit of lock escalation is that operations that would otherwise fail with an SQL0912N error, can instead become successful due to lock escalation. However, the operation may still fail due to lock timeout or deadlock. As a drawback, lock escalation may negatively affect concurrency with other applications which may need to access the table.

Avoiding lock escalation

To avoid lock escalation, you can modify the application to acquire table locks using the LOCK TABLE statement. This is a good strategy for tables where concurrent access by many applications and users is not important.

You can also avoid lock escalation by setting the DB2_AVOID_LOCK_ESCALATION registry variable to ON. This causes the application to fail on the lock request with an SQL0912N error.