DB2 uses transaction locking, via the IRLM, latches, and other non-IRLM mechanisms to control concurrency and access of SQL statements and utilities. These mechanisms associate a resource with a process so that other processes cannot access the same resource when it would cause lost updates and access to uncommitted data. Generally, a process will hold a lock on manipulated data until completing its work so as to ensure that other processes do not get hold of data that has been changed but not committed. Another use is for repeatable read, that is, when an application needs to reread data that must still be in the same state as it was when it was initially read. In addition, user options are available to avoid locking and to allow for access to uncommitted data, as well as system settings and mechanisms to provide for lock avoidance when it would not cause a data-integrity problem.
The following objects can be locked: user tables, catalog tables, and directory tables. Not all objects need to have locks acquired on them to establish concurrency, however. Indexes are not locked, as serialization is controlled by latches, and concurrency is controlled by data locking. Drains and claims can be used to control utilities and DB2 commands. Draining allows utilities and commands to acquire partial or full control of a needed object, with minimal interruption to concurrent access.
DB2 often needs to access data in the catalog, and the data being read or updated must be locked. The following locks can be used during binding: SKCT (skeleton cursor table) for a plan and SKPT (skeleton package table) for a package. During DDL activity, DBD (database descriptor) can be used.
Locks should be viewed as having size (how much data is locked), mode (whether others are allowed to read and/or update the locked object), and duration (how long the lock is held). Each attribute plays a role in the acquisition and release of a lock, and understanding these attributes can help you use them wisely and avoid contention.
More than one type of lock can exist for a specific user for a particular table space. This depends on the mode of the lock. For example, locks might be held on the table space, table, page, and row simultaneously for a particular user. Each lock has its own mode.
Locks can be taken on various objects in DB2. The size of the lock determines how much of the total object will be locked. For non-LOB data, locks can be taken on a
For LOB data, LOB locks can be taken.
Table Space and Table Locks
These locks are the most encompassing and allow the least amount of concurrency. A table space lock locks the entire table space and may prevent access to all pages of all tables contained in that table space. Table locks in a simple table space may lock data in other tables, as the rows can be intermingled on different pages. In a segmented table space, the pages with rows from a single table will be locked and will not affect other tables in the table space. For more information on simple and segmented table spaces, refer to Chapter 4.
DB2 can choose to lock only a partition of a partitioned table space, known as selective partition locking (SPL). Prior to version 8, you could get only SPL if the tablespace defined with LOCKPART(YES). Now, SPL will be used regardless of the LOCKPART setting. SPL can also tell DB2 not to escalate locks to the table space level but rather to the partition level. This allows for less propagation of locks to the coupling facility in a data sharing environment and better concurrency for applications accessing data in various partitions. This feature can be especially useful if usage against the partitions is spread over multiple members using affinity routing.
However, SPL will help in a non-data sharing environment only if an agent escalates. Without SPL, the parent intent lock is taken on the last partition, regardless of which ones you access. These locks are almost always intent locks and therefore almost never cause a problem. With SPL, the parent intent lock is taken on whichever partition you access. Escalation with SPL occurs only for the partition(s) on which excessive locks occurred. Without SPL, the parent lock is only on the last one, so the lock size is escalated to the entire page set, and access to all partitions is denied.
SPL cannot be used in the following situations:
Page locking, usually the lock size of choice for best concurrency and performance, allows DB2 to lock only a single page of data, whether it is 4K, 8K, 16K, or 32K. Page locks for a table in a simple table may lock rows of more than one table, as the rows can be intermingled on the page. Page locks for tables in a segmented table space will lock only rows of a single table.
DB2 supports row-level locking; if applications are experiencing a lot of concurrency problems, row locks might be considered. However, row locks should not be used as a fix for what could be a physical-design issue or an application-design issue. They should be used when a page of data is needed simultaneously by multiple applications, and each user's interest is on different rows. If the interest is on the same row, row-level locking buys you nothing. Use row-level locking only if the increase of the costconcurrency and wait-time overheadof locking is tolerable and you can definitely justify the benefit.
In the hierarchy of locks, row locks and page locks occur at the same level. If either a page lock or a row lock is escalated, it moves to the same higher level, and a row lock can never escalate to a page lock.
LOBs have a different lock mode: a LOB lock. LOB locks have different characteristics from regular locks. A LOB has no concept of row or page locking. LOB locking is not at all like the traditional transaction-level locking. Because LOBs are in an associated object, concurrency between the base table and the LOB must be maintained at all times, even if the base table is using uncommitted read. A LOB lock still needs to be held on the LOB to provide consistency and, most importantly, to maintain space in the LOB table space. LOB locks avoid conflicts between readers and deletes and updates. A SELECT or a DELETE will acquire a share-mode LOB lock. The lock doesn't prevent the DELETE but will prevent the reuse of the deallocated pages until all the LOB locks are released. A shadow copy of the LOB will exist until the LOB locks are released. INSERTs will acquire exclusive LOB locks on new LOBs; an UPDATE is basically a DELETE followed by an INSERT. The LOCK TABLE statement, which acquires a lock on the entire object, can still be used and will not require individual LOB locks.
The ACQUIRE option of BIND has no effect on when the table space lock on the LOB table space is taken. Locks on LOB table spaces are acquired when they are needed. The table space lock is released according to the value specified on the RELEASE option of BIND, with a couple of exceptions: when a cursor is defined WITH HOLD or a LOB locator is held. When a cursor is defined WITH HOLD, LOB locks are held through commit operations. When a LOB value is assigned to a LOB locator, the lock acquired will remain until the application commits. If the application uses HOLD LOCATOR, the locator and the LOB lock must be held and cannot be freed until certain conditions exist. The locks would be held until the first commit operation after a FREE LOCATOR statement is issued or until the thread is deallocated. Using LOB columns generally requires an increase in the number of locks held by a process. As a starting point for preventing unforeseen problems, LOCKMAX should be nonzero on the LOB table spaces. An even better strategy is to use the gross locks during heavy inserting processes to prevent most of the locking difficulties.
LOCK TABLE Statement
Some situations may call for the ability to exclusively lock a table or a partition. A single SQL statement can override locking at any time: LOCK TABLE. You can prevent all other access with LOCK TABLE IN EXCLUSIVE MODE. A typical reason to override locking is if you want to take a snapshot of data and be able to access a table without any other concurrent processes changing data. You can allow other processes read-only access to the table by using the LOCK TABLE IN SHARE MODE statement. This is different from the repeatable read (RR) isolation, because RR prevents changes only from rows or pages that you have already accessed. You can prevent all other access with LOCK TABLE IN EXCLUSIVE MODE.
The LOCK TABLE/TABLESPACE statement can be used only to get a lock, in either share (S) mode or exclusive (X) mode. The lock is acquired when the SQL statement is executed. Its release is dependent on the previous settings of the ACQUIRE and RELEASE parameters. The table or table space lock is released at thread deallocation if RELEASE(DEALLOCATE) was specified in the options when the program was bound. The lock also can be released at either COMMIT or ROLLBACK time if the RELEASE(COMMIT) option was specified at bind time. Despite the type of locking held when the SQL LOCK TABLE statement was issued, when the COMMIT or ROLLBACK is issued, the system falls back to page-level locking unless another SQL LOCK TABLE statement is issued. A user or an application must continually reissue the SQL LOCK TABLE statement after the COMMIT or ROLLBACK when RELEASE(COMMIT) has been specified if locking is still needed at the table or table space level.
The LOCK TABLE IN EXCLUSIVE MODE statement can also be used to avoid additional overhead if you are updating a large portion of a table. This can be more efficient; by preventing concurrent accesses to the data, DB2 avoids the overhead to lock and unlock each page during updates.
Timeouts can be prevented by using either the LOCK TABLE IN EXCLUSIVE or IN SHARE MODE statement. This allows you to process the data quickly, without the possibility of having to contend for locks with other applications.
If further granularity is needed, you can choose to lock only the partition, as seen in the following example:
LOCK TABLE CERTTB PART 1 IN EXCLUSIVE MODE
This example would take an X lock on Part 1, regardless of any other locks held on other partitions in the table space.
As of version 8, the LOCKPART keyword on the CREATE TABLESPACE statement is ignored, so the PART option on the LOCK TABLE statement can be used whether or not LOCKPART(YES) was specified.
DB2 has the following lock modes:
Modes are easy to understand with a simple formula. If the table or table space lock has an I (intent) in it, row or page locks are in use on the individual pages. In all other cases, the table or table space lock will be the only lock used. The intent locks act as an indicator to DB2 to identify what is occurring within the table or table space. Some locking is beyond the control of the programmer and designer; other locks can be controlled to a degree. Table 16-1 lists the compatibility of the various lock modes for table spaces, tables, and partitions. Table 16-2 lists the compatibilities for pages and rows.
Table or Table Space Locks
When using page or row locks, the table or table space lock will be IS/IX/SIX:
SIX is not a common lock. This mode usually occurs when an IX lock already exists and a share has to be acquired.
When not using row or page locks, the table/table space lock will be S/U/X:
Page or Row Locks
Page locks can be one of three types:
The lock mode is determined by the DML statement according to the following:
DB2 does not allow update (UPDATE/INSERT/DELETE) of uncommitted changes by other users. Reading of uncommitted data is allowed only if an isolation level of UR is specified in the SQL WITH clause or as a BIND option.
The duration of a lock is the length of time the lock is held. It can vary depending on the type of lock, the mode of the lock, and when the process chooses to release it.
Table, Partition, and Table Space Lock Durations
Lock duration for table spaces and table locks is determined by the BIND ACQUIRE and RELEASE parameters. ACQUIRE determines when table, table space, or partition locks are taken:
RELEASE determines when table, table space, or partition locks are released:
An exception applies when a cursor has been defined using the WITH HOLD option. The locks needed to maintain the position of the cursor are then held until the next commit after the cursor is closed (RELEASE COMMIT) or until the application deallocates its resources (RELEASE DEALLOCATE).
Page or Row Lock Durations
Lock duration for pages and rows defines the length of time that the lock will be held: the period between acquiring and releasing the lock. You can control how long locks are held by means of commit scopes, program bind parameters, and system parameters. To maximize concurrency, locks should not be held longer than necessary, using a large lock for the duration of the process; nor should excessive memory and CPU overhead be consumed by taking an excessive number of smaller locks, such as taking several row locks over a period of time for the same process.
Isolation levels are set when a program is bound or when used in an individual SQL statement using the WITH clause. The isolation levels directly affect the duration that locks are held. The isolation levels that can be used either at bind time or with an individual SQL statement are
Of these, cursor stability, read stability, and repeatable read have to do with lock durations; uncommitted read has to do with overriding locks.
Cursor stability holds a lock on the row or page, depending on the lock size defined, only if the cursor is positioned on that row or page. The lock will be released when the cursor moves to a new row or page, but the lock will be held until a commit is issued if changes are being made to the data. This option allows for the maximum concurrency for applications that are accessing the same data but cannot allow uncommitted reads.
Read stability holds locks on all rows or pages qualified by stage 1 predicates for the application until the commit is issued. Nonqualified rows or pages, even though touched, are not locked. Uncommitted changes of other applications cannot be read, but if an application issues the same query again, any data changed or inserted by other applications will be read, as RS allows other applications to insert new rows or update rows that could fall within the range of the query. This option is less restrictive but similar in function to repeatable read.
Repeatable read holds a lock on all rows or pages touched by an application program since the last commit was issued, whether or not all those rows or pages satisfied the query. Repeatable read holds these locks until the next commit point, which ensures that if the application needs to read the data again, the values will be the sameno other process could update the locked data. This option is the most restrictive in terms of concurrency of applications and is the default.
Uncommitted read tells DB2 not to take any locks and to ignore other locks and allow read access to the locked data. This option allows an application to run concurrently with other processes, except mass deletes and utilities that drain all claim classes.
Table 16-3 compares various isolation levels.
The mode of the lock will also factor into the duration of the lock.
Share Lock Duration (S)
Using SELECT or FETCH without update intent will acquire this lock. It is released for plans bound with cursor stability (CS) when
Using the cursor WITH HOLD option will hold the locks over COMMIT or ROLLBACK processes in TSO and CAF. If no COMMIT is used in the program, the locks will be held until the cursor is closed. It is important to understand that the lock will be held on the previous row/page until DB2 gets the lock on the new one.
Update Lock Duration (U)
A FETCH on a cursor that specifies FOR UPDATE OF acquires this lock. UPDATE and DELETE statements without a cursor also acquire this lock. UPDATE and DELETE statements cause an implicit SELECT to occur first, matching the conditions in the WHERE clause. During this SELECT, DB2 places a U lock on the page accessed, before completing the UPDATE or DELETE. DB2 releases the lock for plans bound with CS when
Using the cursor WITH HOLD option in TSO will hold the lock over COMMIT and ROLLBACK. However, the COMMIT will demote the U lock back to an S lock. If an update is to occur, the lock will be promoted to an X lock before changing the data page.
Exclusive Lock Duration (X)
Exclusive locks are released when
The lock will be held over COMMIT and ROLLBACK in TSO and CAF when using the cursor WITH HOLD option. However, the COMMIT will demote the X lock back to an S lock.
Table 16-4 lists some system parameters that are used to control various aspects of locking.