SET statements apply to the user session (connection) in which they are executed. They do not affect other user sessions.
SET statements are not subject to rollback; they remain in force even if the transaction that contains the statements is aborted or rolled back. It is good practice to execute the SET statements before any DDL/DML SQL statement in a transaction.
The settings continue in effect until the end of the session (connection) or until another SET statement changes the settings, or in some cases until a higher-precedence statement (for example, SET TRANSACTION) is executed.
Differences between SET and SET TRANSACTION
The SET and SET TRANSACTION statements are used for setting various transaction properties, such as isolation level, and the read level.
The SET statements set the properties for a session and they are often called session-level statements. The SET TRANSACTION statements set the properties for one transaction and they are called transaction-level statements.
The transaction-level statements follow different rules from the session-level statements:
▪ The transaction-level statements take effect only in the transaction in which they are executed; the session-level statements take effect in the next transaction, that is, after the next COMMIT WORK.
Note Some SET statements take effect in the current transaction as well as the following transactions. See individual statements for details.
▪ The transaction-level statements apply to only the current transaction; the session-level statements apply to all subsequent transactions — that is, until the end of the session (connection) or until another SET statement changes them.
▪ The transaction-level statements must be executed at the beginning of a transaction, that is, before any DML or DDL statements. However, they can be executed after other SET statements. If this rule is violated, an error is returned. The session-level statements can be executed at any point in a transaction.
▪ The transaction-level statements take precedence over the session-level statements. However, the transaction-level statements 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 statement (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 following list shows the complete precedence hierarchy for isolation level and read level settings. Items closer to the top of the list have higher precedence.
1 SET TRANSACTION ... (transaction-level settings)
2 SET ... (session-level settings)
3 The server-level settings that are specified by the value in the solid.ini configuration parameter (for example, IsolationLevel or DurabilityLevel
4 The server default setting (parameter factory value)