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

DB2 Locking, Part 5: Lock Suspensions, Timeouts, and Deadlocks

The longer a lock is held, the greater the potential impact to other applications. When an application requests a lock that is already held by another process, and the lock cannot be shared, that application is suspended. A suspended process temporarily stops running until the lock can be acquired. Lock suspensions can be a significant barrier to acceptable performance and application availability.

When an application has been suspended for a pre-determined period of time, it will be terminated. When a process is terminated because it exceeds this period of time, it is said to timeout. In other words, a timeout is caused by the unavailability of a given resource. For example, consider the scenario depicted in Figure 1.

Figure 1. A Timeout Occurs

If Program 2, holding no other competitive locks, requests a lock currently held by Program 1, DB2 tries to obtain the lock for a period of time. Then it quits trying. This example illustrates a timeout. This timeout scenario is also applicable to row locks, not just page locks.

The length of time a user waits for an unavailable resource before being timed out is determined by the IRLMRWT DSNZPARM parameter. You also can set this period of time by using the RESOURCE TIMEOUT field on the DB2 installation panel DSNTIPI.

When a lock is requested, a series of operations is performed to ensure that the requested lock can be acquired (see Figure 2). Two conditions can cause the lock acquisition request to fail: a deadlock or a timeout.

Figure 2. Processing a Lock Request

A deadlock occurs when two separate processes compete for resources held by one another. DB2 performs deadlock detection for both locks and latches. For example, consider the following processing sequence for two concurrently executing application programs:

Figure 3. A Deadlock Occurs

A deadlock occurs when Program 1 requests a lock for a data page held by Program 2, and Program 2 requests a lock for a data page held by Program 1. A deadlock must be resolved before either program can perform subsequent processing. DB2’s solution is to target one of the two programs as the victim of the deadlock and deny that program’s lock request by setting the SQLCODE to -911. This deadlocking scenario is also applicable to row locks, not just page locks. A graphic depiction of a deadlock is shown in Figure 4.

Figure 4. The Deadlock

The length of time DB2 waits before choosing a victim of a deadlock is determined by the DEADLOK IRLM parameter. You also can set this parameter using the RESOURCE TIMEOUT field on the DB2 installation panel DSNTIPJ.

IDUG NA 2013 in Orlando, Florida

Just a short blog post to let everybody know that I will be in Orlando next week for the IDUG DB2 Tech Conference. I'll be delivering an education seminar (DB2 Developer's Guide Comes Alive!) on Monday and giving two presentations on Thursday...

On Tuesday and Wednesday I'll be roaming around the conference, attending sessions, and looking to say hello to old friends and colleagues.

Hope to see you there!

IDUG NA 2013

DB2 Locking, Part 4: Page and Row Locks

In the first three installments of this series on DB2 locking we have looked ata broad overview of locking (part 1), table and table space locks (part 2) and the difference between locks and latches (part 3). Today we will move ahead and discuss page and row locking.

Page Locking

The types of page locks that DB2 can take are outlined in Table 1. S-locks allow data to be read concurrently but not modified. With an X-lock, data on a page can be modified (with INSERT, UPDATE, or DELETE), but concurrent access is not allowed. U-locks enable X-locks to be queued, whereas S-locks exist on data that must be modified.

Table 1. Page Locks

As with table space locks, concurrent page locks can be acquired but only with compatible page locks. The compatibility matrix for page locks is shown in Table 2.

Table 2. Page Lock Compatibility Matrix

When are these page locks taken? Page locks can be acquired only under the following conditions:

• The DDL for the object requesting a lock specifies LOCKSIZE PAGE or LOCKSIZE ANY.
• If LOCKSIZE ANY was specified, the NUMLKTS threshold or the table space LOCKMAX specification must not have been exceeded. You learn more about these topics later in this section.

Keep in mind, though, that if ISOLATION(RR) was used when the program was bound, the optimizer might decide not to use page locking even if the above criteria are met.

If all these factors are met, page locking progresses as outlined in Table 3. The type of processing in the left column causes the indicated page lock to be acquired for the scope of pages identified in the right column. DB2 holds each page lock until it is released as specified in the ISOLATION level of the plan requesting the particular lock. Page locks can be promoted from one type of lock to another based on the type of processing that is occurring. A program can FETCH a row using a cursor with the FOR UPDATE OF clause, causing a U-lock to be acquired on that row’s page. Later, the program can modify that row, causing the U-lock to be promoted to an X-lock.

Table 3. How Page Locks Are Acquired

Row Locks

The smallest piece of DB2 data that you can lock is the individual row. The types of row locks that DB2 can take are similar to the types of page locks that it can take. Refer back to Table 1 and simply replace Page with Row. So row locks act like page locks, only on a smaller granularity (that is, on rows instead of pages). 

S-locks allow data to be read concurrently but not modified. With an X-lock, you can modify data in that row (using INSERT, UPDATE, MERGE, or DELETE), but concurrent access is not allowed. U-locks enable X-locks to be queued, whereas S-locks exist on data that must be modified.

Once again, concurrent row locks can be acquired but only with compatible row locks. Table 2 works the same way for row locks as it does for page locks. 

When are these row locks taken? Row locks can be acquired when the DDL for the object requesting a lock specifies LOCKSIZE ROW. (Although it is theoretically possible for LOCKSIZE ANY to choose row locks, in practice I have yet to see this happen.) Again, we can use an earlier Table (Table 3) replacing the word page with the word row to see how row locking progresses. The type of processing in the left column causes the indicated row lock to be acquired for the scope of rows identified in the right column. A row lock is held until it is released as specified by the ISOLATION level of the plan requesting the particular lock.

Row locks can be promoted from one type of lock to another based on the type of processing that is occurring. A program can FETCH a row using a cursor with the FOR UPDATE OF clause, causing a U-lock to be acquired on that row. Later, the program can modify that row, causing the U-lock to be promoted to an X-lock.

Page Locks Versus Row Locks

The answer to the question of whether to use page locks or row locks is, of course, “It depends!” The nature of your specific data and applications determine whether page or row locks are most applicable.

The resources required to acquire, maintain, and release a row lock are just about the same as the resources required for a page lock. Therefore, the number of rows per page must be factored into the row-versus-page locking decision. The more rows per page, the more resources row locking will consume. For example, a table space with a single table that houses 25 rows per page can consume as much as 25 times more resources for locking if row locks are chosen over page locks. Of course, this estimate is very rough, and other factors (such as lock avoidance) can reduce the number of locks acquired, and thereby reduce the overhead associated with row locking. However, locking a row-at-a-time instead of a page-at-a-time can reduce contention. Row locking almost always consumes more resources than page locking. Likewise, if two applications running concurrently access the same data in different orders, row locking might actually decrease concurrent data access.

You must therefore ask these questions:

• What is the nature of the applications that access the objects in question? Of course, the answer to this question differs not only from organization to organization, but also from application to application within the same organization.
• Which is more important, reducing the resources required to execute an application or increasing data availability? The answer to this question will depend upon the priorities set by your organization and any application teams accessing the data.

As a general rule of thumb, favor specifying LOCKSIZE PAGE, as page locking is generally the most practical locking strategy for most applications. If you’re experiencing severe contention problems on a table space that is currently using LOCKSIZE PAGE, consider changing to LOCKSIZE ROW and gauging the impact on performance, resource consumption, and concurrent data access. Alternatively, you also might choose to specify LOCKSIZE ANY and let DB2 choose the type of locking to be performed.

Note

Note: A possible alternative to row locking is to specify MAXROWS 1 for the table space and use LOCKSIZE PAGE (or LOCKSIZE ANY), instead of LOCKSIZE ROW. 

DB2 Locking, Part 3: Locks Versus Latches

So far in this series on DB2 locking we have offered up a broad overview of what locking is and then delved into the world of table and table space locks. In this short entry, before we tackles page and row locks, we are going to look at the difference between a lock and a latch.

A true lock is handled by DB2 using the IRLM. The IRLM, or internal resource lock manager, is both a separate subsystem and an integral component of DB2. As its name implies, it manages locks for DB2.

However, whenever doing so is practical, DB2 can lock resources without going to the IRLM. This type of lock is called a latch. True locks are always set in the IRLM. Latches, by contrast, are set internally by DB2, without going to the IRLM.

When a latch is taken instead of a lock, it is handled in the Buffer Manager by internal DB2 code; so the cross-memory service calls to the IRLM are eliminated. Latches are usually held only briefly—for a shorter duration than locks. Also, a latch requires about one-third the number of instructions as a lock. Therefore, latches are more efficient than locks because they avoid the overhead associated with calling an external address space. Latches are used when a resource serialization situation is required for a short time. Both latches and locks guarantee data integrity. 

In subsequent blog entries, any usage of the term lock generically, refers to both locks and latches.