DB2 Locking, Part 9: Avoid Bachelor Programming Syndrome

Unless you plan for, and issue regular COMMITs in your DB2 application programs, you will be causing locking problems. It is important for every programmer to issue COMMIT statements in all application programs where data is modified (INSERT, UPDATE, and DELETE). A COMMIT externalizes the modifications that occurred in the program since the beginning of the program or the last COMMIT. A COMMIT ensures that all modifications have been physically applied to the database, thereby ensuring data integrity and recoverability.

Failing to code COMMITs in a data modification program is what I like to call Bachelor Programming Syndrome — or in other words, fear of committing.

One important factor affecting the need for a COMMIT strategy is the amount of elapsed time required for the program to complete. The greater the amount of time needed, the more important it becomes to issue periodic COMMITs. A sound COMMIT strategy will reduce rollback time and reprocessing time in the event of program failure. It is a safe assumption that the elapsed time increases as the number of modifications increases.

Issuing COMMITs in an application program is important for three reasons:

1. If the program fails, all the modifications are backed out to the last COMMIT point. This process could take twice the time it took to perform the modifications in the first place—especially if you are near the end of a program with no COMMITs that performed hundreds or thousands of modification operations.
2. If you resubmit a failing program that issues no COMMITs, the program redoes work unnecessarily.
3. Programs using the repeatable read page locking strategy or the RELEASE(COMMIT) table space locking strategy hold their respective page and table space locks until a COMMIT is issued. If no COMMITs are issued during the program, locks are not released until the auto-COMMIT when the program completes, thereby negatively affecting concurrent access. This can cause lock timeouts and lock escalation.

If concurrent batch or online access uses uncommitted read (UR) isolation, sometimes referred to as dirty reads, COMMIT processing is irrelevant. This is so because uncommitted read isolation does not take any locks. However, most processing requires accurate data and, as such, will not use uncommitted read isolation.

If your program is running for a long time and has issued no COMMITs, your program will probably not suffer. However, others attempting to access the data that is locked by your program will suffer. This will manifest itself with lock timeout errors (such as -911 and -913 SQL return codes). If you see a lot of lock timeouts occurring it is probably a good bet that you have at least one long-running program (maybe more) that needs to have COMMIT logic added.

It is also good programming practice to issue periodic COMMITs in long-running read-only programs. Even though data is not being changed, some databae management systems, such as DB2 for z/OS, acquire read locks whenever data is accessed. A read lock can block other programs (such as utility operations) from running successfully. Therefore, failure to COMMIT, even in a read only program, can result in locking problems.

In some rare circumstances, you might be able to avoid issuing COMMITs in your database programs. But you should only consider doing so for programs that run very quickly, and therefore will not hold locks for a long duration.

Given the tremendous benefits to your DBMS environment that can be gained by assuring a proper COMMIT strategy, why would you ever consider avoiding COMMITs? Perhaps the simplest argument is that the code is easier to write without COMMITs. Additionally, recovery is simpler. When modification programs without COMMITs fail, you can generally restart them from the beginning because database changes have not been committed. Of course, this might not be true if you have modified data not controlled by the DBMS (in which case dual modifications may cause problems).

If you choose to forgo COMMITs in any application program it is wise to clearly document why there are no COMMITs being issued. That way, if the program is changed later rendering the original assumptions inaccurate, adding COMMITs can be considered using the original reasoning accompanied by the added processing changes.

I recommend that you plan to issue COMMITs in every batch program... even read only programs. The COMMIT will release claim requests, which can free up DB2 utilities to execute, thereby improving overall availability.

You can structure the logic so that the COMMIT processing is contingent on a parameter passed to the program. This approach enables an analyst to turn off COMMIT processing but ensures that all batch programs are prepared if COMMIT processing is required in the future.

DB2 Locking, Part 8: LOBs and Locking

When a row is read or modified in a table containing LOB columns, the application will obtain a normal transaction lock on the base table. The actual values for (most) LOBs are stored in a separate table space from the rest of the table data. The locks on the base table also control concurrency for the LOB table space. But DB2 uses locking strategies for large objects, too. A lock that is held on a LOB value is referred to as a LOB lock. LOB locks are deployed to manage the space used by LOBs and to ensure that LOB readers do not read partially updated LOBs.

Note: For applications reading rows using ISOLATION(UR) or lock avoidance, page or row locks are not taken on the base table. However, DB2 takes S-locks on the LOB to ensure that a partial or inconsistent LOB is not accessed.

Note: Inline LOBs, available as of DB2 V10, are treated like normal data and do not require LOB locking. 

One reason LOB locks are used is to determine whether space from a deleted LOB can be reused by an inserted or updated LOB. DB2 will not reuse the storage for a deleted LOB until the DELETE has been committed and there are no more readers on the LOB. Another purpose for locking LOBs is to prevent deallocating space for a LOB that is currently being read. All readers, including “dirty readers” acquire S-locks on LOBs to prevent the storage for the LOB they are reading from being deallocated. 

Types of LOB Locks

There are only two types of LOB locks:

• S-locks, or SHARE: The lock owner and any concurrent processes can SELECT, DELETE, or UPDATE the locked LOB. Concurrent processes can acquire an S-lock on the LOB.
• X-locks, or EXCLUSIVE: The lock owner can read or change the locked LOB, but concurrent processes cannot access the LOB.

Just like regular transaction locking, though, DB2 also takes LOB table space locks. If the LOB table space has a gross lock, DB2 does not acquire LOB locks. The following lock modes can be taken for a the LOB table space:

• S-lock, or SHARE: The lock owner and any concurrent processes can read and delete LOBs in the LOB table space. The lock owner does not need to take individual LOB locks.
• IS-lock, or INTENT SHARE: The lock owner can UPDATE LOBs to null or zero-length, or SELECT or DELETE LOBs in the LOB table space. Concurrent processes can both read and modify LOBs in the same table space. The lock owner acquires a LOB lock on any data that it reads or deletes.
• X-lock, or EXCLUSIVE: The lock owner can read or change LOBs in the LOB table space. The lock owner does not need to take individual LOB locks.
• IX-lock, or INTENT EXCLUSIVE: The lock owner and any concurrent process can read and change data in the LOB table space. The lock owner acquires an individual LOB lock for any LOB it accesses.
• SIX-lock, or SHARE WITH INTENT EXCLUSIVE: The lock owner can read and change data in the LOB table space. The lock owner obtains a LOB locks when inserting or updating. Concurrent processes can SELECT or DELETE data in the LOB table space (or UPDATE the LOB to a null or zero-length).

As with transaction locking, there is a hierarchical relationship between LOB locks and LOB table space locks (see Figure 1). If the LOB table space is locked with a gross lock, LOB locks are not acquired. 

Figure 1. The DB2 LOB locking hierarchy.

The type of locking used is controlled using the LOCKSIZE clause for the LOB table space. LOCKSIZE TABLESPACE indicates that no LOB locks are to be acquired by processes that access the LOBs in the table space. Specifying LOCKSIZE LOB indicates that LOB locks and the associated LOB table space locks (IS or IX) are taken. The LOCKSIZE ANY specification allows DB2 to choose the size of the lock, which is usually to do LOB locking.

Duration of LOB Locks

The ACQUIRE option of BIND has no impact on LOB table space locking. DB2 will take locks on LOB table spaces as needed. However, the RELEASE option of BIND does control when LOB table space locks are releases. For RELEASE(COMMIT), the LOB table space lock is released at COMMIT (unless WITH HOLD is specified or a LOB locator is held).

LOB locks are taken as needed and are usually released at COMMIT. If that LOB value is assigned to a LOB locator, the S-lock on the LOB remains until the application commits. If the application uses HOLD LOCATOR, the locator (and the LOB lock) is not freed until the first commit operation after a FREE LOCATOR statement is issued, or until the thread is deallocated. If a cursor is defined WITH HOLD, LOB locks are held through COMMIT operations. 

LOB Table Space Locking Considerations

Under some circumstances, DB2 can avoid acquiring a lock on a LOB table space. For example, when deleting a row where the LOB column is null, DB2 need not lock the LOB table space. 

DB2 does not access the LOB table space in the following instances:

• A SELECT of a LOB that is null or zero-length
• An INSERT of a LOB that is null or zero-length
• When a null or zero-length LOB is modified (by UPDATE) to a null or zero-length
• A DELETE for a row where the LOB is null or zero-length

DB2 Locking Part 7: Lock Avoidance, Related Issues, and Stuff

In today's blog entry, part 7 in our on-going series on DB2 locking, we will take a look at lock avoidance... as well as some other related things.

Lock Avoidance

Lock avoidance is a mechanism employed by DB2 for z/OS to access data without locking but also while maintaining data integrity. It prohibits access to uncommitted data and serializes access to pages. Lock avoidance improves performance by reducing the overall volume of lock requests. Let’s face it, the most efficient lock is the one never taken.

In general, DB2 avoids locking data pages if it can determine that the data to be accessed is committed and that no semantics are violated by not acquiring the lock. DB2 avoids locks by examining the log to verify the committed state of the data.

When determining if lock avoidance techniques will be practical, DB2 first scans the page to be accessed to determine whether any rows qualify. If none qualify, a lock is not required.

For each data page to be accessed, the RBA of the last page update (stored in the data page header) is compared with the log RBA for the oldest active unit of recovery. This RBA is called the Commit Log Sequence Number, or CLSN. If the CLSN is greater than the last page update RBA (or LRSN in a data sharing environment), the data on the page has been committed and the page lock can be avoided.

Additionally, a bit is stored in the record header for each row on the page. The bit is called the Possibly UNCommitted, or PUNC, bit. The PUNC bit indicates whether update activity has been performed on the row. The PUNC bit is checked if the CLSN test fails. For each qualifying row on the page, the PUNC bit is checked to see whether it is off. This indicates that the row has not been updated since the last time the bit was turned off. Therefore, locking can be avoided.

Note

IBM provides no external method for you to determine whether the PUNC bit is on or off for each row. Therefore, you should ensure that any table that can be modified should be reorganized on a regularly scheduled basis.

If neither CLSN nor PUNC bit testing indicates that a lock can be avoided, DB2 acquires the requisite lock.

In addition to enhancing performance, lock avoidance increases data availability. Data that in previous releases would have been considered locked, and therefore unavailable, is now considered accessible.

When Lock Avoidance Can Occur

Lock avoidance can be used only for data pages. Further, DB2 Catalog and DB2 Directory access does not use lock avoidance techniques. You can avoid locks under the following circumstances:

• For any pages accessed by read-only or ambiguous queries bound with ISOLATION(CS) and CURRENTDATA NO
• For any unqualified rows accessed by queries bound with ISOLATION(CS) or ISOLATION(RS)
• When DB2 system-managed referential integrity checks for dependent rows caused by either the primary key being updated, or the parent row being deleted and the DELETE RESTRICT rule is in effect
• For both COPY and RUNSTATS when SHRLEVEL(CHANGE) is specified

An ambiguous cursor is one where DB2 cannot determine whether there is intent to modify data retrieved using that cursor. The cursor is ambiguous if it is in a plan or package containing either PREPARE or EXECUTE IMMEDIATE SQL statements, along with the following conditions: the cursor is not defined with the FOR READ ONLY clause or the FOR UPDATE OF clause; the cursor is not defined on a read-only result table; the cursor is not the target of a WHERE CURRENT clause on an UPDATE or DELETE statement.

Skipping Locked Rows

Although not really a part of lock avoidance, you can indeed avoid locking data using the capability added with DB2 V9 that allows for a transaction to skip over rows that are locked using the SKIP LOCKED DATA option. SKIP LOCKED DATA can be coded on the following SQL statements:

• SELECT
• SELECT INTO
• PREPARE
• searched UPDATE
• searched DELETE

You can also use the SKIP LOCKED DATA option with the UNLOAD utility.

When you tell DB2 to skip locked data, then that data is not accessed and your program will not have it available. DB2 just skips over any locked data instead of waiting for it to be unlocked. The benefit, of course, is improved performance because you will not incur any lock wait time; however, it comes at the cost of not accessing the locked data. This means that you should utilize this clause only when your program can tolerate skipping over some data.

The SKIP LOCKED DATA option is compatible with cursor stability (CS) isolation and read stability (RS) isolation; however, it cannot be used with uncommitted read (UR) or repeatable read (RR) isolation levels. DB2 simply ignores the SKIP LOCKED DATA clause under UR and RR isolation levels.

In addition, SKIP LOCKED DATA works only with row locks and page locks. That means that SKIP LOCKED DATA does not apply to table, partition, LOB, XML, or table space locks. And the bigger the lock size, the more data that will be skipped when a lock is encountered. With row locking, you will be skipping over locked rows; however, with page locking, you will be skipping over all the rows on the locked page.

Use this feature with extreme care, and make sure that you know exactly what you are telling DB2 to do; otherwise, you might be reading less than you want. Consider using this option in certain test environments or possibly even in production under the proper conditions. For example, perhaps you have a program that needs to read from a table like a queue to get a next number. If it is not imperative that the numbers be sequential, using SKIP LOCKED DATA can eliminate bottlenecks by skipping any locked rows/pages to get data off of the queue.

Using Currently Committed Data

DB2 10 for z/OS adds another interested nuance to locking with the option to use currently committed data.

A common problem encountered by DB2 subsystems with poorly designed applications is frequent lock timeouts (and deadlocks). Under normal locking semantics, if data is locked an application requesting the same data would just wait until it becomes available, or timeout after waiting. As of DB2 V10, though, there is an alternative option for applications that cannot tolerate waiting on locks.

Using the CONCURRENTACCESSRESOLUTION parameter of the BIND command, you can direct your application program to use currently committed semantics, by specifying
USECURRENTLYCOMMITTED for the CONCURRENTACCESSRESOLUTION parameter. This clause applies only when the isolation level in effect is cursor stability (CS) or read stability (RS); otherwise it is simply ignored.

The impact of this clause on the program is that DB2 uses the currently committed version of the data for applicable scans when data is in the process of being updated or deleted. Rows that are in the process of being inserted can be skipped. So, only committed data is returned, as was the case previously, but now readers do not wait for writers to release locks. Instead, readers return data based on the currently committed version; that is, data prior to the start of the write operation.

Until next time... stay tuned for the next edition in the DB2 Locking series here at the DB2 Portal.

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.