Avoiding Lock Escalations and Improving Application Concurrency

- David Sciaraffa, Software Development Manager – IBM Db2 Availability

The concept and purpose of a database 'lock' should be well understood by all database administrators and application developers. Locks allow for multiple applications to access and manipulate database data at the same time.  Locks prevent race-conditions and provide the I in an A.C.I.D. compliant database - Isolation.

Locks, being conceptual in nature, come in many different forms and vary by database architecture. Most enterprise databases will have in common two fundemental types of locks: row lock and table lock. When a row within a table is being inserted, updated, deleted, or read, a row lock may be acquired. A lock on the entire table can also be acquired for some operations.  An application will hold locks for the duration of a unit of work, until a commit or rollback operation is performed (the unit of work is thus ended), at which point locks are released.

Finite Memory for Locks

Although locks are conceptual in nature, they are physically maintained within the database because a portion of database memory is used to manage and track locks.

The LOCKLIST configuration parameter is used to configure the quantity of database memory to use for managing and tracking locks.  MAXLOCKS allows the database administrator to configure the maximum percentage of locklist memory that a single application can use. This is meant as a safeguard to prevent a single un-tuned SQL operation from consuming all the locklist memory.

Since locklist memory is finite in size, the database manager is only capable of managing a finite quantity of locks. So, how should the database behave when locklist memory is exhausted and no more locks can be acquired to service applications? Db2's default behaviour is to free locklist memory by performing lock 'escalation'.

Lock Escalation

Lock escalation is the process by which many row locks on the same table and associated with an application connection, are released and replaced by a single table lock associated to that application connection. Thus the locklist memory which was previously used by the many row locks becomes available for reuse.

While lock escalation is beneficial in that it allows the database to continue to grant new locks, it also has a negative side affect. By escalating to a single table lock associated to that application, other applications may no longer be able to access rows on this table (depending on their configured isolation levels).

lockescalation1.jpg

Forcing Lock Escalation Avoidance

Starting in Db2 Version 11.1.2.2, a new feature called 'DB2_AVOID_LOCK_ESCALATION' provides control over lock escalation behaviour.  The feature is configured through a dynamic (no instance recycling needed) registry variable:

        db2set DB2_AVOID_LOCK_ESCALATION=ON

When this feature is enabled and an application encounters the conditions where lock escalation would normally occur (i.e. locklist memory is full, or MAXLOCKS limit reached) instead of performing lock escalation the application will receive an SQL0912N error. The application then has an opportunity to either perform a COMMIT or ROLLBACK to release the locks held by this application.

avoidance.png

 To read further about this new cool feature, please see David's blog post.

Recent Stories
Avoiding Lock Escalations and Improving Application Concurrency

Access your database sooner after a system failure -- introducing DB2_ONLINERECOVERY!

What's New In Recovery and Availability : Db2 V11.1.2.2