SQL in a Nutshell, 3rd Edition

Name

SET TRANSACTION Statement

The SET TRANSACTION statement controls many characteristics of data modification, primarily the read/write characteristics and isolation level of a transaction.

Platform	Command
MySQL	Supported, with variations
Oracle	Supported, with limitations
PostgreSQL	Supported
SQL Server	Supported, with variations

SQL2003 Syntax

SET [LOCAL] TRANSACTION  [READ ONLY | READ WRITE]
   [ISOLATION LEVEL {READ COMMITTED | READ UNCOMMITTED |
      REPEATABLE READ | SERIALIZABLE}]
   [DIAGNOSTIC SIZE int]

Keywords

LOCAL

Changes transaction settings for the current session on the local server only. If this keyword is not specified, the transaction settings for the next transaction are changed, even if the transaction runs on a remote server.

READ ONLY

Sets the next upcoming transaction as a read-only transaction. Once the next transaction is complete, transaction behavior reverts to the default settings.

READ WRITE

Sets the next upcoming transaction so it may perform transactions that read and write data.

ISOLATION LEVEL

Sets the isolation level for the next transaction in the session.

READ COMMITTED

Allows a transaction to read rows written by other transactions only when they have been committed.

READ UNCOMMITTED

Allows a transaction to read rows that have been written, but not committed, by other transactions.

REPEATABLE READ

All sessions can see records that are committed before their first transactions were begun. Other open sessions can see or change only committed rows in the user’s current session. Consequently, ...