Retain update locks

If a user has the isolation level set lower than Repeatable Read, the database server releases update locks placed on rows as soon as the next row is fetched from a cursor. With this feature, you can use the RETAIN UPDATE LOCKS clause to retain an update lock until the end of a transaction when you set any of the following isolation levels:
  • Dirty Read
  • Committed Read
  • Cursor Stability

This feature lets you avoid the overhead of Repeatable Read isolation level or workarounds such as dummy updates on a row. When the RETAIN UPDATE LOCKS feature is turned on and an update lock is implicitly placed on a row during a fetch of a SELECT...FOR UPDATE statement, the update lock is not released until the end of the transaction. With the RETAIN UPDATE LOCKS feature, only update locks are held until end of transaction, whereas the Repeatable Read isolation level holds both update locks and shared locks until end of transaction.

The following example shows how to use the RETAIN UPDATE LOCKS clause when you set the isolation level to Committed Read. 
SET ISOLATION TO COMMITTED READ RETAIN UPDATE LOCKS

To turn off the RETAIN UPDATE LOCKS feature, set the isolation level without the RETAIN UPDATE LOCKS clause. When you turn off the feature, update locks are not released directly. However, from this point on, a subsequent fetch releases the update lock of the immediately preceding fetch but not of earlier fetch operations. A close cursor releases the update lock on the current row.

For more information about how to use the RETAIN UPDATE LOCKS feature when you specify an isolation level, see the Informix® Guide to SQL: Syntax.