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

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