RETAINUPDATELOCKS session environment option

The RETAINUPDATELOCKS environment option can improve concurrency in Dynamic SQL applications that include the SELECT ... FOR UPDATE statement. This option can modify the behavior of the current transaction isolation level at runtime if the session is using the Committed Read, Dirty Read, or Cursor Stability isolation levels to enable (or to disable) the RETAIN UPDATE LOCKS clause of the SET ISOLATION statement.

The RETAINUPDATELOCKS session environment option supports the following syntax:

Usage

The RETAINUPDATELOCKS option accepts any one of five settings that can affect the current HCL OneDB™ isolation level, as well as the isolation levels established by SET ISOLATION statements issued after the SET ENVIRONMENT statement. For every setting except 'NONE', the effect of the setting is to implicitly include the RETAIN UPDATE LOCKS keywords in SET ISOLATION specifications:

'COMMITTED READ': The database server retains any update lock until the end of a transaction that uses the Committed Read isolation level.
'CURSOR STABILITY': The database server retains any update lock until the end of a transaction that uses the Cursor Stability isolation level.
'DIRTY READ': The database server retains any update lock until the end of a transaction that uses the Dirty Read isolation level.
'ALL': The database server retains any update lock until the end of the transaction that uses the Committed Read, Dirty Read, or Cursor Stability isolation level.
'NONE': the RETAINUPDATELOCKS feature is disabled until the session ends, or until another SET ISOLATION or SET ENVIRONMENT statement re-enables the retention of update locks. Under the NONE setting, if your application defines an update cursor, the database server releases its update locks at the next FETCH operation, or when the update cursor is closed. Update locks are not retained, even if the Committed Read, Dirty Read, or Cursor Stability isolation level had enforced RETAIN UPDATE LOCKS behavior before the SET ENVIRONMENT RETAINUPDATELOCKS 'NONE' statement executed.

When the RETAINUPDATELOCKS environment option is enabled for the current isolation level, the database server, by default, retains the update lock on a row until the end of the transaction. Any update locks are held until the transaction is committed or rolled back, whether or not the SET ISOLATION statement that defined the isolation level included the RETAIN UPDATE LOCKS keywords. When this option is set to ALL or to the name of the current HCL OneDB isolation level (if this level is Committed Read, Dirty Read, or Cursor Stability), this setting prevents concurrent users in other sessions from deleting or updating a row on which you have placed an update lock, but that you have not yet updated.

By specifying NONE as the RETAINUPDATELOCKS setting, you disable this feature and restore the default locking behavior. When NONE is the setting, unless the isolation level has been set by a SET ISOLATION statement that explicitly included the RETAIN UPDATE LOCKS keywords, the database server releases the update lock at the next FETCH operation, or when the cursor is closed.

The SET ENVIRONMENT RETAINUPDATELOCKS statement has no effect on update cursors if the HCL OneDB isolation level is REPEATABLE READ. Similarly out of scope are transactions whose isolation level has been set by the SET TRANSACTION statement, which defines ANSI/ISO-compliant isolation levels, rather than HCL OneDB isolation levels. (For more information about HCL OneDB and ISO isolation levels, see the topic Comparing SET TRANSACTION with SET ISOLATION.)

Like other SET ENVIRONMENT options, these settings are not case-sensitive, but they require single ( ' ) or double ( " ) quotation marks as delimiters. For example, the settings 'ALL' and 'all' and "aLl" and "aLl" have the same effect.

Examples of setting RETAINUPDATELOCKS

The SET ENVIRONMENT RETAINUPDATELOCKS statement takes effect (by resetting the session environment) when it is issued. It can be issued outside a transaction. If the isolation level of the current transaction matches the setting specified after the RETAINUPDATELOCKS keyword, the new setting can change the RETAIN UPDATE LOCKS behavior of the transaction that is running when the statement is issued.

For example, consider the following SET ENVIRONMENT and SET ISOLATION statements:

BEGIN WORK; --Begin first transaction
SET ENVIRONMENT RETAINUPDATELOCKS 'COMMITTED READ';
SET ISOLATION TO COMMITTED READ LAST COMMITTED;
SELECT ... FOR UPDATE ...
...
COMMIT WORK;
SET ENVIRONMENT RETAINUPDATELOCKS 'DIRTY READ';
BEGIN WORK; --Begin second transaction 
SET ISOLATION TO COMMITTED READ LAST COMMITTED;
SELECT ... FOR UPDATE ...
...
COMMIT WORK;

In the first transaction above, the RETAINUPDATELOCKS setting in the SET ENVIRONMENT statement makes the retention of update locks the default behavior for the Committed Read isolation level. As a result, the database server interprets the first SET ISOLATION statement, which specifies Committed Read but has no RETAIN UPDATE LOCKS clause, as if it had included that clause:

SET ISOLATION TO 
   READ LAST COMMITTED RETAIN UPDATE LOCKS;

Because the SET ENVIRONMENT RETAINUPDATELOCKS statement in the second transaction specifies DIRTY READ as its setting, however, it has no effect on the second SET ISOLATION statement, which defines a Committed Read isolation level. Each of the settings that correspond to a specific HCL OneDB isolation level only affect update locks in transactions that use the same isolation level.

If your goal is to enable retention of update locks in the Dirty Read isolation level, this is a more appropriate example than the second transaction above:

SET ENVIRONMENT RETAINUPDATELOCKS 'DIRTY READ';
BEGIN WORK; --Begin third transaction 
SET ISOLATION TO DIRTY READ;
SELECT ... FOR UPDATE ...
...
COMMIT WORK;

In the third transaction, the database server interprets the SET ISOLATION TO DIRTY READ statement, which matches the SET ENVIRONMENT isolation level of Dirty Read, but has no RETAIN UPDATE LOCKS clause, as if it had included that clause:

SET ISOLATION TO DIRTY READ RETAIN UPDATE LOCKS;

In cross-server SELECT ... FOR UPDATE distributed queries, but some participating servers do not support update lock retention, the entire transaction conforms to the isolation level of the session that issued the transaction. If that session has an enabled RETAINUPDATELOCKS option in effect, it is also in effect for the servers that support update lock retention, but other participating servers follow their default behavior for releasing update locks.

The SET ENVIRONMENT RETAINUPDATELOCKS statement fails with error -26199 if the database in which it is issued does not support transaction logging.

Setting RETAINUPDATELOCKS in the sysdbopen( ) procedure

For a user, for a role, or for the PUBLIC group, the built-in sysdbopen( ) routine can issue the SET ENVIRONMENT RETAINUPDATELOCKS statement when the session connects to a database in which sysdbopen( ) is defined, as in the following example.

CREATE PROCEDURE PUBLIC.SYSDBOPEN()
   SET PDQPRIORITY 10;
   SET ENVIRONMENT RETAINUPDATELOCKS 'ALL';
END PROCEDURE

After the example above takes effect, it prevents other sessions from modifying rows on which you have placed an update lock, so that you can update the rows later in the current transaction. Unless you issue another SQL statement within the same session to disable the retention of update locks, the effects of a SET ENVIRONMENT RETAINUPDATELOCKS statement that sysdbopen( ) issues persists until the end of the session.

This session-long persistence of the RETAINUPDATELOCKS value that sysdbopen( ) specifies, however, is a special case. Any other SPL routine can use the SET ENVIRONMENT statements to specify update lock retention for the Committed Read, Dirty Read, or Cursor Stability transaction isolation level, or for 'ALL', but their effect persists only while the routine is executing, and not after the routine exits.

Restoring the default update lock behavior

In releases of HCL OneDB earlier than version 11.50.xC6, the most recently executed SET ISOLATION statement specified the default for subsequent transactions. If the most recent SET ISOLATION statement included the RETAIN UPDATE LOCKS clause, it was necessary to execute the SET ISOLATION statement for the same isolation level (but without the RETAIN UPDATE LOCKS clause) to disable the retention of update locks.

In this release, however, if SET ENVIRONMENT RETAINUPDATELOCKS has enabled retention, you must explicitly run the SET ENVIRONMENT RETAINUPDATELOCKS 'NONE' statement to restore non-retention as the default behavior, as in the following example.

BEGIN WORK;  --Begin first transaction
SET ISOLATION TO COMMITTED READ LAST COMMITTED;
SET ENVIRONMENT RETAINUPDATELOCKS 'COMMITTED READ';
SELECT ... FOR UPDATE ...
...
COMMIT WORK;
BEGIN WORK;  --Begin second transaction
SET ENVIRONMENT RETAINUPDATELOCKS 'NONE';
SET ISOLATION TO COMMITTED READ LAST COMMITTED;
SELECT ... FOR UPDATE ...
...

In the first transaction above, the first SET ENVIRONMENT statement modifies the behavior of the Committed Read isolation level of the current transaction to retain update locks, even though the SET ISOLATION statement that established that isolation level preceded the SET ENVIRONMENT statement in the lexical order of statements within the transaction. The LAST COMMITTED specification for this isolation level is not affected by this SET ENVIRONMENT statement.

The SET ISOLATION statement in the second transaction is interpreted literally, however, because the default behavior was reset to NONE by the second SET ENVIRONMENT statement.

Update lock retention in High Availability Clusters

In a high availability cluster environment, the RETAINUPDATELOCKS option is valid only on a primary server. Applications that require the retention of update locks must be run on the primary server if they include the SET ENVIRONMENT RETAINUPDATELOCKS statement. When it is issued from a secondary server, the statement has no effect on locking behavior, and the server returns an error.