Using the RETAIN UPDATE LOCKS Option
Use the RETAIN UPDATE LOCKS option to affect the behavior of the database server when it handles a SELECT ... FOR UPDATE statement.
In a database with the isolation level set to Dirty Read, Committed Read, or Cursor Stability, the database server places an update lock on a fetched row of a SELECT ... FOR UPDATE statement. When you turn on the RETAIN UPDATE LOCKS option, the database server retains the update lock until the end of the transaction rather than releasing it at the next subsequent FETCH or when the cursor is closed. This option prevents other users from placing an exclusive lock on the updated row before the current user reaches the end of the transaction.
You can use this option to achieve the same locking effects but avoid the overhead of dummy updates or the repeatable read isolation level.
You can turn this option on or off at any time during the current session.
BEGIN WORK;
SET ISOLATION TO
COMMITTED READ LAST COMMITTED RETAIN UPDATE LOCKS;
...
COMMIT WORK;
BEGIN WORK;
SET ISOLATION TO COMMITTED READ LAST COMMITTED ;
...
COMMIT WORK;
Controlling Update Locks through the Session Environment
SET ENVIRONMENT RETAINUPDATELOCKS 'NONE';
This
disables the RETAIN UPDATE LOCKS clause for the
current transaction, and for any subsequent transactions of the same
session, by resetting the RETAINUPDATELOCKS session environment variable.The SET ENVIRONMENT RETAINUPDATELOCKS statement can also make the retention of update locks the default behavior for either the Committed Read, Cursor Stability, or Dirty Read isolation levels, or for all of these isolation levels, regardless of whether the SET ISOLATION statement includes the RETAIN UPDATE LOCKS clause.
For more information on update locks, see RETAINUPDATELOCKS session environment option and Locking Considerations.