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