Differences between SET and SET TRANSACTION

The SET and SET TRANSACTION commands are used for setting various transaction properties, such as isolation level, the read level, or SQL passthrough mode.

The SET commands set the properties for a session and they are often called session-level commands. The SET TRANSACTION commands set the properties for one transaction and they are called transaction-level commands.

The transaction-level commands follow different rules from the session-level commands. These differences are listed below.

▪The transaction-level commands take effect in the transaction in which they are issued; the session-level commands take effect in the next transaction, that is, after the next COMMIT WORK.

▪The transaction-level commands apply to only the current transaction; the session-level commands apply to all subsequent transactions — that is, until the end of the session (connection) or until another SET command changes them.

▪The transaction-level commands must be executed at the beginning of a transaction, that is, before any DML or DDL statements. However, they may be executed after other SET statements. If this rule is violated, an error is returned. The session-level commands may be executed at any point in a transaction.

▪The transaction-level commands take precedence over the session-level commands. However, the transaction-level commands apply only to the current transaction. After the current transaction is committed or aborted, the settings will return to the value set by the most recent previous SET command (if any). For example:

COMMIT WORK; -- Finish previous transaction;
SET ISOLATION LEVEL SERIALIZABLE;
COMMIT WORK; -- Isolation level is now SERIALIZABLE
...
COMMIT WORK;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Isolation level is now REPEATABLE READ because
-- transaction-level settings take precedence
-- over session-level settings.
COMMIT WORK;
-- Isolation level is now back to SERIALIZABLE, since the
-- transaction-level settings applied only to that
-- transaction.

The complete precedence hierarchy for isolation level and read level settings is below. Items closer to the top of the list have higher precedence.

3 The server-level settings specified by the value in solid.ini configuration parameter (for example, IsolationLevel or DurabilityLevel