Part 1  |  Part 2  |  Part 3

This is the final installment of a three-part series on DB2 locking (adapted from my book, DB2 Developer’s Guide). In this piece, we will look at locking and data sharing, as well as offer some advice on implementing an efficient DB2 locking strategy.

Data Sharing Global Lock Management

DB2 locking takes on a different spin in a data sharing context. 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 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 member 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.

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 tablespace without notifying the coupling facility. In a data sharing environment, locks on the top parents are always propagated to the coupling facility lock structures. In addition, the local DB2 propagates locks on children, depending on the compatibility of the maximum lock held on a tablespace 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.

LOBs and Locking

To be thorough in our coverage of DB2 locking, we need to discuss LOBs. In the second part of this article we discussed the various types of locks, but did not get into how DB2 deals with locks when LOB columns exist, so we’ll discuss it here.

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. Although the actual values for LOBs are stored in a separate tablespace from the rest of the table data, the locks on the base table also control concurrency for the LOB tablespace. 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.

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.

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 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 tablespace locks. If the LOB tablespace has a gross lock, DB2 does not acquire LOB locks. The following lock modes can be taken for the LOB tablespace:

S-lock, or SHARE: The lock owner and any concurrent processes can read and delete LOBs in the LOB tablespace. 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 tablespace. Concurrent processes can both read and modify LOBs in the same tablespace. 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 tablespace. 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 tablespace. 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 tablespace. The lock owner obtains a LOB locks when inserting or updating. Concurrent processes can SELECT or DELETE data in the LOB tablespace (or UPDATE the LOB to a null or zero-length).

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

Figure 5. The DB2 LOB locking hierarchy.

The type of locking used is controlled using the LOCKSIZE clause for the LOB tablespace. LOCKSIZE TABLESPACE indicates that no LOB locks are to be acquired by processes that access the LOBs in the tablespace. Specifying LOCKSIZE LOB indicates that LOB locks and the associated LOB tablespace 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 tablespace locking. DB2 will take locks on LOB tablespaces as needed. However, the RELEASE option of BIND does control when LOB tablespace locks are releases. For RELEASE(COMMIT), the LOB tablespace 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 Tablespace Locking Considerations

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

DB2 does not access the LOB tablespace 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 Guidelines

Locking is a complex subject, and it can take much time and effort to understand and master its intricacies. Do not be frustrated if these concepts escape you after only an initial reading of this series of articles. To help you along, you can instead refer to the following guidelines:

Be aware of the effect of referential integrity on locking

When tablespace locks are acquired because of the processing of referential constraints, all locking specifications, except the ACQUIRE bind parameter, are obeyed. Locks acquired because of referential integrity always acquire locks when needed, acting as though ACQUIRE(USE) were specified, regardless of the ACQUIRE parameter.

Establish acceptable BIND plan and package parameters

Favor the use of the following parameters when binding application plans because they usually produce the most efficient, effective DB2 plan. In particular, the ISOLATION, ACQUIRE, and RELEASE parameters specified in the following list create an efficient plan in terms of enabling a large degree of concurrent processing.

The following parameters can be used as the set of favored parameters when binding application plans:

     ISOLATION (CS)
     VALIDATE (BIND)
     ACTION (REPLACE)
     NODEFER (PREPARE)
     FLAG (I)
     ACQUIRE (USE)
     RELEASE (COMMIT)
     DEGREE (1)
     CURRENTDATA (NO)
     EXPLAIN (YES)

Likewise, establish similar favored parameters for DB2 packages:

     ISOLATION (CS)
     VALIDATE (BIND)
     ACTION (REPLACE)
     SQLERROR (NOPACKAGE)
     FLAG (I)
     RELEASE (COMMIT)
     DEGREE (1)
     CURRENTDATA (NO)
     EXPLAIN (YES)

These BIND parameters usually produce the most efficient and effective DB2 plans and packages. But, of course, there will be times when different parameters values will need to be specified. This list is to be used as a starting point; it should not be used as the final authoritative word for all programs.

Be aware of lock promotion

When binding a plan with an ISOLATION level of RR, the optimizer sometimes decides that tablespace locks will perform better than page locks. As such, the optimizer promotes the locking level to tablespace locking, regardless of the LOCKSIZE specified in the DDL. This process is called lock promotion.

Be aware of lock escalation

When you set the LOCKSIZE bind parameter to ANY, DB2 processing begins with page-level locking. As processing continues and locks are acquired, however, DB2 might decide that too many page (or row) locks have been acquired, causing inefficient processing.

In this scenario, DB2 escalates the level of locking from page (or row) locks to table or tablespace locks[md]a procedure called lock escalation. The threshold governing when lock escalation occurs is set in one of two ways:

• The DSNZPARM start-up parameters for DB2
• The LOCKMAX parameter of the CREATE or ALTER TABLESPACE statement (which is stored in the MAXROWS column of SYSIBM.SYSTABLESPACE)

Lock escalation applies only to objects defined with LOCKSIZE ANY in the DDL. A table lock can never be escalated to a tablespace lock. Tablespace locks are the highest level of locking and, therefore, cannot be escalated.

Be aware of user lock escalation

If a single user accumulates more page locks than are allowed by the DB2 subsystem (as set in DSNZPARMs), the program is informed via a 904 SQLCODE. The program can either issue a ROLLBACK and produce a message indicating that the program should be modified to COMMIT more frequently or, alternately, escalate the locking strategy itself by explicitly issuing a LOCK TABLE statement within the code.

Prior to implementing the second approach, refer to the upcoming discussion “Use LOCK TABLE with Caution” for further clarification on the ramifications of using LOCK TABLE.

Use DSNZPARM parameters to control lock escalation

The two DSNZPARM parameters used to govern DB2 locking are NUMLKTS and NUMLKUS. NUMLKTS defines the threshold for the number of page locks that can be concurrently held for any one tablespace by any single DB2 application (thread). When the threshold is reached, DB2 escalates all page locks for objects defined as LOCKSIZE ANY according to the following rules:

• All page locks held for data in segmented tablespaces are escalated to table locks.
• All page locks held for data in simple or partitioned tablespaces are escalated to tablespace locks.

NUMLKUS defines the threshold for the total number of page locks across all tablespaces that can be concurrently held by a single DB2 application. When any given application attempts to acquire a lock that would cause the application to surpass the NUMLKUS threshold, the application receives a resource unavailable message (SQLCODE of -904).

Consider favoring LOCKSIZE ANY

Letting DB2 handle the level of locking required can be a fine strategy. Turning over the determination for lock size to DB2 requires setting LOCKSIZE ANY.

Of course, you may have a compelling reason to use a specific LOCKSIZE. For example, you might choose to specify LOCKSIZE PAGE to explicitly direct DB2 to lock at the page level. Or, under certain conditions you might choose LOCKSIZE ROW to implement row-level locking. This topic is covered in more detail at the beginning of this article.

Use LOCKMAX to control lock escalation by tablespace

The LOCKMAX parameter specifies the maximum number of page or row locks that any one process can hold at any one time for the tablespace. When the threshold is reached, the page or row locks are escalated to a table or tablespace lock. The LOCKMAX parameter is similar to the NUMLKTS parameter, but for a single tablespace only.

Use LOCK TABLE with caution

Use the LOCK TABLE statement to control the efficiency of locking in programs that will issue many page lock requests. The LOCK TABLE statement is coded as a standard SQL statement and can be embedded in an application program.

There are two types of LOCK TABLE requests. The LOCK TABLE...IN SHARE MODE command acquires an S-lock on the table specified in the statement. This locking strategy effectively eliminates the possibility of concurrent modification programs running while the LOCK TABLE is in effect. Be aware that the S-lock is obtained on the tablespace for tables contained in non-segmented tablespaces.

The LOCK TABLE...IN EXCLUSIVE MODE command acquires an X-lock on the table specified in the statement. All concurrent processing is suspended until the X-lock is released. And be sure to note that the X-lock is obtained on the tablespace for tables contained in non-segmented tablespaces.

The table locks acquired as a result of the LOCK TABLE statement are held until the next COMMIT point unless ACQUIRE(DEALLOCATE) was specified for the plan issuing the LOCK TABLE statement. In that situation, the lock is held until the program terminates.

Encourage lock avoidance

To encourage DB2 to avoid locks, try the following:

• Whenever practical, specify ISOLATION(CS) and CURRENTDATA NO when binding packages and plans.
• Avoid ambiguous cursors by specifying FOR READ ONLY (or FOR FETCH ONLY) when a cursor is not to be used for updating.

Be aware of concurrent access with partition independence

Partition independence allows more jobs to be run concurrently. This capability can strain system resources. You should monitor CPU usage and I/O when taking advantage of partition independence to submit concurrent jobs that would have needed to be serialized with previous versions.

Use caution when specifying WITH HOLD

Using the CURSOR WITH HOLD clause causes locks and claims to be held across commits. This capability can increase the number of timeouts and affect availability. Before coding the WITH HOLD clause on a cursor, be sure that the benefit gained by doing so is not negated by reduced availability.

Access tables in the same order

Design all application programs to access tables in the same order. Doing so reduces the likelihood of deadlocks. Consider the following:

Program 1	Program 2
Lock on DEPT	Lock on EMP
Request Lock on EMP	Request Lock on DEPT

In this scenario, a deadlock occurs. However, if both programs accessed DEPT, followed by EMP, the deadlock situation might be able to be avoided.

Design application programs with locking in mind

Minimize the effect of locking through proper application program design. Limit the number of rows that are accessed by coding predicates to filter unwanted rows. Doing so reduces the number of locks on pages containing rows that are accessed but not required, thereby reducing timeouts and deadlocks.

Also, you should design update programs so that the update is as close to the COMMIT point as possible. Doing so reduces the time that locks are held during a unit of work, which also reduces timeouts and deadlocks.

Keep similar things together

Place tables for the same application into the same database. Although minimizing the number of databases used by an application can ease administration, it can negatively impact availability. For example, while dynamic SQL is accessing a table in a database, another table cannot be added to that database. When scheduled downtime is limited due to extreme availability requirements, such as is common in data warehousing and e-business environments, consider using one database for each large or active table.

Furthermore, each application process that creates private tables should have a dedicated private database in which to create the tables. Do not use a database that is in use for other, production database objects.

As with all advice, remember the cardinal rule of DB2: It depends! There are legitimate reasons for storing similar things separately. For example, as databases grow in size and activity increases, it might make sense to reduce the database size by storing fewer tablespaces per database.

Use LOCKPART to optimize partition independence

Enable selective partition locking by specifying LOCKPART YES when you create tablespaces. With selective partition locking, DB2 will lock only those partitions that are accessed. If you specify LOCKPART NO, the tablespace is locked with a single lock on the last partition. This has the effect of locking all partitions in the tablespace.

You cannot specify LOCKPART YES if you also specify LOCKSIZE TABLESPACE.

Cluster data

Use clustering to encourage DB2 to maintain data that is accessed together on the same page. If you use page locking, fewer locks are required to access multiple rows if the rows are clustered on the same page or pages.

Choose segmented over simple tablespaces for locking efficiency

Both simple and segmented tablespaces can contain more than one table. A lock on a simple tablespace locks all the data in every table because rows from different tables can be intermingled on the same page. In a segmented tablespace, rows from different tables are contained in different pages. Locking a page does not lock data from more than one table. Additionally, for segmented tablespaces only, DB2 can acquire a lock on a single table.

Consider increasing free space

If you increase free space, fewer rows are stored on a single page. Therefore, fewer rows are locked by a single page lock. This approach can decrease contention. However, it consumes additional disk storage, and it can also decrease the performance of tablespace scans.

Consider decreasing number of rows per page

The MAXROWS option of the CREATE TABLESPACE statement can be used to decrease the number of rows stored on a tablespace page. The fewer rows per page, the less intrusive page locking will be because fewer rows will be impacted by a page lock.

Control LOB locking for INSERT with subselects

Because LOB locks are held until COMMIT, it is possible that a statement, such as an INSERT, with a subselect involving LOB columns can acquire and hold many more locks than if LOBs are not involved. To prevent system problems caused by too many locks, consider the following tactics:

• Enable lock escalation by specifying a non-zero LOCKMAX parameter for LOB tablespaces impacted by the INSERT statement.
• Change the LOCKSIZE to LOCKSIZE TABLESPACE for the LOB tablespace prior to executing the INSERT statement.
• Use the LOCK TABLE statement to lock the LOB tablespace.

Summary

In this series of articles, we examined how DB2 guarantees the accuracy of its data by enacting data locks. Locking is a complex subject with many intricate details that can be difficult to understand. There are numerous types of locks, accompanied by a vast array of locking terms and strategies to learn. But an understanding of DB2 locking is important, because this is how DB2 maintains data integrity.

--

Craig Mullins is an independent consultant and president of Mullins Consulting, Inc. Craig has extensive experience in the field of database management having worked as an application developer, a DBA, and an instructor with multiple database management systems including DB2, Sybase, and SQL Server. Craig is also the author of the DB2 Developer’s Guide, the industry-leading book on DB2 for z/OS, and Database Administration: Practices and Procedures, the industry’s only book on heterogeneous DBA procedures. You can contact Craig via his web site at http://www.craigsmullins.com.