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!”
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
