Locking Data

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.

Lock Attributes

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.

Lock Sizes

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

• Table space

• Table in a segmented table space

• Partition

• Page

• Row

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.

Partition Locking

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:

• Plan/package bound ACQUIRE(ALLOCATE)

• Table space defined LOCKSIZE TABLESPACE

• LOCK TABLE statement used without PART option

Page Locks

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.

Row Locks

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.

NOTE

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.

LOB Locking

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.

NOTE

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.

Lock Modes

DB2 has the following lock modes:

• IS (intent share)

• S (share)

• IX (intent exclusive)

• U (update)

• SIX (share with intent exclusive)

• X (exclusive)

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 16-1. Table Spaces, Tables, and Partitions

Lock Mode	IS	IX	S	U	SIX	X
IS	Yes	Yes	Yes	Yes	Yes	No
IX	Yes	Yes	No	No	No	No
S	Yes	No	Yes	Yes	No	No
U	Yes	No	Yes	No	No	No
SIX	Yes	No	No	No	No	No
X	No	No	No	No	No	No

Table 16-2. Pages and Rows

Lock Mode	S	U	X
S	Yes	Yes	No
U	Yes	No	No
X	No	No	No

Table or Table Space Locks

When using page or row locks, the table or table space lock will be IS/IX/SIX:

IS

• Lock owner wants to read only (may lock pages/rows)

• Allow read of lock owner's pages/rows and read/update of all other pages/rows

• Lock owner has share locks on the pages

• Allows other users to read and update

IX

• Lock owner wants to update (may lock pages/rows)

• Allow read/update of all other pages/rows

• Lock owner can have share, update, or exclusive locks on the pages

• Other users still able to read and update

SIX

• Lock owner wants to read (will not lock pages/rows)

• Lock owner wants to update (may lock pages/rows)

• Allow read of all other pages/rows

• Lock owner can have share, update, or exclusive locks on the pages

• Other users still able to read, but no other user can update

NOTE

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:

S

• Lock owner wants to read only

• Others are allowed read only

• No one can update

U

• Lock owner wants to read with possibility of update

• Others allowed read only

• No other user can update

X

• Lock owner wants to update

• Other access not allowed

• User needs exclusive use of the table or table space

• No other user allowed

Page or Row Locks

Page locks can be one of three types:

S

• Restricts other users to read-only use of this page

• S or U locks can be acquired by other users

• Using SELECT or FETCH without update intent will acquire this lock

U

• Allows the user to go to the update step, if required

• Other applications can get S locks but nothing else

X

• User has updated the data on the page; no other user can get any page lock on the page

The lock mode is determined by the DML statement according to the following:

SELECT...	S
SELECT...FOR UPDATE OF...	U
UPDATE/DELETE/INSERT	X

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.

Lock Durations

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:

• ALLOCATE, the maximum required lock, is taken on all the objects in the plan or package when the first SQL statement is issued.

• USE, the required lock, is taken on the involved objects when an SQL statement is issued.

RELEASE determines when table, table space, or partition locks are released:

• DEALLOCATE: at the end of the program

• COMMIT: at COMMIT

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

• UR (uncommitted read)

• CS (cursor stability)

• RS (read stability)

• RR (repeatable read)

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.

Table 16-3. Comparison of Isolation Levels

Access by Other Applications	UR	CS	RS	RR
Can the application see uncommitted changes made by other application processes?	Yes	No	No	No
Can the application update uncommitted changes made by other application processes?	No	No	No	No
Can the reexecution of a statement be affected by other application processes?	Yes	Yes	Yes	No
Can updated rows be updated by other application processes?	No	No	No	No
Can updated rows be read by other application processes that are running at an isolation level other than UR?	No	No	No	No
Can updated rows be read by other application processes that are running at the UR isolation level?	Yes	Yes	Yes	Yes
Can accessed rows be updated by other application processes?	Yes	No	No	No
Can accessed rows be read by other application processes?	Yes	Yes	Yes	Yes

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

• A process issues an SQL COMMIT or ROLLBACK

• An IMS process issues the next SYNCH or ROLB

• A CICS synch point call occurs

• A noncursor SELECT statement completes without update

• The cursor position moves to a new row/page without update

• The cursor is closed

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

• A process issues an SQL COMMIT or ROLLBACK

• An IMS process issues the next SYNCH or ROLB

• The next synch point call occurs in CICS

• A noncursor SQL statement has completed without update

• The cursor position moves to a new page without update

• The cursor is closed without an update having occurred

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

• A process issues an SQL COMMIT or ROLLBACK

• An IMS process issues the next SYNCH or ROLB

• The next synch point call occurs in CICS

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.

System Parameters

Table 16-4 lists some system parameters that are used to control various aspects of locking.

Table 16-4. System Parameters for Locking

Parameter	Description
RECURHL	The use of RECURHL = YES can help with concurrency, allowing DB2 to release the locks that are held by a cursor defined WITH HOLD.
IRLMRWT	The number of seconds that a transaction will wait for a lock before a timeout is detected. The IRLM uses this parameter for timeout and deadlock detection. Most shops take the default of 60 seconds; if detection must occur sooner so that the applications are not incurring excessive lock wait times, it is set lower. If timeouts or deadlocks are hit often, the application is reviewed and tuned.
XLKUPDLT	New with version 6, allows you to specify the locking method to use when a searched UPDATE or DELETE is performed. The default is NO, best for concurrency, which says that DB2 will use an S or U lock when scanning qualifying rows and then upgrade to an X lock when a qualifying row is found. The value of YES is useful in a data sharing environment when the searches involve an index, because it takes an X lock on qualifying rows or pages.
NUMLKTS	The maximum number of locks on an object. If you are turning off lock escalation (LOCKMAX 0), you will need to increase this number. If you are using LOCKMAX SYSTEM, the value here will be the value for SYSTEM.
NUMLKUS	The maximum number of page or row locks that a single application can have held concurrently on all table spaces: data pages, index pages, subpages, and rows. If you specify 0, there is no limit on the number of locks. You want to be careful with 0, because if you turn off lock escalation and do not commit frequently enough, you could run into storage problems. (DB2 uses 250 bytes for each lock.)
URCHKTH	Finds long-running units of work that are not committing; specifies the number of checkpoints that should occur before a message is issued identifying a long-running unit of work.
LRDRTHLD	Can be used to proactively identify reader threads that have exceeded the user-specified time limit threshold without COMMIT. When this value is nonzero, DB2 records time a task holds a read claim. When passed a specified number of minutes, an IFCID 313 record is written.