DB2 Locking, Part 6: Claims, Drains, and Partition Independence

DB2 augments resource serialization using claims and drains in addition to transaction locking. The claim and drain process enables DB2 to perform concurrent operations on multiple partitions of the same table space.

Claims and drains provide another “locking” mechanism to control concurrency for resources between SQL statements, utilities, and commands. But do not confuse the issue: DB2 continues to use transaction locking, as well as claims and drains.

As with transaction locks, claims and drains can timeout while waiting for a resource.

Claims

DB2 uses a claim to register that a resource is being accessed. The following resources can be claimed:

• Segmented table spaces
• A single data partition of a partitioned table space (classic or universal range-partitioned)
• A non-partitioned index space
• A single index partition of a partitioned index (of either the partitioning index or a DPSI)

Think of claims as usage indicators. A process stakes a claim on a resource, telling DB2, in effect, “Hey, I’m using this!”A claim is a notification to DB2 that an object is being accessed. Claims prevent drains from occurring until the claim is released, which usually occurs at a commit point.

Claims prevent drains from acquiring a resource. A claim is acquired when a resource is first accessed. Claims are released at commit time, except for cursors declared using the WITH HOLD clause or when the claimer is a utility.

Multiple agents can claim a single resource. Claims on objects are acquired by the following:

• SQL statements (SELECT, INSERT, UPDATE, MERGE, DELETE)
• DB2 restart on INDOUBT objects
• Some utilities (for example, COPY SHRLEVEL CHANGE, RUNSTATS SHRLEVEL CHANGE, and REPORT)

Every claim has a claim class associated with it. The claim class is based on the type of access being requested, as follows:

• A CS claim is acquired when data is read from a package or plan bound specifying ISOLATION(CS).
• An RR claim is acquired when data is read from a package or plan bound specifying ISOLATION(RR).
• A write claim is acquired when data is deleted, inserted, or updated.

Drains

Like claims, drains also are acquired when a resource is first accessed. A drain acquires a resource by quiescing claims against that resource. Drains can be requested by commands and utilities. A drain is the act of acquiring a locked resource by quiescing access to that object.

Multiple drainers can access a single resource. However, a process that drains all claim classes cannot drain an object concurrently with any other process.

To more fully understand the concept of draining, think back to the last time that you went to a movie theater. Before anyone is permitted into the movie, the prior attendees must first be cleared out. In essence, this example illustrates the concept of draining. DB2 drains make sure that all other users of a resource are cleared out before allowing any subsequent access.

The following resources can be drained:

• Segmented table spaces
• A single data partition of a partitioned table space (classic or universal range-partitioned)
• A non-partitioned index space
• A single index partition of a partitioned index (of either the partitioning index or a DPSI)

A drain places drain locks on a resource. A drain lock is acquired for each claim class that must be released. Drain locks prohibit processes from attempting to drain the same object at the same time.

The process of quiescing a claim class and prohibiting new claims from being acquired for the resource is called draining. Draining allows DB2 utilities and commands to acquire partial or full control of a specific object with a minimal impact on concurrent access. Three types of drain locks can be acquired:

• A cursor stability drain lock
• A repeatable read drain lock
• A write drain lock

A drain requires either partial control of a resource, in which case a write drain lock is taken, or complete control of a resource, accomplished by placing a CS drain lock, an RR drain lock, and a write drain lock on an object.

You can think of drains as the mechanism for telling new claimers, “Hey, you can’t use this in that way!” The specific action being prevented by the drain is based on the claim class being drained. Draining write claims enables concurrent access to the resource, but the resource cannot be modified. Draining read (CS and/or RR) and write claims prevents any and all concurrent access.

Drain locks are released when the utility or command completes. When the resource has been drained of all appropriate claim classes, the drainer acquires sole access to the resource.

Claim and Drain Lock Compatibility

As with transaction locks, concurrent claims and drains can be taken, but only if they are compatible with one another. Table 1 shows which drains are compatible with existing claims... Table 2 shows which drains are compatible with existing drains:

Table 1. Claim/Drain Compatibility Matrix

Table 2. Drain/Drain Compatibility Matrix

Transaction Locking Versus Claims and Drains  

DB2 uses transaction locks to serialize access to a resource between multiple claimers, such as two SQL statements or an SQL statement and a utility that takes claims, such as RUNSTATS SHRLEVEL(CHANGE).

Claims and drains serialize access between a claimer and a drainer. For example, an INSERT statement is a claimer that must be dealt with by the LOAD utility, which is a drainer.

Drain locks are used to control concurrency when both a command and a utility try to access the same resource.

The Impact of Commit

It is vitally important that you design all your long-running application programs with a COMMIT strategy in mind. This means that after a period of execution, a COMMIT is issued. This guidance applies to read-only programs as well as to modification programs. Remember that claims are released at COMMIT, so a long-running read-only program that never commits can hold claims for extended periods, thereby causing concurrency issues, particularly for utilities (including online utilities such as REORG SHRLEVEL CHANGE).

The LRDRTHLD DSNZPARM, introduced with DB2 V10, can be used to identify processes that hold read claims for extended periods of time. The parameter can be used to set a threshold that when met, causes DB2 to write a trace record. The default is 10 minutes.

You also can use the -DISPLAY DATABASE command with the CLAIMERS keyword to display claim types and durations for specified database objects. For example, to show the claim information for the TSCUST01 table space in the DBCUST database, you could issue the following command:

-DISPLAY DATABASE(DBCUST) SPACENAM(TSCUST01) CLAIMERS