Improving concurrency for update and delete operations

You can avoid certain deadlock situations by controlling the lock modes that are used by certain SELECT, UPDATE, and DELETE statements.

About this task

Statements that search for data to update or delete can encounter lock contention, such as timeout and deadlock, when concurrent operations acquire locks on the data during the search phase. The contention happens when locks from concurrent processes prevent the statement from acquiring the locks that are required for the update or delete operations.

For example, if a statement uses S-locks while it searches for data to update, a concurrent operation can acquire S locks on the same data. The S-lock from the concurrent operation might prevent the first statement from acquiring the X-lock that it must use to update or delete the data. The result might be a timeout. If both concurrent operations need to acquire x-locks, deadlock situations can result.

Procedure

To improve concurrency for statements that search before they update or delete data, use the following approaches:

  • Specify USE AND KEEP lock-mode LOCKS in the isolation-clause.
    This clause applies at the statement level to SELECT statements that use RR or RS isolation. The best mode to specify depends on the filter factor of the search operation:
    • Specify U-locks when the filter factor is high and a large percentage of the rows that are fetched for the statement are updated or deleted.
    • Specify X-locks if almost all fetched rows are updated or deleted.
  • Set the value of the RRULOCK subsystem parameter.
    This value controls the mode of locks at the subsystem level for cursor-based SELECT statements that specify FOR UPDATE. It also applies to UPDATE and DELETE statements when a cursor is not used. The YES option can avoid deadlocks but it reduces concurrency. This option applies only to statements in packages that use RR or RS isolation.

Results

When USE AND KEEP lock-mode LOCKS is specified in a statement, it overrides the value of the RRULOCK subsystem parameter. Another subsystem parameter XLKUPDLT also controls lock modes for non-cursor UPDATE and DELETE statements. The value of the XLKUPDLT subsystem parameter overrides the value of the RRULOCK subsystem parameter. It can be used to reduce the cost of lock requests for UPDATE and DELETE operations in data sharing environments.

The following table summarizes the results of the options.

Table 3. Lock modes for statements that use RR and RS options
Option cursor SELECT with FOR UPDATE SELECT with RS or RR isolation UPDATE or DELETE
USE AND KEEP lock-mode LOCKS S, U, or X-locks, as specified by lock-mode. S, U, or X-locks, as specified by lock-mode. Not applicable
RRULOCK=YES U-locks Not applicable. U-locks
XLKUPDLT=YES Not applicable Not applicable X-locks