Improved performance through the use of locks

Db2 uses locks on various data objects.

Locks can be placed on rows, pages, tables, table space segments, table space partitions, entire table spaces, and databases. When an application acquires a lock, the application holds or owns the lock.

Db2 uses of the lock modes to determine whether one lock is compatible with another. Some lock modes do not exclude all other users. For 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 process B, a lock of some particular mode. If the mode of the lock for process A permits the lock requested by process B, the modes of the two locks are said to be compatible. However, if the two locks are not compatible, process B cannot proceed. It must wait until process A releases its lock, and until all other existing incompatible locks are released.

The following lock modes provide different degrees of protection:

S lock (share)
The lock owner and any concurrent processes can read, but not change, the locked page or row. Concurrent processes can acquire S or U locks on the page or row or might read data without acquiring a page or row lock. Only one U lock can be acquired concurrently with one or more S locks.
U lock (update)
The lock owner can read, but not change, the locked page or row. Concurrent processes can acquire S locks or might read data without acquiring a page or row lock, but no concurrent process can acquire a U lock.

U locks reduce the chance of deadlocks when the lock owner is reading a page or row to determine whether to change it. The owner can start with the U lock and then promote the lock to an X lock to change the page or row.

X lock (exclusive)
The lock owner can read or change the locked page or row. A concurrent process cannot acquire S, U, or X locks on the page or row. However, concurrent processes, such as those processes bound with the CURRENTDATA(NO) or ISOLATION(UR) bind options or running with YES specified for the EVALUNC subsystem parameter, can read the data without acquiring a page or row lock.

The following table shows the compatibility of any two modes for page and row locks. No question of compatibility arises between page and row locks, because a partition or table space cannot use both page and row locks.

Table 1. Compatibility matrix of page lock and row lock modes
Lock mode Share (S-lock) Update (U-lock) Exclusive (X-lock)
Share (S-lock) Yes Yes No
Update (U-lock) Yes No No
Exclusive (X-lock) No No No

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

When a page or row is locked, the table, partition, or table space that contains it is also locked. This intent lock indicates the plan that the application process has for accessing the data. In that case, the table, partition, or table space lock has one of the intent modes: either IS for intent share, IX for intent exclusive, or SIX for share with intent exclusive.

Compatibility for table space locks is slightly more complex that for page and row locks.

Locks are important for maintaining concurrency in the Db2 environment. However, locks might cause several types of contention situations that degrade Db2 performance, including suspension, timeout, and deadlock.

Suspension
An application encounters suspension when it requests a lock that is already held by another application process and cannot be shared. The suspended process temporarily stops running. A suspended process resumes when all processes that hold the conflicting lock release them or the requesting process experiences a timeout or deadlock and the process resumes and handles an error condition.
Timeout
An application process encounters a timeout 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 timeout. The STOP DATABASE command, however, can time out, in which case Db2 sends messages to the console. When this happens Db2 retries the STOP DATABASE command as many as 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. In determining which process to roll back or terminate, Db2 assesses many characteristics of the processes that are involved in the deadlock and chooses the one that, if terminated, will cause the least impact relative to the other processes. By choosing a process to roll back or terminate, Db2 frees the locks and allows the remaining processes to continue.

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

The following scenarios illustrate the importance of locks.

Scenario: Avoidance of the loss of updated data

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

  1. Kathy reads the data value, 100, into a host variable.
  2. Frank reads the same column value into a host variable.
  3. Kathy adds 10 to the host variable value and saves the new value, 110, in the Db2 table column.
  4. Frank adds 20 to the host variable value and saves the new value, 120, in the Db2 table column.

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:

  1. Kathy reads column value 100 into a host variable with the intention of updating the value. Db2 then grants an update lock to Kathy.
  2. Frank wants to read the same column value into a host variable with the intention of updating the value. According to the compatibility matrix in the table above, Db2 does not grant Frank an update lock (U-lock) on the Db2 object that contains column value 100. Therefore, Frank must wait to read the column value until Kathy releases the lock.
  3. Kathy adds 10 to the host variable value and wants to save the new value, 110, in the Db2 table column. At this point, Db2 changes the U-lock to an exclusive lock (X-lock) on the Db2 object that contains the column value.
  4. Kathy commits the change. Db2 then releases the X-lock on the Db2 object that contains the column value. Next, Db2 grants the U-lock to Frank on the same object (unless Frank timed out while waiting for access). The host variable that Frank specified now contains the updated value of 110.
  5. Frank adds 20 to the host variable value and wants to save the new value, 130, in the table column. Db2 changes the U-lock to an X-lock on the Db2 object that contains the column value.
  6. Frank commits the change. Db2 then releases the X-lock on the Db2 object that contains the column value.

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: Avoidance of read access to uncommitted data

Two users, Kathy and Frank, are both trying to access the same Db2 table.

  1. Kathy updates the value of 100 to 0 in the Db2 table column.
  2. Frank reads the updated value of 0 and makes program decisions based on that value.
  3. Kathy cancels the process and changes the value of 0 back to 100 for the Db2 table column.

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

When this scenario includes locking, this is what happens:

  1. Kathy attempts to update the value of 100 to 0 in the table column. Db2 grants an X-lock to Kathy on the Db2 object that contains the column value that requires an update.
  2. Frank tries to read the updated column value so that he can make program decisions based on that value. Db2 does not allow Frank to read the updated column value of 0. Frank tries to acquire an S-lock on the Db2 object that currently has the X-lock. Frank must wait until Kathy commits or rolls back the work.
  3. Kathy cancels the process and changes the value of 0 back to the original value of 100 for the Db2 table column. Db2 makes the actual change to the data and releases the X-lock for Kathy. Db2 then grants the S-lock to Frank on the Db2 object that contains the column value. Frank then reads the value of 100.

When the scenario 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: Avoidance of updates between multiple reads 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.

Begin general-use programming interface information.

Assume that Kathy uses the following SQL statement: 

SELECT * FROM EMP
  WHERE SALARY>
         (SELECT AVG(SALARY) FROM EMP);
End general-use programming interface information.

This SQL statement reads the EMP table twice:

  1. It calculates the average of the values in the SALARY column of all rows in the table.
  2. It finds all rows in the EMP table that have a value in the SALARY column that exceeds the average value.

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.
  • Begin general-use programming interface information.
  • 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
End general-use programming interface information.