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.