CLUSTER_TXN_SCOPE session environment option

When a client session in a high-availability cluster server issues a commit, the server blocks the current session until the committed transaction is applied in that session, or applied on a secondary server, or applied across the cluster, depending on the setting of the CLUSTER_TXN_SCOPE session environment option of the SET ENVIRONMENT statement.

The CLUSTER_TXN_SCOPE session environment option has this syntax:

Usage

In a cluster environment, this statement can apply a different transaction scope that overrides the setting of the CLUSTER_TXN_SCOPE configuration parameter for the current user session. It can also restore the effects of that onconfig file setting, after a SET ENVIRONMENT statement in the same session overrode the configuration parameter setting.

To use this transaction coordination feature, specify one of the following options, delimited by single ( ' ) or double ( " ) quotation marks, immediately after the SET ENVIRONMENT CLUSTER_TXN_SCOPE keywords. The syntax diagram is simplified by omitting the " delimiters.

'SESSION': When a client session issues a commit, the database server blocks the session until the effects of the transaction commit are returned to that session. After control is returned to the session, other sessions at the same database server instance or on other database servers in the same cluster might be unaware of the transaction commit and of the effects of the transaction.
'SERVER': 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. This is the default behavior, unless the CLUSTER_TXN_SCOPE configuration parameter has a nondefault setting for your database server instance.
'CLUSTER': When a client session issues a commit, the database server blocks the session until the transaction is applied at all database servers in the high-availability cluster, excluding RS secondary servers that have the DELAY_APPLY or STOP_APPLY configuration parameters enabled. Other sessions at any database server in the high-availability cluster, excluding RS secondary servers that are using DELAY_APPLY or DELAY_APPLY, are aware of the transaction commit and the transaction's effects.
'DEFAULT': The cluster transaction scope reverts to the CLUSTER_TXN_SCOPE configuration parameter setting in the onconfig file of the database server instance, if that parameter is set.

The CLUSTER_TXN_SCOPE setting affects sessions on read-only secondary servers and on updatable secondary servers. Transactions do not need to be applied on the RS secondary servers before client applications can receive commits.

Examples of setting CLUSTER_TXN_SCOPE

This statement sets the cluster as the scope of transaction coordination during the session:

SET ENVIRONMENT CLUSTER_TXN_SCOPE 'CLUSTER';

This setting minimizes the risk of transaction coordination failure, but it can increase the time required for the session to receive the transaction commit. For the session with this setting, after the primary server sends logical log buffers to the HDR secondary server, it returns control to the session, but the session does not receive a commit until the transaction is applied on all of the servers in the cluster.

The next example specifies the local database server as the scope of transaction coordination:

SET ENVIRONMENT CLUSTER_TXN_SCOPE 'SERVER';

The 'SERVER' setting can reduce the risk of transaction coordination failure when different sessions on the same server are concurrently processing data in the same tables.

This example allows processing to resume after a transaction commit is returned to the current session:

SET ENVIRONMENT CLUSTER_TXN_SCOPE 'SESSION';

This setting might be appropriate if the session is accessing database objects that are unlikely to be referenced by concurrent sessions of the same server, or of other servers in the same cluster.

The following statement makes the value for the current setting match the CLUSTER_TXN_SCOPE configuration parameter setting of the local server instance:

SET ENVIRONMENT CLUSTER_TXN_SCOPE "DEFAULT";

If the CLUSTER_TXN_SCOPE configuration parameter setting for the server is CLUSTER or SERVER, then"DEFAULT" might have been a more appropriate CLUSTER_TXN_SCOPE session environment setting than the previous "SESSION" example, if transactions in concurrent sessions produce unexpected results.