SET TRANSACTION

SET TRANSACTION — Set the characteristics of the current transaction

Synopsis

SET TRANSACTION transaction_mode [, ...]

SET TRANSACTION SNAPSHOT snapshot_id

SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]

where transaction_mode is one of:

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

UNCOMMITTED }

READ WRITE | READ ONLY

[ NOT ] DEFERRABLE

Description

SET TRANSACTION sets the characteristics of the current transaction.

SET SESSION CHARACTERISTICS sets the default transaction characteristics for subsequent transactions of a session. These defaults can be overridden with SET TRANSACTION in individual transactions.

The available transaction characteristics are the transaction isolation level, the transaction access mode (read/write or read-only), and the deferrable mode. Additionally, a snapshot can be selected, but only for the current transaction, not as a session default.

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 of the current transaction can only see rows committed before the first query or data modification statement was executed in this transaction.

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. If the read/write patterns of concurrent serializable transactions could lead to a situation that would not be possible if those transactions were executed serially (one at a time), one of them will be rolled back with a serialization_failure error.

The SQL standard defines an additional level: READ UNCOMMITTED. In Halo, READ UNCOMMITTED is treated as READ COMMITTED.

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

The access mode of a transaction determines whether the transaction is read/write or read-only. Read/write is the default. When a transaction is read-only, SQL commands INSERT, UPDATE, DELETE, and COPY FROM are not allowed if the table to be written is not a temporary table. CREATE, ALTER, and DROP commands are not allowed. COMMENT, GRANT, REVOKE, and TRUNCATE are not allowed. If EXPLAIN ANALYZE and EXECUTE would execute any of the above commands, they are also not allowed. This is a high-level notion of read-only that does not prevent all disk writes.

The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and READ ONLY. When all three properties are selected for a transaction, the transaction may block when it first acquires its snapshot, after which it runs without the overhead of a SERIALIZABLE transaction and without any risk of being canceled by a serialization failure. This mode is well-suited for long-running reports or backups. The SET TRANSACTION SNAPSHOT command allows a new transaction to run using the same snapshot as an existing transaction. The pre-existing transaction must have exported its snapshot using the pg_export_snapshot function. This function returns a snapshot identifier, and SET TRANSACTION SNAPSHOT needs to be given a snapshot identifier to specify the snapshot to import. In this command, the identifier must be written as a string literal, for example '000003A1-1'. SET TRANSACTION SNAPSHOT can only be executed at the start of a transaction, before the first query or data modification statement (SELECT, INSERT, DELETE, UPDATE, FETCH, or COPY) in that transaction. Additionally, the transaction must already have been set to SERIALIZABLE or REPEATABLE READ isolation level (otherwise, the snapshot would be immediately discarded, since READ COMMITTED mode takes a new snapshot for each command). If the importing transaction uses the SERIALIZABLE isolation level, the transaction importing the snapshot must also use that isolation level. Also, a non-read-only serializable transaction cannot import a snapshot from a read-only transaction.

Notes

If SET TRANSACTION is executed without a prior START TRANSACTION or BEGIN, it will issue a warning and have no effect.

You can avoid using SET TRANSACTION by specifying the desired transaction_modes in BEGIN or START TRANSACTION. However, this option is not available for SET TRANSACTION SNAPSHOT.

Session default transaction modes can also be set by configuring the parameters default_transaction_isolation, default_transaction_read_only, and default_transaction_deferrable (in fact, SET SESSION CHARACTERISTICS is simply the equivalent of using SET to set these variables). This means defaults can be set via configuration files, ALTER DATABASE, etc.

Examples

To begin a new transaction with the same snapshot as an existing transaction, first export the snapshot from the existing transaction. This will return the snapshot identifier, for example:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT pg_export_snapshot();

pg_export_snapshot

---------------------

00000003-0000001B-1

(1 row)

Then use the snapshot identifier in a SET TRANSACTION SNAPSHOT command at the beginning of a newly started transaction:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION SNAPSHOT '00000003-0000001B-1';