SQL in a Nutshell, 2nd Edition

Name

SET TRANSACTION Statement

Synopsis

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

Platform	Command
DB2	Not supported
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 and only the local server. Otherwise, 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 which have been written, but not committed, by other transactions.

REPEATABLE READ

All sessions can see records that are committed before their first transaction was begun. Other open sessions can see or change ...