Db2 for Z/OS Locking (DRAIN S, CLAIM S, LOCK S, LATCH S) Overview

Db2 for z/OS Locking (DRAIN’s, CLAIM’s, LOCK’s, LATCH’s) Overview

One of the two most important synchronous processes within the Db2 for z/OS DBMS (Data Base Management System) is the locking facility (the other is the Db2 log facility). Db2 employs locks to control access to data. This locking process is critical to the enforcement of basic tenets of the DBMS such as Isolation, Concurrency, Consistency, and Data Integrity.

Closely related to the locking topic in Db2 are the utility processes known as DRAIN’s and CLAIM’s. Also, closely related to the locking topic in Db2 is the concept of a latch. While a DRAIN is technically thought of as a lock, a CLAIM can be thought of as a counter which may behave like a lock. A latch can be thought of as code (latch code) that can also behave like a lock.

This paper is an accumulation of notes and observations pertaining to the Db2 for z/OS locking behavior of DRAIN’s, CLAIMS’s, LOCK’s, and LATCH’s. A wide variety of Db2 for z/OS topics all closely related to locking within Db2 will also be referenced. The paper is written at a moderately technical level, and assumes that the reader has a certain minimum knowledge of Db2 for z/OS processing.

For the remainder of this discussion, the term Db2 will mean Db2 for z/OS (as opposed to Db2 LUW, etc.). This discussion is exclusively for Db2 for z/OS, where the term Db2 is concerned and used. References to z/OS are exclusively for those services and programs executing in a z/OS operating system.

DISCLAIMER: The following discussion is a personal interpretation of the IBM provided documentation on these topics. Because interpretations of technical documentation may vary from person to person, and are therefore subject to scrutiny, the user should always rely on IBM documentation for the definitive and authoritative explanation of each topic.

This paper is as of Db2 12 for z/OS and z/OS 2.2, and was written on Dec. 19, 2017 by:

Kurt Bohnert

Manager, Db2 Systems Programming

Rocket Software, Inc.

This paper is separated into fourteen (14) sections of notes, as follows:

Opening Notes
Latches
Lock SIZE, MODE, DURATION
BIND parameters ACQUIRE and RELEASE
BIND parameter ISOLATION
Cursor With Hold (CWH)
Lock Avoidance
Lock Escalation
Timeout
Deadlock
DRAINS and CLAIMS
Skipped Locked Rows
Optimistic Locking
Closing Notes

Opening Notes

Logging (via the MSTR TCB) and locking (via the IRLM TCB) are considered by some to be two of the most important synchronous processes in Db2 for z/OS.

Locks control access to data. We have “some” control over locks. Resources (such as locks) required to execute an application (i.e. PLAN/PACKAGE) are acquired and released based on a variety of factors, most notably BIND options.

Concurrencyis the ability of > 1 application (UOW/UOR) to access the same data at the same time while maintaining data integrity.

Isolationis the degree to which the operations of one UOW are isolated from the effects of other concurrent update operations.

Latches

A latch is essentially code sometimes referred to in Db2 as the Latch Manager (Latch Management). Latching is used for short-term serialization of internal Db2 resources. It is performed by the Db2 agent services manager subcomponent and the Buffer Manager. Latch management controls concurrent access to internal Db2 resources that cannot be simultaneously updated.

A latch can inhibit access to resources (e.g. data), in much the same way as a lock (this is called “latch contention”).

Latch contention occurs when a thread must wait toaccess data, because the latch code determines that the requested data is “not in a consistent state”. The cause of the inconsistent state can be many things.

A latch is approx. 60 code instructions, while a lock is approx. 700 code instructions. It is usually transparent to the end user, and there are few human actions that can be taken to alleviate latch contention.

LOCK SIZE, DURATION, MODE

Locking essentially occurs at two (2) levels in Db2 data resources:
Page Set level (on an entire page set or table of a Segmented TS)
Page/Row level (at the page or row level)

A lock willusually be enforced at one of those two levels. A table level lock only occurs when a LOCK TABLE statementis issued on one table in a Segmented TS (see below for more on LOCK TABLE).

Note: For the remainder of this paper, if a lock is hi-lighted in green, it is a page/row level lock. If hi-lighted in yellow, it is a page set level lock. If it is not hi-lighted, it either does not apply, or can be at either level.

There are three (3) classifications of locks:
SIZETABLESPACE, TABLE …PAGE, ROW … ANY
MODEIS, IX, SIX, S, U, X, DRAIN … S, U, X
DURATIONACQUIRE, RELEASE …ISOLATION

These classifications are at the Page Set level andPage/Row level.

SIZEdetermines the level (Page Set vs. Page/Row) of locking, and is definedin the CREATE TABLESPACE keyword LOCKSIZE:

CREATE TABLESPACE …LOCKSIZE ANY|PAGE|ROW|TABLE|TABLESPACE

Note there is also a LOCKSIZE LOB, used exclusively for LOB TS’s, and not discussed in this paper. See IBM Db2 documentation for more on LOCKSIZE(LOB).

Use LOCKSIZEANY until you have a reason not to. LOCKSIZE ANY is the default, and requests Db2 to select the LOCKSIZE on your behalf. You “usually” get PAGE level locking with LOCKSIZE ANY.

Row level locking is better for random access applications, not so much for sequential access applications.

MODErefers to the type of lock taken (at either the Page Set level or at thePage/Row level discussed above).

Db2 Lock MODE’s:

Page Set (or Table) level – IS, IX, SIX, S, U, X, DRAIN

------

| PAGE/ROW PAGE/ROW |

| ------|

| | | | | |

| | S, U, X | | S, U, X | |

| | | | | |

| ------. . . more . . . |

| pages |

------

IS Intent Share

IX Intent Exclusive

SIX Share with Intent Exclusive (see below for details)

S Share

X Exclusive

U Update (see below for details)

DRAIN Utility DRAIN Lock (see below for details)

IS: This agent (i.e. thread) intends to take ‘S’ locks at the Page/Row level, within this Page Set (or Table). Other agents may also take IS and/or IX locks on this Page Set (or Table). See below for more on “intent” locks.
IX: This agent intends to take ‘X’ locks at the Page/Row level, within this Page Set (or Table). Other agents may also take IS and/or IX locks on this Page Set (or Table).
SIX: This agent intends to take ‘X’ locks at the Page/Row level, within this Page Set (or Table), and other agents are restricted to Share locking (‘S’ locks … Read Only) only. See below for more on the SIX lock.

S: An agent has placed the entire Page Set (or Table) in Read Only (RO) status for all agents accessing this Page Set (or Table).
S: An agent has placed this one Page or Row in Read Only (RO) status for all agents accessing this one Page or Row.
X: An agent has taken exclusive control of the entire Page Set (or Table). No other agent may access this Page Set (or Table) until this ‘X’ lock is released.
X: An agent has taken exclusive control of this one Page or Row. No other agent may access thisone Page or Row until the ‘X’ lock is released.

U and U: See below for a discussion on Update locks.

DRAIN: Db2 utility DRAIN lock … see below for a discussion on DRAIN locks.

There is a concept in Db2 locking referred to as “intent locking”. Intent locking occurs at the Page Set (or Table) level only. An “intent” lock (IS, IX, SIX) is a lock which essentially states that a thread intends to take page/row level locks within this page set (or Table). So “Intent” locks occur at the page set level (or at the Table level in a segmented TS).

Intent IS, IX, SIX means that the lock owner (UOW) may get a page/row level lock (‘S’, ‘U’, ‘X’) at some point in the future.

An intent level lock on (the old) simple TS applied to all tables in the TS.
An intent level lock on segmented TS applies only to one table in the TS.
An intent level lock on PART TS applies to the PART Page Set.

‘X’ and ‘S’ locks are acquired at the Page/Row level only after ‘IX’ or ‘IS’ locks are acquired at the Page Set (or Table) level.

‘X’ and ‘S’ locks are acquired at the page set level only as the result of one of the following:
LOCK ESCALATION (see below)
LOCK TABLE SQL statement (see below)
LOCKSIZE TABLE|TABLESPACE (see above)

SQL statement LOCK TABLE is used to create a Page Set (or Table) level lock:

LOCK TABLE table-name PART n (PART is optional)

IN SHARE MODE | IN EXCLUSIVE MODE

LOCK TABLE overrides all other Db2 rules for choosing lock SIZE and MODE.

If LOCK TABLE is used, it always results in an ‘X’ or ‘S’ lockat the Page Set or Table level (except for ‘SIX’ … see below for SIX). LOCK TABLE never results in a lock at the Page/Row level.

LOCK TABLE on a segmented TS, only the segments for the one table are locked. If TS not segmented, the lock is a full TS|PART level lock.

A ‘SIX’ lock is a page set level lock. It is an ‘S’ lock –plus- an ‘IX’ lock. This essentially says that all readers are welcome, but only “I” can update.

A ‘SIX’ lock blocks all other locks except another ‘IS’ lock at the page set level and a subsequent ‘S’ lock at the page/row level.
The ‘IX’ part of the ‘SIX’ lock allows the owning application (the owner of the ‘SIX’ lock) to take ‘X’ locks at the page/row level to perform updates.

One way to get a ‘SIX’ lock:
Open a cursor FOR UPDATE to get the ‘IX’ lock.
LOCK TABLE … IN SHARE MODE to get the ‘S’ lock.
Now, anyone can read but only the ‘SIX’ lock owner can update.

Db2 frequently uses ‘SIX’ locks in its code for accessing catalog objects.

A ‘U’ lock (either ‘U’ or ‘U’)is used in place of an ‘X’ lock. Itbehaves virtually identical to an ‘S’ lock, but may provide greater concurrency than an ‘X’ lock in READ for UPDATE applications.

A ‘U’ Lock behaves just like an ‘S’ Lock except it also blocks other ‘U’ Locks and can be promoted to an ‘X’ Lock. Note however, that the upgrade from ‘U’ to ‘X’ can be expensive.

According to IBM doc, the ‘U’ lock “may” provide for greater concurrency than an ‘X’ lock, if the application performs frequent updates. This is because the ‘U’ lock will stop other ‘U’ locks but will not stopother ‘S’ locks. An ‘X’ lock always stops both ‘U’ and ‘S’ locks.

See the ZPARM’s below which control use of a ‘U’ LOCK. Remember … in both ZPARM’s below, it is during a READ for UPDATE/DELETE that the ZPARM takes effect.

‘U’ Lock example (with XLKUPDLT = NO):

DELETE FROM table-name WHERE C1 = ‘A’ AND C2 = ‘A’;

C1 is in an index. C2 is not in an index. Db2 does not know whether a row is to be deleted until the row is read (cannot tell from the index because of C2). If XLKUPDLT = NO, Db2 starts with a ‘U’ Lock and upgrades to ‘X’ if C2 = ‘A’. If XLKUPDLT = YES Db2 starts with an ‘X’ lock on each row retrieved via the index before applying the C2 predicate.

The ‘U’ lock is controlled by ZPARM(s)XLKUPDLTand RRULOCK.

XLKUPDLTYES|NOShould Db2 use an ‘X’ lock (at the page/row

TARGETlevel), for a “searched”update/delete?

The SQL statement is an UPDATE/DELETE using a predicate for row selection (i.e. row screening … a predicate tells Db2 to interrogate rows to ascertain if the row qualifies for the update/delete).

YES means Db2 always uses an ‘X’ lock while scanning for qualifying rows.

NO means Db2 uses a‘U’ lockwhile scanning for qualifying rows. For any qualifying rows, the page/row lock is promoted to an ‘X’ lock to perform the update/delete. Use NO to improve concurrency, it is the default.

TARGET combines YES and NO behaviors. Db2 uses ‘X’ (YES) for the table targeted for update/delete, and ‘S’ for all other tables in the query.

RRULOCKYES|NOSpecify whether to use a‘U’lockinsteadof an ‘S’

lock (at the page/row level),when using RR or RS ISOLATION to OPEN (SELECT)a CURSOR FOR UPDATE. The cursor is a SELECT FOR UPDATE.

NO means Db2 uses an ‘S’ lock (normal behavior) as the cursor is scrolled, until an update is issued via the cursor, in which case the lock is promoted to an ‘X’ lock for that page/row.

YES means Db2 uses a ‘U’ lock as the cursor is scrolled, until an update is issued via the cursor, in which case the lock is promoted to an ‘X’ lock for that page/row. YES is the default.

Lock “Promotion” is the action of exchanging one lock on a resource for a more restrictive lock on the same resource (e.g. ‘U’ to ‘X’).

When promoting a lock, Db2 must first wait for all pre-existing incompatible locks held by other UOW’s to be released. Timeout (ZPARM IRLMRWT) applies during this wait (see below for more on IRLMRWT).

Examples of lock promotion:

‘S’ to ‘X’, ‘U’ to ‘X’, ‘IS’ to ‘S’, ‘IX’ to ‘X’

DURATION: Lock duration is the time in which a UOW holds a lock on aresource.

Lock duration is essentially governed by the BIND options ACQUIRE/RELEASE (Page Set level locks) and ISOLATION (PAGE/ROW level locks).

See notes on ACQUIRE, RELEASE, and ISOLATION below for details.

Do not confuse LPI (below) with SPL (below).

LPI (Logical Partition Independence) occurs on NPI’s (Non-Partitioned Indexes), when you use the PART option in a utility (i.e. you run a utility against one PART of a PART TS on which an NPI exists). If there is an NPI (Non-Partitioned Index) on the object on which the utility is run, LPI attempts to separate the logical partitions of the NPI to the greatest degree possible for utility processing. Note that there are some cases (e.g. online utilities) where this may not be possible. LPI occurs whenever possibleautomatically,whenever you use the PART keyword in a utility. See below in this paper for details on LPI.

SPL (Selective Partition Locking) . . . Db2 takes intent level locks on only the PART’s accessed. It’s enabled automatically by Db2.
Note that PART locks (via SPL) are page set level locks, and are held to the rules applicable to page set level locks, in all cases.

ACQUIRE and RELEASE (BIND Options)

ACQUIRE and RELEASE BIND options apply to “page set level” locksonly (TS, PART, or a TABLE in segmented TS). These options dictate when the page set level lock is taken (ACQUIRE) and when it is released (RELEASE).

ACQUIRE(ALLOCATE|USE):
ALLOCATE:This option is deprecated as of Db2 V10.
USE:Locks are taken when the resource is accessed (used).
RELEASE(COMMIT|DEALLOCATE):
COMMIT:Release locks at COMMIT.
DEALLOCATE:Release locks when the thread terminates.
ACQUIRE(ALLOCATE) was deprecated in Db2 V10 and ACQUIRE(USE) is used always.

There is no longer an ACQUIRE option for packages. A package always acquires resources when it first uses them, as if you specified ACQUIRE(USE).
ACQUIRE(USE) RELEASE(COMMIT) are the defaults, and are theleast restrictive so they provide the greatest concurrency.

ACQUIRE(USE) RELEASE(COMMIT) provide the greatest concurrency, but if the application does frequent commits and then re-accesses the same objects, more CPU is required to re-acquire the locks (again and again possibly).

ACQUIRE(USE) RELEASE(DEALLOCATE) is the most efficient, unless you do not need to re-access the data after COMMIT. In such a case, you would be better served to use ACQUIRE(USE) RELEASE(COMMIT).

Dynamic SQL alwaysuse the defaults ACQUIRE(USE) RELEASE(COMMIT).

RELEASE(DEALLOCATE) only keeps “intent” Page Set level locks (IS, IX) at COMMIT. Page set ‘X’ and ‘S’ locks and all page/row locks are alwaysreleased at COMMIT (at one time, Cursor With Hold (CWH) might have kept a lock through RELEASE(DEALLOCATE), but no longer … see CWH discussion below for details).

RELEASE also dictates when certain other resources are released:
Cursors without CWH (are they closed or not closed at COMMIT)
LocalDYN SQL cache (Thread level DYN SQL caching)
Global Temp Tables (Declared and Created)
xProcs (sproc, iproc, uproc)
Dynamic Detection (i.e. Dynamic Prefetch)
Index look-aside

Because ACQUIRE/RELEASE essentially dictate the duration of intent level locks, it is common to see multiple IS and IX locks for different concurrent UOW’s on the same page set, and they might be seen within a DRAIN’d object during some utility execution (‘Intent’ locks can persist through some utility executions).

Try to use RELEASE(COMMIT) for OLTP for performance.
Generally, the only time you would use RELEASE(DEALLOCATE) is for batch processing or for High Performance DBAT’s (see below).

Db2 10 introduced “High Performance DBAT’s”. They are implemented via RELEASE(DEALLOCATE). V9 did not allow DBAT’s to use this.
DDF Threads stay active after COMMIT instead of going to the reuse pool. See IBM DB2 doc for details on High Performance DBAT’s.

ISOLATION (Bind Parameter)

Isolation is the degree to which the operations of one UOW are isolated from the effects of other concurrent update operations. The BIND parm ISOLATION dictates the duration that Read-Only (RO) page/row locks are held in CURSOR processing.

ISOLATION addresses the situation where one UOW wants to read data and another UOW wants to update the same data.
ISOLATION only applies to PAGE/ROW level locks (‘S’, ‘U’, or ‘X’ locks on the page/row only). ISOLATION never applies to “intent” level locks (i.e. page set level locks).

ISOLATION only applies to Read Only (RO) cursors. If you update a row via a cursor, the lock is always held to commit, regardless of ISOLATION level.

When using singleton SQL (non-cursor SQL), UPDATE/INSERT/DELETE always hold locks to commit. Singleton SQL SELECT may or may not take a lock depending on issues such as Lock Avoidance (see below) and ISOLATION UR (see below).

ISOLATION dictates the duration that a RO (SELECT)page/row lock is held in CURSOR processing:

CURSOR SELECT (RO) relies on ISOLATION to determine when the page/row lock should be released.
CURSOR UDPATE/DELETE always hold page/row locks to COMMIT.

PAGE/ROW level locks are always released at commit.

The default for ISOLATION is Cursor Stability (CS).

There are four (4) ISOLATION levels specified in the BIND command:
RR Repeatable Read
RS Read Stability
CS Cursor Stability
UR Uncommitted Read

RRPage/row locks in cursors are held to commit. No inserts allowed within the cursor while the locks are held.