SET TRANSACTION

Contents

Synopsis
Description
Parameters
Notes
Examples
Compatibility
See also

Sets the characteristics of the current transaction.

SET TRANSACTION [<transaction_mode>] [READ ONLY | READ WRITE]

SET TRANSACTION SNAPSHOT <snapshot_id>

SET SESSION CHARACTERISTICS AS TRANSACTION <transaction_mode>
     [READ ONLY | READ WRITE]
     [NOT] DEFERRABLE

where transaction_mode is one of:

ISOLATION LEVEL {SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED}

and snapshot_id is the ID of the existing transaction whose snapshot you want this transaction to run with.

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, the transaction access mode (read/write or read-only), and the deferrable mode.

NOTE

Deferrable transactions require the transaction to be serializable. Greengage DB does not support serializable transactions, so including the DEFERRABLE clause has no effect.

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.
REPEATABLE READ — all statements in the current transaction can only see rows committed before the first query or data-modification statement run in the transaction.

The SQL standard defines two additional levels, READ UNCOMMITTED and SERIALIZABLE. In Greengage DB, READ UNCOMMITTED is treated as READ COMMITTED. If you specify SERIALIZABLE, Greengage DB falls back to REPEATABLE READ.

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

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;
EXPLAIN ANALYZE and EXECUTE — if the command they would run is among those listed.

This is a high-level notion of read-only that does not prevent all writes to disk.

The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and READ ONLY. When all of these properties are set on a transaction, the transaction may block when first acquiring its snapshot, after which it is able to run without the normal overhead of a SERIALIZABLE transaction and without any risk of contributing to or being cancelled by a serialization failure. Because Greengage DB does not support serializable transactions, the DEFERRABLE transaction property has no effect in Greengage DB.

Parameter Description

Parameter	Description
SNAPSHOT	Allows a new transaction to run with the same snapshot as an existing transaction. You pass the ID of the existing transaction to the `SET TRANSACTION SNAPSHOT` command. You must first call the `pg_export_snapshot` function to obtain the existing transaction’s ID
SESSION CHARACTERISTICS	Sets the default transaction characteristics for subsequent transactions of a session
READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE	The SQL standard defines four transaction isolation levels: `READ UNCOMMITTED`, `READ COMMITTED`, `REPEATABLE READ`, and `SERIALIZABLE`. `READ UNCOMMITTED` allows transactions to see changes made by uncommitted concurrent transactions. This is not possible in Greengage DB, so `READ UNCOMMITTED` is treated the same as `READ COMMITTED`. `READ COMMITTED`, the default isolation level in Greengage DB, guarantees that a statement can only see rows committed before it began. The same statement run twice in a transaction can produce different results if another concurrent transaction commits after the statement is run the first time. The `REPEATABLE READ` isolation level guarantees that a transaction can only see rows committed before it began. `REPEATABLE READ` is the strictest transaction isolation level Greengage DB supports. Applications that use the `REPEATABLE READ` isolation level must be prepared to retry transactions due to serialization failures. The `SERIALIZABLE` transaction isolation level guarantees that all statements of the current transaction can only see rows committed before the first query or data-modification statement was run in this transaction. If a pattern of reads and writes among concurrent serializable transactions would create a situation which could not have occurred for any serial (one-at-a-time) execution of those transactions, one of the transactions will be rolled back with a `serialization_failure` error. Greengage DB does not fully support `SERIALIZABLE` as defined by the standard, so if you specify `SERIALIZABLE`, Greengage DB falls back to `REPEATABLE READ`. See Compatibility for more information about transaction serializability in Greengage DB
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 run is among those listed
[NOT] DEFERRABLE	The `DEFERRABLE` transaction property has no effect in Greengage DB because `SERIALIZABLE` transactions are not supported. If `DEFERRABLE` is specified and the transaction is also `SERIALIZABLE` and `READ ONLY`, the transaction may block when first acquiring its snapshot, after which it is able to run without the normal overhead of a `SERIALIZABLE` transaction and without any risk of contributing to or being cancelled by a serialization failure. This mode is well suited for long-running reports or backups

SNAPSHOT

Allows a new transaction to run with the same snapshot as an existing transaction. You pass the ID of the existing transaction to the SET TRANSACTION SNAPSHOT command. You must first call the pg_export_snapshot function to obtain the existing transaction’s ID

SESSION CHARACTERISTICS

Sets the default transaction characteristics for subsequent transactions of a session

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

The SQL standard defines four transaction isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

READ UNCOMMITTED allows transactions to see changes made by uncommitted concurrent transactions. This is not possible in Greengage DB, so READ UNCOMMITTED is treated the same as READ COMMITTED.

READ COMMITTED, the default isolation level in Greengage DB, guarantees that a statement can only see rows committed before it began. The same statement run twice in a transaction can produce different results if another concurrent transaction commits after the statement is run the first time.

The REPEATABLE READ isolation level guarantees that a transaction can only see rows committed before it began. REPEATABLE READ is the strictest transaction isolation level Greengage DB supports. Applications that use the REPEATABLE READ isolation level must be prepared to retry transactions due to serialization failures.

The SERIALIZABLE transaction isolation level guarantees that all statements of the current transaction can only see rows committed before the first query or data-modification statement was run in this transaction. If a pattern of reads and writes among concurrent serializable transactions would create a situation which could not have occurred for any serial (one-at-a-time) execution of those transactions, one of the transactions will be rolled back with a serialization_failure error. Greengage DB does not fully support SERIALIZABLE as defined by the standard, so if you specify SERIALIZABLE, Greengage DB falls back to REPEATABLE READ. See Compatibility for more information about transaction serializability in Greengage DB

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 run is among those listed

[NOT] DEFERRABLE

The DEFERRABLE transaction property has no effect in Greengage DB because SERIALIZABLE transactions are not supported. If DEFERRABLE is specified and the transaction is also SERIALIZABLE and READ ONLY, the transaction may block when first acquiring its snapshot, after which it is able to run without the normal overhead of a SERIALIZABLE transaction and without any risk of contributing to or being cancelled by a serialization failure. This mode is well suited for long-running reports or backups

If SET TRANSACTION is run without a prior START TRANSACTION or BEGIN, a warning is issued and the command has 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, default_transaction_read_only, and default_transaction_deferrable.

Set the transaction isolation level for the current transaction:

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Both commands are defined in the SQL standard. SERIALIZABLE is the default transaction isolation level in the standard. In Greengage DB, the default is READ COMMITTED. Due to lack of predicate locking, Greengage DB does not fully support the SERIALIZABLE level, so it falls back to the REPEATABLE READ level when SERIALIZABLE is specified. Essentially, a predicate-locking system prevents phantom reads by restricting what is written, whereas a multi-version concurrency control model (MVCC) as used in Greengage DB prevents them by restricting what is read.

PostgreSQL provides a true serializable isolation level, called serializable snapshot isolation (SSI), which monitors concurrent transactions and rolls back transactions that could introduce serialization anomalies. Greengage DB does not implement this isolation mode.

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 Greengage DB server.

The DEFERRABLE transaction mode is a Greengage DB language extension.

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

BEGIN
LOCK

SET SESSION AUTHORIZATION

SHOW

SET TRANSACTION

Synopsis

Description

Parameters

Notes

Examples

Compatibility

See also