USELASTCOMMITTED session environment option

The USELASTCOMMITTED session environment option can improve concurrency in sessions that use the Committed Read, Dirty Read, Read Committed, or Read Uncommitted isolation levels by reducing the risk of locking conflicts when two or more sessions attempt to access the same row in a table whose locking granularity is row-level locking.

The SET ENVIRONMENT USELASTCOMMITTED statement of SQL supports the following syntax:

Usage

The SET ENVIRONMENT USELASTCOMMITTED statement can specify whether queries and other operations that encounter exclusive locks that other sessions hold while changing data values should use the most recently committed version of the data, rather than wait for the lock to be released.

This statement can override the USELASTCOMMITTED configuration parameter setting for the duration of the current session. You can use the SET ISOLATION statement to override the USELASTCOMMITTED session environment setting.

The USELASTCOMMITTED session environment option can have any one of four values:

'COMMITTED READ': The database server reads the most recently committed version of the data when it encounters an exclusive lock while attempting to read a row in the Committed Read or Read Committed isolation level.
'DIRTY READ': The database server reads the most recently committed version of the data if it encounters an exclusive lock while attempting to read a row in the Dirty Read or Read Uncommitted isolation level.
'ALL': The database server reads the most recently committed version of the data if it encounters an exclusive lock while attempting to read a row in the Committed Read, Dirty Read, Read Committed, or Read Uncommitted isolation level.
'NONE': This value disables the USELASTCOMMITTED feature that can access the last committed version of data in a locked row. Under this setting, if your session encounters an exclusive lock when attempting to read a row in the Committed Read, Dirty Read, Read Committed, or Read Uncommitted isolation level, your transaction cannot read that row until the concurrent transaction that holds the exclusive lock is committed or rolled back.

Examples of SET ENVIRONMENT USELASTCOMMITTED

The following statements specify the Committed Read isolation mode and replace the explicit or default USELASTCOMMITTED configuration parameter setting with a setting that reads the most recently committed version of the data in rows on which concurrent readers hold an exclusive lock:

SET ISOLATION COMMITTED READ;
SET ENVIRONMENT USELASTCOMMITTED 'ALL';

By including the following statements within a PUBLIC.sysdbopen or user.sysdbopen procedure, specify at connection time the Committed Read isolation mode and replace the explicit or default USELASTCOMMITTED configuration parameter setting with a setting that reads the most recently committed version of the data in tables on which concurrent readers hold an exclusive lock:

SET ENVIRONMENT USELASTCOMMITTED 'COMMITTED READ';

Besides sysdbopen( ), any SPL routine can use these statements to specify the Committed Read Last Committed transaction isolation level during a session. These statements enable SQL operations that read data to use the last committed version when an exclusive lock is encountered during an operation that reads a table. This can avoid deadlock situations or other locking errors when another session is attempting to modify the same row or table. It does not reduce the risk of locking conflicts with other sessions that are writing to tables, or with concurrent DDL transactions that hold implicit or explicit locks on a user table or on a system catalog table.

In cross-server distributed queries, if the isolation level of the session that issued the query has the LAST COMMITTED isolation level option in effect, but one or more of the participating databases does not support this LAST COMMITTED feature, then the entire transaction conforms to the Committed Read or Dirty Read isolation level of the session that issued the transaction, without the LAST COMMITTED option enabled.

The next example enables the database server reads the most recently committed version in transactions where the isolation level is Dirty Read or Read Uncommitted:

SET ENVIRONMENT USELASTCOMMITTED 'DIRTY READ';

The following example disables the explicit or default USELASTCOMMITTED configuration parameter setting, so that in the Committed Read, Dirty Read, Read Committed, or Read Uncommitted isolation, DML operations that encounter exclusive locks during the session must wait until the lock is released before reading the last committed version of the row:

SET ENVIRONMENT USELASTCOMMITTED 'NONE';

Restrictions on the USELASTCOMMITTED option

This session environment option is designed for operations on tables that use a locking granularity of ROW. For operations on tables with a locking granularity of TABLE, the USELASTCOMMITTED setting does not reduce the risk of locking conflicts when concurrent processes attempt to access the same tables.

For information about additional restrictions that can prevent a transaction from reading the most recently committed data from a table locked by another transaction while USELASTCOMMITTED is enabled, see The LAST COMMITTED Option to Committed Read.

For more information about the USELASTCOMMITTED configuration parameter, see your HCL OneDB™ Administrator's Reference.