- 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 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).
Forcing Lock Escalation Avoidance
Starting in Db2 Version 220.127.116.11, 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:
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.
To read further about this new cool feature, please see David's blog post.