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;

SET ENVIRONMENT RETAINUPDATELOCKS 'NONE';