- 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.
Isolation Levels
When a row is read from a table by an application, the isolation level of the application determines the type of lock required. The isolation level allows the application to dictate how data changes are isolated from other applications. For example, suppose an application A is inserting rows into a table but has not yet committed it's changes (meaning that it could cancel and rollback these changes at any time), while application B is reading rows from this table -- should application B be allowed to read the uncommitted data that is being inserted by application A? Isolation levels allow the application to configure this behaviour, and more.
Isolation levels are well documented in the Db2 Knowledge Center. This developerworks article provides an excellent overview of isolation levels: https://www.ibm.com/developerworks/data/library/techarticle/dm-0509schuetz/
Finite Memory for Locks
Although locks are conceptual in nature, they are physically manifested 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. By default this parameter is tuned by the Self Tuning Memory Manager and will increase and decrease with usage.
Another configuration parameter, called 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 untuned 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).
For this reason, the frequency of lock escalations are monitored by experienced database administrators, and tuning is performed. Lock escalations can be monitored by querying the LOCK_ESCALS value of the MON_GET_ACTIVITY() table function (as well as many other granular table functions).
Common Causes of Lock Escalation
The possible causes of lock escalations are easily described:
- When an application inserts/updates/deletes a very large quantity of rows in a single unit of work, a lock may be required for every row and this can exhaust locklist memory. Applications should never perform large batch I/U/D operations like this in a single unit of work, and should instead perform a commit operation after a small set of rows is processed, repeatedly. The lock_count value of the MON_GET_LOCKS() table function can be monitored periodically to identify any applications which hold an large quantity of locks.
- When an application is using a strict isolation level (such as 'Repeatable Read (RR)'), a lock will be required for every row read by that application. For queries of large data sets, this can exhaust locklist memory. Tuning options must be explored (such as splitting the query operation into multiple smaller queries, or having the application explicitly acquire a table lock ahead of time, or reducing the isolation level of the application if that acceptable from business perspective). The lock_count value of the MON_GET_LOCKS() table function can be monitored periodically to identify any applications which hold a large quantity of locks.
- When locklist memory is not tuned by the Self Tuning Memory manager, it may simply be too small for the database workload. If the database administrator is not able to identify any applications which fit the two scenarios described above, then increasing the amount of locklist memory may be required. Estimating the ideal quantity of locklist memory is tricky and requires a deep understanding of application workloads and queries. This technote (http://www-01.ibm.com/support/docview.wss?uid=swg21600309) may be beneficial in helping to make that estimate. However, usually when lock escalations occur due to locklist memory exhaustion, a database administrator will simply increase the locklist memory by some factor (say 25%-100%) and then continue to monitor the number of lock escalations.
- When the MAXLOCKS configuration value is configured extremely small, lock escalations will occur. Since the intent of this configuration parameter is as a safeguard against runaway un-tuned queries, we generally see this value configured between 50% and 70%.
Forcing Lock Escalation Avoidance
When lock escalation problems are observed, a database administrator should always explore the cause and look for any tuning opportunities, as described earlier. Sometimes, queries and workloads are adhoc and beyond the control of the database administrator. In such scenarios, lock escalations are unpredictable and difficult to tune.
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 registry variable:
db2set DB2_AVOID_LOCK_ESCALATION=ON
(the database or database manager does not require a restart for this setting to take effect).
When this feature is enabled and an application encounters the conditions where lock escalation would normally occur (ie. 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."
For more information on lock escalation avoidance, please see:
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.regvars.doc/doc/r0005665.html#P_DB2_AVOID_LOCK_ESCALATION
I hope this information is useful. If you have any thoughts or questions, feel free to submit a comment below.
