DB2 Locking, Part 11: Data Sharing Global Lock Management

Data sharing adds an additional level of complexity to the DB2 locking strategies and techniques we have been discussing in this series. Because data sharing group members can access any object from any member in the group, a global locking mechanism is required. It is handled by the lock structure defined in the coupling facility. The lock structure is charged with managing inter-member locking. Without a global lock management process, data integrity problems could occur when one member attempts to read (or change) data that is in the process of being changed by another member.

Data sharing groups utilize a global locking mechanism to preserve the integrity of the shared data. The global locking mechanism allows locks to be recognized between members.

Global Locking

All members of a data sharing group must be aware of locks that are held or requested by the other members. The DB2 data sharing group utilizes the coupling facility (CF) to establish and administer global locks.

The IRLM performs locking within each member DB2 subsystem. Additionally, the IRLM communicates with the coupling facility to establish global locks. Each member of the data sharing group communicates lock requests to the coupling facility’s lock structure. The manner in which a transaction takes locks during execution does not change. The only difference is that, instead of being local locks, the locks being taken are global in nature.

DB2 data sharing does not use message passing to perform global locking. The members DB2 IRLMs use the coupling facility to do global locking. Contention can be identified quickly without having to suspend the tasks to send messages around to the other DB2 members contained in the data sharing group. The following list outlines the events that occur when transactions from different DB2 members try to access the same piece of data:

1. TXN1 requests a lock that is handled by the local IRLM.
2. The local IRLM passes the request to the coupling facility global lock structures to ensure that no other members have incompatible locks. No incompatible locks are found, so the lock is taken.
3. TXN2 requests a lock that is handled by its local IRLM. The lock is for the same data held by TXN1 executing in a different DB2 subsystem.
4. Once again, the local IRLM passes the request to the coupling facility global lock structures to check for lock compatibility. In this case, an incompatible lock is found, so the lock request cannot be granted. The task is suspended.
5. Eventually, TXN1 executes a COMMIT, which releases all local and global locks.
6. TXN2 now can successfully execute the lock and continue processing.

Data Sharing Locking Considerations 

Consider specifying TRACKMOD NO for objects used in a data sharing environment to avoid locking problems. In addition, consider the MEMBER CLUSTER option. The MEMBER CLUSTER option indicates that DB2 should locate table space data based on available space rather than clustering the data by the clustering index. This option can benefit applications when there are many inserts to the same table from multiple members.

You might also improve performance by randomizing index key columns to reduce hot spots. This can be accomplished using the RANDOM keyword of CREATE INDEX.

The coupling facility level (CFLEVEL) can also be a consideration. DB2 prefetch processing for GBP-dependent page sets and partitions varies depending on the CFLEVEL in which the group buffer pool is allocated.

If the group buffer pool is allocated in a coupling facility with CFLEVEL=0 or 1, DB2 reads and registers one page at a time in the group buffer pool. If the group buffer pool is allocated in a coupling facility with CFLEVEL=2 or higher, DB2 can register the entire list of pages prefetched with one request to the coupling facility.

You can determine the CFLEVEL of your coupling facility using the DISPLAY GROUP command.

Lock Structures

The coupling facility contains several lock structures that are used for global locking purposes. The lock lists contain names of modified resources. This information is used to notify members of the data sharing group that the various resources have been changed.

Additionally, a hash table is used to identify compatible and incompatible lock modes. If the same hash value is used for the same resource name from different systems (with incompatible lock modes), lock contention will occur. If the same hash value is used for different resource names (called a hashing collision), false contention will occur. Any contention requires additional asynchronous processing to occur.

Hierarchical Locking

DB2 data sharing introduces the concept of explicit hierarchical locking to reduce global locking overhead (which increases global locking performance). Explicit hierarchical locking allows data sharing to differentiate between global and local locks. When no inter-DB2 interest occurs in a resource, the local IRLM can grant locks locally on the resources that are lower in the hierarchy. This feature allows the local DB2 to obtain local locks on pages or rows for that table space without notifying the coupling facility. In a data sharing environment, locks on the top parents are always propagated to the coupling facility lock structures. (These structures are detailed on the previous page.) In addition, the local DB2 propagates locks on children, depending on the compatibility of the maximum lock held on a table space that also has other members of the DB2 data sharing group requesting locks on it.

P-Locks Versus L-Locks

DB2 data sharing introduces two new lock identifiers: P-locks and L-locks.

P-locks preserve inter-DB2 coherency of buffered pages. P-locks are owned by the member DB2 subsystem and are used for physical resources such as page sets. These physical resources can be either data objects or index objects. P-locks are held for the length of time the pages are locally cached in the local buffer pool. As such, data can be cached beyond a transaction commit point.

P-locks are negotiable. If multiple DB2 members hold incompatible P-locks, the IRLMs try to downgrade lock compatibility. P-locks are never timed out. Because P-locks are not owned by transactions, they cannot be deadlocked. The sole job of a P-lock is to ensure inter-DB2 coherency. P-locks notify the data sharing group that a member of that group is performing work on that resource. This way, the coupling facility can become involved and begin treating the resources globally.

L-locks are used for both intra- and inter-DB2 concurrency between transactions. L-locks can either be local or global in scope. L-locks are owned by transactions and are held for COMMIT or allocation duration. L-locks are not negotiable and, as such, must wait for incompatible L-locks held by other DB2 members to be released before they can be taken. Suspended L-locks can be timed out by the IRLM.

DB2 Locking, Part 10: Know Your ISOLATION Levels

DB2 provides a way to change the way that a program or SQL statement acquires locks. That way is known as the isolation level and it can be set to specify the locking behavior for a transaction or statement. Standard SQL defines four isolation levels that can be set using the SET TRANSACTION ISOLATION LEVEL statement:

• Serializable
• Repeatable read
• Read committed
• Read uncommitted

The isolation level determines the mode of page or row locking implemented by the program as it runs.

DB2 supports a variation of the standard isolation levels. DB2 implements page and row locking at the program execution level, which means that all page or row locks are acquired as needed during the program run. Page and row locks are released as the program run depending on the isolation level.

In DB2 you can specify the following four isolation levels: cursor stability (CS), repeatable read (RR), read stability (RS), and uncommitted read (UR).

Using the ISOLATION parameter of the BIND command you can set the isolation level of a package or plan. You also can use the WITH parameter on a SELECT statement to set the isolation level of a single SQL statement.

Cursor stability is the DB2 implementation of the SQL standard read committed isolation level. CS is perhaps the most common DB2 isolation level in use in production applications because it offers a good tradeoff between data integrity and concurrency. When CS is specified the transaction will never read data that is not yet committed; only committed data can be read. Cursor stability is the default isolation level if none is specified at BIND time.

A higher level of integrity is provided with repeatable read. Under an RR isolation level all page locks are held until they are released by a COMMIT (or ROLLBACK), whereas with CS read-only page locks are released as soon as another page is accessed.
Repeatable read is the default isolation level if none is specified at BIND time.

An RR page locking strategy is useful when an application program requires consistency in rows that may be accessed twice in one execution of the program, or when an application program requires data integrity that cannot be achieved with CS.

For example of a good reason to use RR page locking, consider a reporting program that scans a table to produce a detail report, and then scans it again to produce a summarized managerial report. If the program is bound using CS, the results of the first report might not match the results of the second.

Suppose that you are reporting the estimated completion dates for project activities. The first report lists every project and the estimated completion date. The second, managerial report lists only the projects with a completion date greater than one year.

The first report indicates that two activities are scheduled for more than one year. After the first report but before the second, however, an update occurs. A manager realizes that she underestimated the resources required for a project. She invokes a transaction to change the estimated completion date of one of her project's activities from 8 months to 14 months. The second report is produced by the same program, but reports 3 activities.

If the program used an RR isolation level rather than CS, an UPDATE that occurs after the production of the first report but before the second would not have been allowed. The program would have maintained the locks it held from the generation of the first report and the updater would be locked out until the locks were released.

How about another example? Consider a program that is looking for pertinent information about employees in the information center and software support departments who make more than $30,000 in base salary. In the DB2 sample tables department 'C01' is the information center and department 'E21' is software support.

The program opens a cursor based on the following SELECT statement:

    SELECT  EMPNO, FIRSTNME, LASTNAME,

            WORKDEPT, SALARY

    FROM    DSN8710.EMP

    WHERE   WORKDEPT IN ('C01', 'E21')

    AND     SALARY > 30000;

The program then begins to FETCH employee rows. Assume further, as would probably be the case, that the statement uses the XEMP2 index on the WORKDEPT column. An update program that implements employee modifications is running concurrently. The program handles transfers by moving employees from one department to another, and implements raises by increasing the salary.

Assume that Sally Kwan, one of your employees, has just been transferred from the information center to software support. Assume further that another information center employee, Heather Nicholls, received a 10 percent raise. The update program running concurrently with the report program implements both of these modifications.

If the report program were bound with an isolation level of CS, the second program could move Sally from 'C01' to 'E21' after she was reported to be in department 'C01' but before the entire report was finished. Thus, she could be reported twice: once as an information center employee and again as a software support employee. Although this circumstance is rare, it can happen with programs that use cursor stability. If the program were bound instead with RR, this problem could not happen. The update program probably would not be allowed to run concurrently with a reporting program, however, because it would experience too many locking problems.

Now consider Heather's dilemma. The raise increases her salary 10 percent, from $28,420 to $31,262. Her salary now fits the parameters specified in the WHERE condition of the SQL statement. Will she be reported? It depends on whether the update occurs before or after the row has been retrieved by the index scan, which is clearly a tenuous situation. Once again, RR avoids this problem.

You might be wondering, "If CS has the potential to cause so many problems, why is it used so ubiquitously? Why not trade the performance and concurrency gain of CS for the integrity of RR?"

The answer is simple: the types of problems outlined are rare. The expense of using RR, however, can be substantial in terms of concurrency. So the tradeoff between the concurrency expense of RR and the efficiency of CS usually is not a sound one.

The third isolation level provided by DB2 is read stability (RS), which is like the SQL standard serializable level. Read stability is similar in functionality to the RR isolation level. A retrieved row or page is locked until the end of the unit of work; no other program can modify the data until the unit of work is complete, but other processes can insert values that might be read by your application if it accesses the row a second time.

Use read stability only when your program can handle retrieving a different set of rows each time a cursor or singleton SELECT is issued. If using read stability, be sure your application is not dependent on having the same number of rows returned each time.

Finally, we come to the last, and most maligned isolation level, uncommitted read (UR). The UR isolation level provides read-through locks, also know as dirty read or read uncommitted. Using UR can help to overcome concurrency problems. When you're using an uncommitted read, an application program can read data that has been changed but is not yet committed.

UR can be a performance booster, too, because application programs bound using the UR isolation level will read data without taking locks. This way, the application program can read data contained in the table as it is being manipulated. Consider the following sequence of events:

(1) To change a specific value, at 9:00 a.m. a transaction containing the following SQL is executed:

    UPDATE    EMP
       SET FIRSTNME = "MICHELLE"
    WHERE     EMPNO = 10020;

     The transaction is a long-running one and continues to execute without issuing a COMMIT.

(2) At 9:01 a.m., a second transaction attempts to SELECT the data that was changed, but not committed.

If the UR isolation level were used for the second transaction, it would read the changed data even though it had yet to be committed. Obviously, if the program doesn't need to wait to take a lock and merely reads the data in whatever state it happens to be at that moment, the program will execute faster than if it had to wait for locks to be taken and resources to be freed before processing.

The implications of reading uncommitted data, however, must be carefully examined before being implemented. Several types of problems can occur. Using the previous example, if the long-running transaction rolled back the UPDATE to EMPNO 10020, the program using dirty reads may have picked up the wrong name ("MICHELLE") because it was never committed to the database.

Inaccurate data values are not the only problems that can be caused by using UR. A dirty read can cause duplicate rows to be returned where none exist. Alternatively, a dirty read can cause no rows to be returned when one (or more) actually exists. Additionally, an ORDER BY clause does not guarantee that rows will be returned in order if the UR isolation level is used. Obviously, these problems must be taken into consideration before using the UR isolation level.

Keep in mind, too, that the UR isolation level applies to read-only operations: SELECT, SELECT INTO, and FETCH from a read-only result table. Any application plan or package bound with an isolation level of UR will use uncommitted read functionality for any read-only SQL. Operations contained in the same plan or package and are not read-only will use an isolation level of CS.

When is it appropriate to use UR isolation? The general rule of thumb is to avoid UR whenever the results must be 100 percent accurate. Following are examples of when this would be true:

• Calculations that must balance are being performed on the selected data
• Data is being retrieved from one source to insert to or update another
• Production, mission-critical work is being performed that cannot contain or cause data integrity problems

In general, most DB2 applications are not serious candidates for dirty reads. In a few specific situations, however, the dirty read capability will be of major benefit. Consider the following cases in which the UR isolation level could prove to be useful:

• Access is required to a reference, code, or look-up table that basically is static in nature. Due to the non-volatile nature of the data, a dirty read would be no different than a normal read the majority of the time. In those cases when the code data is being modified, any application reading the data would incur minimum, if any, problems.
• Statistical processing must be performed on a large amount of data. Your company, for example, might want to determine the average age of female employees within a certain pay range. The impact of an uncommitted read on an average of multiple rows will be minimal because a single value changed will not greatly impact the result.
• Dirty reads can prove invaluable in a data warehousing environment that uses DB2 as the DBMS. A data warehouse is a time-sensitive, subject-oriented, store of business data that is used for online analytical processing. Other than periodic data propagation and/or replication, access to the data warehouse is read-only. Because the data is generally not changing, an uncommitted read is perfect in a read-only environment due to the fact that it can cause little damage. More data warehouse projects are being implemented in corporations worldwide and DB2 with dirty read capability is a very wise choice for data warehouse implementation.
• In those rare cases when a table, or set of tables, is used by a single user only, UR can make a lot of sense. If only one individual can be modifying the data, the application programs can be coded such that all (or most) reads are done using UR isolation level, and the data will still be accurate.
• Finally, if the data being accessed already is inconsistent, little harm can be done using a dirty read to access the information.

Although the dirty read capability can provide relief to concurrency problems and deliver faster performance in specific situations, it also can cause data integrity problems and inaccurate results. Be sure to understand the implications of the UR isolation level and the problems it can cause before diving headlong into implementing it in your production applications.

Summary

It is important for DB2 DBAs and application programmers to know the four isolation levels and their impact on SQL. Using the isolation levels is an effective way to control concurrency and locking for your DB2 applications.

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.