Description

The SET TRANSACTION command sets the characteristics of the current
transaction. It has no effect on any subsequent transactions.

The available transaction characteristics are the transaction isolation level and the
transaction access mode (read/write or read-only).

The isolation level of a transaction determines what data the transaction can see when
other transactions are running concurrently.

READ COMMITTED — A statement can only see rows committed before it
began. This is the default.

SERIALIZABLE — All statements of the current transaction can only
see rows committed before the first query or data-modification statement was executed in
this transaction.

The SQL standard defines two additional levels, READ UNCOMMITTED and
REPEATABLE READ. In Greenplum Database READ UNCOMMITTED
is treated as READ COMMITTED. REPEATABLE READ is not
supported; use SERIALIZABLE if REPEATABLE READ behavior is
required.

The transaction isolation level cannot be changed after the first query or
data-modification statement (SELECT, INSERT,
DELETE, UPDATE, FETCH, or
COPY) of a transaction has been executed.

The transaction access mode determines whether the transaction is read/write or read-only.
Read/write is the default. When a transaction is read-only, the following SQL commands are
disallowed: INSERT, UPDATE, DELETE, and
COPY FROM if the table they would write to is not a temporary table; all
CREATE, ALTER, and DROP commands;
GRANT, REVOKE, TRUNCATE; and
EXPLAIN ANALYZE and EXECUTE if the command they would
execute is among those listed. This is a high-level notion of read-only that does not
prevent all writes to disk.

Parameters

SESSION CHARACTERISTICS

Sets the default transaction characteristics for subsequent transactions of a
session.

SERIALIZABLE

READ COMMITTED

READ UNCOMMITTED

The SQL standard defines four transaction isolation levels: READ
COMMITTED, READ UNCOMMITTED, SERIALIZABLE,
and REPEATABLE READ. The default behavior is that a statement can only
see rows committed before it began (READ COMMITTED). In Greenplum
Database READ UNCOMMITTED is treated the same as READ
COMMITTED. REPEATABLE READ is not supported; use
SERIALIZABLE instead. SERIALIZABLE is the strictest
transaction isolation. This level emulates serial transaction execution, as if
transactions had been executed one after another, serially, rather than concurrently.
Applications using this level must be prepared to retry transactions due to
serialization failures.

READ WRITE

READ ONLY

Determines whether the transaction is read/write or read-only. Read/write is the
default. When a transaction is read-only, the following SQL commands are disallowed:
INSERT, UPDATE, DELETE, and
COPY FROM if the table they would write to is not a temporary table;
all CREATE, ALTER, and DROP commands;
GRANT, REVOKE, TRUNCATE; and
EXPLAIN ANALYZE and EXECUTE if the command they
would execute is among those listed.

Notes

If SET TRANSACTION is executed without a prior START
TRANSACTION or BEGIN, it will appear to have no effect.

It is possible to dispense with SET TRANSACTION by instead specifying the
desired transaction_modes in BEGIN or START TRANSACTION.

The session default transaction modes can also be set by setting the configuration
parameters default_transaction_isolation and
default_transaction_read_only.

Examples

Set the transaction isolation level for the current transaction:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Compatibility

Both commands are defined in the SQL standard. SERIALIZABLE is the default
transaction isolation level in the standard. In Greenplum Database the default is
READ COMMITTED. Because of lack of predicate locking, the
SERIALIZABLE level is not truly serializable. Essentially, a
predicate-locking system prevents phantom reads by restricting what is written, whereas a
multi-version concurrency control model (MVCC) as used in Greenplum Database prevents them
by restricting what is read.

In the SQL standard, there is one other transaction characteristic that can be set with
these commands: the size of the diagnostics area. This concept is specific to embedded SQL,
and therefore is not implemented in the Greenplum Database server.

The SQL standard requires commas between successive transaction_modes,
but for historical reasons Greenplum Database allows the commas to be omitted.