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 an initial reading of this chapter. Instead, refer to the following guidelines to assist you in designing your application's locking needs. Let this information sink in for a while and then reread the chapter.

Be Aware of the Effect of Referential Integrity on Locking

When table space 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 Parameters

This information is covered in more detail in Chapter 13, "Program Preparation," but it is repeated here because it affects DB2 locking. Favor the use of the following parameters when binding application plans because they usually produce the most efficient and 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.

Favor the use of the following parameters when binding application plans:

ISOLATI ON (CS)

VALIDATE (BIND)

ACTION (REPLACE)

NODEFER (PREPARE)

FLAG (I)

ACQUIRE (USE)

RELEASE (COMMIT)

DEGREE (1)

CURRENTDATA (NO)

EXPLAIN (YES)

These BIND PLAN parameters usually produce the most efficient and effective DB2 plan.

Establish Acceptable BIND Package Parameters

The ISOLATION parameter is the most important in terms of locking for DB2 packages. The following list of parameters should be favored when binding packages:

ISOLATION (CS)

VALIDATE (BIND)

ACTION (REPLACE)

SQLERROR (NOPACKAGE)

FLAG (I)

RELEASE (COMMIT)

DEGREE (1)

CURRENTDATA (NO)

EXPLAIN (YES)

Usually, these BIND PACKAGE parameters produce the most efficient and effective DB2 package. Other guidelines in this chapter cover the occasions when you should choose another option.

Be Aware of Lock Promotion

When binding a plan with an ISOLATION level of RR , the optimizer sometimes decides that table space locks will perform better than page locks. As such, the optimizer promotes the locking level to table space 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 ”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 table space lock. Table space locks are the highest level of locking and, therefore, cannot be escalated.

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 guideline, "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 table space 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 table spaces are escalated to table locks.

• All page locks held for data in simple or partitioned table spaces are escalated to table space locks.

NUMLKUS defines the threshold for the total number of page locks across all table spaces 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 LOCKSIZE ANY

In general, letting DB2 handle the level of locking required is a fine strategy. Turning over the determination for lock size to DB2 requires setting LOCKSIZE ANY .

Of course, you might 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. Chapter 5, "Data Definition Guidelines," provides more details on the LOCKSIZE parameter.

Use LOCKMAX to Control Lock Escalation by Table Space

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

Set IRLM Parameters to Optimize Locking

When the IRLM is installed, you must code a series of parameters that affect the performance of DB2 locking. In particular, you should define the IRLM so that it effectively utilizes memory to avoid locking performance problems. The IRLM parameters are detailed in Table 23.13.

Table 23.13. Recommended IRLM Parameters

Parameter	Recommended Value	Reason
SCOPE	LOCAL	The IRLM should be local.
DEADLOK	(15,4)	Every 15 seconds, the IRLM goes into a deadlock detection cycle.
PC	NO	Cross-memory services are not used when requesting IRLM functions; instead, the locks are stored in ECSA and therefore are directly addressable.
ITRACE	NO	Never turn on the IRLM trace because it uses a vast amount of resources.

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. Note: The S -lock is obtained on the table space for tables contained in non-segmented table spaces.

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. Note: The X -lock is obtained on the table space for tables contained in non-segmented table spaces.

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.

Use the DSNZPARM RELCURHL to minimize these locks. Specify whether DB2 should, at COMMIT time, release a page or row lock on which a cursor defined WITH HOLD is positioned. This lock is not necessary for maintaining cursor position. YES is the default causing DB2 to release this lock after a COMMIT is issued. Specifying RELCURHL YES can improve concurrency. If you choose NO , DB2 holds the lock for WITH HOLD cursors after the COMMIT . This option is provided so that existing applications that rely on this lock can continue to work correctly.

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 could 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.

CAUTION

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 table spaces per database.

Use LOCKPART to Optimize Partition Independence

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

CAUTION

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 Table Spaces for Locking Efficiency

Both simple and segmented table spaces can contain more than one table. A lock on a simple table space locks all the data in every table because rows from different tables can be intermingled on the same page. In a segmented table space, rows from different tables are contained in different pages. Locking a page does not lock data from more than one table. Additionally, for segmented table spaces 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 DASD, and it can also decrease the performance of table space 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 table space 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 table spaces impacted by the INSERT statement.

• Change the LOCKSIZE to LOCKSIZE TABLESPACE for the LOB table space prior to executing the INSERT statement.

• Use the LOCK TABLE statement to lock the LOB table space.