Configure transaction properties with SET TRANSACTION - PolarDB

SET TRANSACTION sets the characteristics of the current transaction. SET SESSION CHARACTERISTICS sets the default transaction characteristics for all subsequent transactions in a session. SET TRANSACTION overrides session-level defaults for an individual 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

Transaction characteristics

Isolation level

The isolation level controls what data a transaction can see when other transactions run concurrently.

Level	Behavior	Default
`READ COMMITTED`	Each statement sees only rows committed before that statement began.	Yes
`REPEATABLE READ`	All statements see only rows committed before the first query or data-modification statement in the transaction.	No
`SERIALIZABLE`	Same visibility as `REPEATABLE READ`. If concurrent serializable transactions create a situation that could not have occurred in any serial (one-at-a-time) execution, one of them rolls back with a `serialization_failure` error.	No
`READ UNCOMMITTED`	SQL standard level. In PostgreSQL, treated as `READ COMMITTED`.	No

The isolation level cannot be changed after the first query or data-modification statement — SELECT, INSERT, DELETE, UPDATE, MERGE, FETCH, or COPY — has executed in the transaction.

Access mode

The access mode determines whether the transaction is read/write or read-only. The default is READ WRITE.

When a transaction is READ ONLY, the following commands are disallowed if they would write to a non-temporary table:

INSERT, UPDATE, DELETE, MERGE, COPY FROM

The following commands are unconditionally disallowed:

All CREATE, ALTER, and DROP commands
COMMENT, GRANT, REVOKE, TRUNCATE
EXPLAIN ANALYZE and EXECUTE, if the command they execute is among those listed above

READ ONLY is a high-level restriction and does not prevent all writes to disk.

DEFERRABLE

DEFERRABLE has no effect unless the transaction is also SERIALIZABLE and READ ONLY. When all three properties are set, the transaction may block when first acquiring its snapshot, but then runs without the normal overhead of a SERIALIZABLE transaction and without any risk of contributing to or being canceled by a serialization failure. This combination is well suited for long-running reports or backups.

Snapshot

SET TRANSACTION SNAPSHOT lets a new transaction run with the same snapshot as an existing transaction. The existing transaction must export its snapshot with pg_export_snapshot(), which returns a snapshot identifier. Pass that identifier as a string literal to SET TRANSACTION SNAPSHOT.

Constraints:

Execute SET TRANSACTION SNAPSHOT before the first query or data-modification statement in the transaction.
The transaction must use SERIALIZABLE or REPEATABLE READ isolation level. Under READ COMMITTED, each command takes a new snapshot, so the imported snapshot would be discarded immediately.
If the importing transaction uses SERIALIZABLE, the exporting transaction must also use SERIALIZABLE.
A non-read-only serializable transaction cannot import a snapshot from a read-only transaction.

Usage notes

If SET TRANSACTION runs without a prior START TRANSACTION or BEGIN, it emits a warning and has no effect.
To avoid SET TRANSACTION, specify the desired transaction_mode values directly in BEGIN or START TRANSACTION. This alternative is not available for SET TRANSACTION SNAPSHOT.
Session-level defaults can also be set via the configuration parameters default_transaction_isolation, default_transaction_read_only, and default_transaction_deferrable. SET SESSION CHARACTERISTICS is equivalent to setting these parameters with SET. This means defaults can be configured in the configuration file, via ALTER DATABASE, and so on.

Examples

Share a snapshot between transactions

Export the snapshot from the existing transaction:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();
 pg_export_snapshot
---------------------
 00000003-0000001B-1
(1 row)

Import the snapshot in the new transaction:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '00000003-0000001B-1';