SQL in a Nutshell

Name

SET TRANSACTION

Synopsis

The SET TRANSACTION statement controls many characteristics of a data modification, such as read/write or its isolation level.

Vendor	Command
SQL Server	Supported, with variations
MySQL	Not supported
Oracle	Supported, with limitations
PostgreSQL	Supported

SQL99 Syntax and Description

SET [LOCAL] TRANSACTION { {READ ONLY | READ WRITE}[,...]
| ISOLATION LEVEL 
  {READ COMMITTED 
  | READ UNCOMMITTED 
  | REPEATABLE READ
  | SERIALIZABLE}[,...]
| DIAGNOSTIC SIZE INT};

When issued, this command is outside the context of a transaction but applies to the next valid transaction. More than one option may be applied with this command, each separated by a comma.

The transaction settings may be applied only to the local server via the LOCAL command. Otherwise, the transaction settings are assumed to apply regardless of where the transaction is run. This option is new to SQL99.

A transaction also can be specified as READ ONLY or READ WRITE. The DIAGNOSTIC SIZE clause, followed by an integer, designates the specific number of error messages to capture for a transaction. The GET DIAGNOSTICS statement retrieves this information.

The ISOLATION LEVEL clause controls a number of behaviors in a transaction concerning concurrent transactions. Isolation levels control how transactions behave with regards to dirty reads, non-repeatable reads, and phantom records:

Dirty reads

Occur when a transaction reads the altered records of another transaction before the other transaction ...