The Key To Mastering Db2 Locking On Row-Store Tables

By Zoltan Toth posted Oct 31, 2017 06:00 AM


by: Zoltan Toth, IBM and Michael Feng, IBM


A database lock is a mechanism utilized by Db2 to govern the access to a database object amongst different transactions. 


The following is a list of objects that Db2 usually regulates via the usage of locks:

- Table

- Table partition

- Block

- Row

- Table space

- Catalog/Package cache

- Package/Variation

- Sequence


There are also locks that do not represent a tangible object but rather they are used to prevent incompatible operation from executing at the same time.  For example, an online-backup lock coordinates when an online-backup can run or an alter-table lock that governs when a table alteration operation gets executed.


Lock Memory Management:


Lock Resource Block (LRB)


Each distinct lock in the database consists of an LRB at the database level, containing a summary of all information from the transactions.  This includes information such as the type of lock, the ID of the locked object, a link to the transaction owning the lock, and much more.  Each transaction has its own LRB with transaction specific information such as duration, mode, hold count, attributes, cursor flags, and many others.  When an object is first locked, Db2 uses two LRBs, while subsequent locks to the same object use one additional LRB.


Prior to Db2 9.7, the size of an LRB was dependent on architecture word size, with one LRB consuming 48 bytes and 64 bytes for 32-bit and 64-bit platforms respectively.  As concurrency enhancements were added to the product, the size of this structure needed to increase.  In Db2 9.7 the size is of one LRB is 128 bytes regardless of platform.  Therefore, when upgrading from older version of Db2 to Db2 9.7, you should increase your lock-list size accordingly, if it is not set to automatic.  In Db2 9.7 the lock-list limit was increased from 2GB to 512GB to allow you to increase your lock-list size when upgrading.


Configuration Parameters




The database configuration parameter LOCKLIST controls the amount of memory to be used for locks (i.e. Lock Resource Blocks).  It is measured in 4K pages and can be set anywhere from 4 (16K) to 134217728 (512GB).  The default setting is AUTOMATIC, which allows Db2 to dynamically manage memory when there is at least 1 other AUTOMATIC memory setting.




The database configuration parameter MAXLOCKS controls the maximum percentage of LOCKLIST memory that a single transaction can consume.  It is measured in percent and can be set anywhere from 1 to 100.  The default setting is AUTOMATIC, which requires LOCKLIST to also be set to AUTOMATIC.


When an application consumes more than MAXLOCK percent of LOCKLIST memory, lock escalation will occur.  Lock escalation, which is performed automatically by Db2, is the process by which many row locks on the same table and associated with a single application connection, are released and replaced by a single table lock associated to that application connection in order to consume less LOCKLIST memory.


Lock escalation


Some people think that increasing MAXLOCKS will avoid lock escalations while others think that lowering MAXLOCKS will avoid lock escalations.  Both are true and not true at the same time.  If you lower MAXLOCKS, then a single application will have less LOCKLIST memory available to it and it might need to perform lock escalation sooner.  On the other hand, if you increase MAXLOCKS to 90% and a single application is using close to 90% of the LOCKLIST memory, then this leaves very little LOCKLIST memory for all of the other applications running on the database, increasing the chance of one of them needing to escalate their locks.  For this reason, the use of self-tuning memory management is encouraged.


Starting in Db2 Version, a new feature called 'DB2_AVOID_LOCK_ESCALATION' provides control over lock escalation behavior. 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. lock list 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.



Db2 Isolation Levels


When a row is read from a table by an application, the isolation level of the application or statement determines the type of lock required.  Db2 supports the following isolation levels:


Uncommitted Read (UR)

The Uncommitted Read isolation level is intended for read only statements.  Queries using this isolation level:

- perform no row locking

- can see uncommitted changes

- skip uncommitted deleted records


Cursor Stability (CS)

The Cursor Stability isolation level is also intended for read only statements.  This is the default isolation level.  Queries using this isolation level:

- might lock rows

- only process committed data


Read Stability (RS)

The Read Stability isolation level is intended to be used when future actions might need to be taken on processed rows.  It is like the CS isolation level except that it acquires and retains locks on qualifying rows until the end of the transaction.


Repeatable Read (RR)

The Repeatable Read isolation level is intended to be used when you require no changes to results by other transaction between when the query sees a record to when the transaction commits.  It is like the RS isolation level except that it acquires locks not just on qualifying rows, but on all rows seen by the scan.

Db2 and ANSI Isolation Levels - Naming Differences and Anomalies



* An example of the non-repeatable read phenomenon is as follows: Unit of work UW1 reads a row. Unit of work UW2 modifies that row and commits. If UW1 subsequently reads that row again, it might see a different value.


** An example of the phantom read phenomenon is as follows: Unit of work UW1 reads the set of n rows that satisfies some search condition. Unit of work UW2 inserts one or more rows that satisfy the same search condition and then commits. If UW1 subsequently repeats its read with the same search condition, it sees a different result set: the rows that were read originally plus the rows that were inserted by UW2.


Selecting an Isolation Level


When selecting an isolation level, you must base your decision on functional requirements, not performance considerations.  After all, if it doesn't work, does it matter how fast it is?  Consider what behavior your application, session, transaction, or statement requires and then pick the lowest isolation level that meets the business need.


The Access Plan's Effect on Locking


An access plan is the set of operations chosen by the Db2 compiler to satisfy and execute an SQL statement.  Different access plans cause access to different data, different granularity and different order in which data is accessed, and sometimes the lock hierarchy/modes used.  Every row that Db2 visits is like a car driving down the road towards a stop-light.  The stop-light can be green or it can be red.  If the access plan tells Db2 to go through a table, we look at every row in the table.  If the access plan tells Db2 to go through portion of the index by supplying a start-key and a stop-key, then we are being told to take an optimized path with fewer stop-lights.


Locking-Related Registry Variables



When enabled, this registry variable allows statements using CS or RS isolation levels to skip uncommitted inserted rows as if they had not yet been inserted.  Since the definition of CS and RS isolation levels both allow for phantom reads, skipping uncommitted inserted rows is often acceptable and does not modify their definition or list of possible anomalies.  The only time you should not use this setting is if there are applications that talk to each other through uncommitted, inserted rows.



When enabled, this registry variable allows statements using CS or RS isolation levels to defer or avoid locking until a row/key is known to satisfy the predicates specified in the scan.  It also tells Db2 to unconditionally skip deletes rows on table access, while continuing to block on qualifying deleted keys.  Queries will behave like UR isolation until the row qualifies.  This should only be used if the application's behavior is well known and can tolerate skipping rows due to uncommitted updates and deletes.  With this setting Db2 will still not process and return uncommitted data.



When enabled, this registry variable allows statements using CS or RS isolation levels to unconditionally skip deleted rows and deleted keys.  Since index updates are decomposed into delete and insert operations, uncommitted updates could also be skipped.  With this setting Db2 will still not process and return uncommitted data, but will only skip uncommitted deletes (and updates for index scans).  Only use this setting if the application's behavior is well known and can tolerate skipping rows due to uncommitted deletes and updates.


The DB2_SKIPINSERTED, DB2_EVALUNCOMMITTED, and DB2_SKIPDELETED registry variables do not apply and are not respected for some CS/RS use cases.  Such cases include access to Db2 catalog tables (explicit or internal) and scans for integrity processing, such as referential integrity cascade/validation or MQT maintenance.


Currently Committed (new to Db2 9.7)


Lock timeouts and deadlocks can occur under the CS isolation level with row-level locking, especially with applications that are not designed to prevent such problems.  Some high throughput database applications cannot tolerate waiting on locks that are issued during transaction processing, and some applications cannot tolerate processing uncommitted data, but still require non-blocking behavior for read transactions.  Under the new currently committed semantics, only committed data is returned, as was the case previously, but now readers do not wait for writers to release row locks. Instead, readers return data that is based on the currently committed version -- that is, data prior to the start of the write operation.


There are two steps involved in the implementation of currently committed.  First, lock avoidance is applied such that no row locking is performed for data that it knows is committed.  Second, currently committed uses Db2's lock manager to store information to allow access to the currently committed version of the data/row from the transaction logs (either in the log buffer or the log files).  If infinite logging is enabled (i.e. the database configuration parameter LOGSECOND is set to -1, which is not recommended), the log record might have been overwritten already, in which case Db2 will wait on the lock.


Currently Committed can be enabled in one of many ways.  The first way is the CUR_COMMIT database configuration parameter, which may be set as follows:

- ON:  default for new databases created in Db2 9.7 - all read only CS scans will use currently committed

- AVAILABLE:  need to request currently committed (see below)

- DISABLED:  default value on upgrade -- doesn't support currently committed, even on explicit requests


When CUR_COMMIT is set to AVAILABLE, currently committed can be enabled or disabled at the package level by using ConcurrentAccessResolution bind option.  Valid values for the parameter are USE CURRENTLY COMMITTED (default), USECURRENTLYCOMMITTED, WAIT FOR OUTCOME, and WAITFOROUTCOME.


Using Old Locking Behavior


While many applications running on your database will benefit from using Db2's locking enhancements, sometimes a set of queries or applications might need Db2's old locking behavior (i.e. to have Db2 lock every potentially qualifying row when using the cursor stability or read stability isolation levels).  This can be done at the select-statement level using the WAIT FOR OUTCOME option, or at the package level using the "ConcurrentAccessResolution WaitForOutcome" bind option.



To examine the currently committed activity in database, two db2pd options are useful:


1) db2pd -d

This will indicate the type and number of reads from log performed for currently committed.  Increasing the log buffer size might reduce the number of disk log reads.



db2pd -d mytestdb -logs | grep "Cur Commit"

Cur Commit Disk Log Reads    1771

Cur Commit Total Log Reads   8211


2) db2pd -d

In the "TCB Table Stats" section there is a column named CCLogReads that indicates how many times the currently committed image was retrieved from the log for each table partition.


SQL Locking Options




The ALTER TABLE statement has a LOCKSIZE option that can be set to ROW, BLOCKINSERT, or TABLE.  It can be used to specify the lowest level lock taken on the table.  By default, this gets set to ROW when a table is created.  Setting LOCKSIZE to TABLE is ideal for read only tables as it will avoid the row-locking code path and will reduce the amount of lock-list memory consumed for those tables.  Set LOCKSIZE to BLOCKINSERT for Multi-Dimensional Cluster (MDC) tables to have blocks locked X instead of IX, eliminating the need for X row locking on insert.  This is great for bulk-insert workloads.


Cursor Blocking and FOR READ ONLY or FOR UPDATE [OF ...]


Cursor blocking is a technique that reduces overhead by having the database manager retrieve a block of rows in a single operation.  By default, Db2 will only apply cursor blocking when the cursor specifies the FOR READ ONLY option, or when neither FOR READ ONLY nor FOR UPDATE is specified, the cursor is unambiguous and is read-only (e.g. a cursor that uses a GROUP BY clause cannot update the current cursor position and therefore must be read-only).  For this reason, it is important to tell Db2 whether a cursor is read-only or not.


To change the default cursor blocking behavior, use the BLOCKING option on the PREP or BIND commands.


Lock Modes and FOR READ ONLY or FOR UPDATE [OF ...]


There are three general categories of row lock modes:

- Share (S) - Under an S lock, concurrent application processes are limited to read-only operations on the data.

- Update (U) - Under a U lock, concurrent application processes are limited to read-only operations on the data, if these processes have not declared that they might update a row. The database manager assumes that the process currently looking at a row might update it.

- Exclusive (X) - Under an X lock, concurrent application processes are prevented from accessing the data in any way.  This does not apply to application processes with an isolation level of uncommitted read (UR) (which can read the row without a lock) or cursor stability (which might be able to read the row without a lock if it can determine that value it sees is currently committed).


The purpose of the U lock is to prevent a deadlock when two concurrent statements are both reading rows with the intention of updating them.  They could each get an S lock on a common row at the same time and then both decide to update it, attempting to convert their S locks to X locks, resulting in a deadlock.  A U lock is compatible with an S lock, but not with another U lock on the same row.  In the above scenario, U locks are taken on the row instead of S locks, so the first transaction that sees the row gets a U lock, blocking the other transaction from reading it.  U locks are taken by default on rows selected by a cursor when a FOR UPDATE clause is specified, or when neither FOR READ ONLY nor FOR UPDATE are specified and the cursor is ambiguous.  This is another reason why it is important to tell Db2 whether a cursor is read-only or not.


Specifying Lock Modes Explicitly


Db2 allows you to specify the lock mode used by cursors that use the read stability and repeatable read isolation levels.  This is done by using the USE AND KEEP {SHARE | UPDATE | EXCLUSIVE} LOCKS clause of the WITH {RS | RR} isolation option.  It provides finer grained locking control for applications to:

- use read-only cursors and have them benefit from cursor blocking while still doing U or X locking.  This is useful in the case where an application does not use the cursor to update rows, but instead performs a searched later in the transaction.

- force X locking so that no lock conversion is necessary when deleting or updating the row using the cursor (i.e. WHERE CURRENT OF).

- override FOR UPDATE's use of U locks and acquire shared locks instead.


Releasing Locks Prior to End of Transaction


Sometimes an application needs read stability or repeatable read isolation level semantics for the life of the cursor, but not the life of the transaction.  When this is the case, close the cursor with the WITH RELEASE option to release all read locks acquired by the cursor.