O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required