Improving performance for multiple users: Locking and concurrency

DB2 uses locks on user data. The main reason for using locks is to ensure the integrity, or accuracy, of the data. Without locks, one user might be retrieving a specific data item while another user might be changing that data. The result is that the first user retrieves inaccurate data. In the DB2 UDB for z/OS environment, which includes vast amounts of data and large numbers of users and transactions, the prospect of inaccurate data is unacceptable. Therefore, DB2 UDB for z/OS provides comprehensive locking to ensure data integrity.

Despite the importance of data integrity, locking can sometimes be too restrictive. If an application process locks too much data, other users, utilities, and application processes must wait for the locked data. This situation results in poor concurrency. Concurrency is the ability of more than one application process to access the same data at essentially the same time. DB2 UDB for z/OS handles the tradeoff between concurrency and data integrity to maximize concurrency without sacrificing the integrity of the data.

This section provides an overview of how locking works in DB2 UDB for z/OS.

How locking works

DB2 uses locks on a variety of data objects, including rows, pages, segmented tables, entire table spaces, and databases. When an application acquires a lock, the application “holds” or “owns” the lock. The following different lock modes provide different degrees of protection:

Share lock (S-lock)

The lock owner and any concurrent process can read, but cannot change, the locked object. Other concurrent processes can acquire share or update locks on the DB2 object.

Update lock (U-lock)

The lock owner can read, but not change, the DB2 object. Concurrent processes can acquire share locks, and they can read the DB2 object, but no other processes can acquire an update lock. Before actually making the change to the data, DB2 promotes update locks to exclusive locks.

Exclusive lock (X-lock)

The lock owner can read or change the locked data. A concurrent process cannot acquire share, update, or exclusive locks on the data. However, the concurrent process can read the data without acquiring a lock on the DB2 object.

The lock modes determine whether one lock is compatible with another.

Example: Assume that application process A holds a lock on a table space that process B also wants to access. DB2 requests, on behalf of B, a lock of some particular mode. If the mode of A's lock permits B's request, the two locks (or modes) are compatible. If the two locks are not compatible, B cannot proceed; it must wait until A releases its lock. (In fact, B must wait until the release of all existing incompatible locks.)

Compatibility for page and row locks is easy to define. Table 8.1 shows whether page locks or row locks of any two modes are compatible. Page locks and row locks are never compatible with each other because a table space cannot use both page and row locks.

In Table 8.1, the share, update, and exclusive locks apply to row or page locks. These facts apply only to application processes that have acquired an intent lock on the table space and the table, if the table is in a segmented table space.

An intent lock indicates the plan that the application process has for accessing the data. The two types of intent locks are intent-share and intent-exclusive.

Compatibility for table space locks is more complex.

Despite the importance of locks in the DB2 environment, some locking problems can occur, as the following list shows:

Suspension

An application process is suspended when it requests a lock that another application process already holds if that lock is not a shared lock. The suspended process temporarily stops running, and it resumes running in the following circumstances:

• All processes that hold the conflicting lock release it.

• The requesting process experiences a timeout or deadlock and the process resumes and handles an error condition.

Timeout

An application process times out when it terminates because of a suspension that exceeds a preset interval. DB2 terminates the process, issues messages, and returns error codes. Commit and rollback operations do not time out. The STOP DATABASE command, however, can time out, in which case DB2 sends messages to the console; the STOP DATABASE command will retry up to 15 times.

Deadlock

A deadlock occurs when two or more application processes each hold locks on resources that the others need and without which they cannot proceed. After a preset time interval, DB2 can roll back the current unit of work for one of the processes or request a process to terminate. DB2 thereby frees the locks and allows the remaining processes to continue.

Although some locking problems can occur, you can avoid system and application locking problems.

Concurrency control

DB2 provides the following isolation levels that determine how much to isolate an application from the effects of other running applications:

• Repeatable read (RR): RR isolation provides the most protection from other applications. With RR, the rows that an application references cannot be updated by any other applications before the application reaches a commit point.

• Read stability (RS): RS isolation allows an application to read the same pages or rows more than once while preventing another process from changing the rows. However, other applications can insert or update rows that did not satisfy the original application's search condition.

• Cursor stability (CS): CS isolation allows maximum concurrency with data integrity. With CS, a transaction holds locks only on its uncommitted changes and on the current row of each of its cursors.

• Uncommitted read (UR): UR isolation allows the application to read uncommitted data.

Scenarios that illustrate the need for locking

The following scenarios illustrate why locking is critical.

Scenario 1: Losing updated data

Two users, Kathy and Frank, are both trying to access the same DB2 table. Here is what happens:

This scenario does not use locking. It shows that the updated value in the column depends on which user commits the data first. If Kathy commits first, the updated column value is 120, and Kathy's update is lost. If Frank commits first, the updated column value is 110, and Frank's update is lost.

The scenario changes if it includes locking. When you read the process below, assume the use of an updatable cursor. Here is what happens:

If this scenario did not include updatable cursors, DB2 would grant a share lock (S-lock) to Kathy instead of a U-lock in step 1. DB2 would also grant an S-lock to Frank in step 2. When both Kathy and Frank try to update the column value, they would encounter a deadlock. When a deadlock occurs, DB2 decides whether to roll back Kathy's work or Frank's work. A rollback occurs when DB2 reverses a change that an individual application process tried to make. If DB2 rolls back Kathy's changes, Kathy releases the locks and Frank can then complete the process. Conversely, if DB2 rolls back Frank's changes, Frank releases the locks and Kathy can complete the process.

Application programs can minimize the risk of deadlock situations by using the FOR UPDATE OF clause in the DECLARE CURSOR statement. The program does not actually acquire the U-lock until any other U-locks or X-locks on the data object are released.

Scenario 2: Reading uncommitted data

As in Scenario 1, two users, Kathy and Frank, are both trying to access the same DB2 table.

This scenario does not include locks. It shows that Frank made an incorrect program decision. As a result, the business data in the database may be inaccurate.

When this scenario includes locking, this is what happens:

When the sccenario includes locks, Frank reads the correct data and can therefore make the correct program decision. As a result, the business data in the database is accurate.

Scenario 3: Repeatable read within a unit of work

In this scenario, Kathy wants to read the same data twice. No other program or user can change the data between the two reads.

Example: Assume that Kathy uses the following SQL statement:

SELECT * FROM EMP
 WHERE SALARY >
  (SELECT AVG(SALARY) FROM EMP);

This SQL statement reads the EMP table twice:

If Kathy does not lock the data between the two read processes, another user can update the EMP table between the two read processes. This update can lead to an incorrect result for Kathy.

Kathy could use DB2 locks to ensure that no changes to the table occur in between the two read processes. Kathy can choose from these options:

• Using the package or plan isolation level of repeatable read (RR) or using the WITH RR clause in the SQL SELECT statement.

• Locking the table in share or exclusive mode, using one of these statements:

  - LOCK TABLE EMP IN SHARE MODE
  - LOCK TABLE EMP IN EXCLUSIVE MODE
  

How to promote concurrency

DB2 uses and depends on locks because of the requirement for data integrity. However, locks are sometimes the cause of problems, such as deadlocks, timeouts, and suspensions. To minimize these problems and promote concurrency, database designers and application designers can take a variety of actions.

Recommendations for database designers

Database designers can take the following general actions to promote concurrency:

• Keep like things together in the database. For example, try to cluster tables that are relevant to the same application in the same database.

• Keep unlike things apart from each other in the database. For example, assume that user A owns table A and user B owns table B. By keeping table A and table B in separate databases, you can create or drop indexes on these two tables at the same time without causing lock contention.

• Use the LOCKSIZE ANY clause of the CREATE TABLESPACE statement unless doing otherwise proves to be preferable.

• Examine small tables, looking for opportunities to improve concurrency by reorganizing data or changing the locking approach.

• Partition the data.

• Partition secondary indexes. The use of data-partitioned secondary indexes promotes partition independence and, therefore, can reduce lock contention.

• Minimize update activity that moves rows across partitions.

• Store fewer rows of data in each data page.

These are a few high-level recommendations that database designers can follow to promote concurrency without compromising data integrity.

Recommendations for application designers

Application designers can take the following general actions to promote concurrency:

• Access data in a consistent order. For example, applications should generally access the same data in the same order.

• Commit work as soon as doing so is practical, to avoid unnecessary lock contentions.

• Retry an application after deadlock or timeout to attempt recovering from the situation without assistance.

• Close cursors to release locks and to free resources that the locks hold.

• Bind plans with the ACQUIRE(USE) clause, which is the best choice for concurrency.

• Bind with ISOLATION(CS) and CURRENTDATA(NO) in most cases. ISOLATION(CS) typically lets DB2 release acquired locks as soon as possible. CURRENTDATA(NO) typically lets DB2 avoid acquiring locks as often as possible.

• Use global transactions, which enables DB2 and other transaction managers to participate in a single transaction and thereby share the same locks and access the same data. (“Coordination of updates” on page 383 has more information about global transactions.)

These are a few high-level recommendations that application designers can follow to promote concurrency without compromising data integrity.