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 ...

Get SQL in a Nutshell now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.