'\" t .\" Title: SET TRANSACTION .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets vsnapshot .\" Date: 2023 .\" Manual: PostgreSQL 13.10 Documentation .\" Source: PostgreSQL 13.10 .\" Language: English .\" .TH "SET TRANSACTION" "7" "2023" "PostgreSQL 13.10" "PostgreSQL 13.10 Documentation" .\" ----------------------------------------------------------------- .\" * Define some portability stuff .\" ----------------------------------------------------------------- .\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .\" http://bugs.debian.org/507673 .\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html .\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" ----------------------------------------------------------------- .\" * set default formatting .\" ----------------------------------------------------------------- .\" disable hyphenation .nh .\" disable justification (adjust text to left margin only) .ad l .\" ----------------------------------------------------------------- .\" * MAIN CONTENT STARTS HERE * .\" ----------------------------------------------------------------- .SH "NAME" SET_TRANSACTION \- set the characteristics of the current transaction .SH "SYNOPSIS" .sp .nf SET TRANSACTION \fItransaction_mode\fR [, \&.\&.\&.] SET TRANSACTION SNAPSHOT \fIsnapshot_id\fR SET SESSION CHARACTERISTICS AS TRANSACTION \fItransaction_mode\fR [, \&.\&.\&.] where \fItransaction_mode\fR is one of: ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } READ WRITE | READ ONLY [ NOT ] DEFERRABLE .fi .SH "DESCRIPTION" .PP The \fBSET TRANSACTION\fR command sets the characteristics of the current transaction\&. It has no effect on any subsequent transactions\&. \fBSET SESSION CHARACTERISTICS\fR sets the default transaction characteristics for subsequent transactions of a session\&. These defaults can be overridden by \fBSET TRANSACTION\fR for an individual transaction\&. .PP The available transaction characteristics are the transaction isolation level, the transaction access mode (read/write or read\-only), and the deferrable mode\&. In addition, a snapshot can be selected, though only for the current transaction, not as a session default\&. .PP The isolation level of a transaction determines what data the transaction can see when other transactions are running concurrently: .PP READ COMMITTED .RS 4 A statement can only see rows committed before it began\&. This is the default\&. .RE .PP REPEATABLE READ .RS 4 All statements of the current transaction can only see rows committed before the first query or data\-modification statement was executed in this transaction\&. .RE .PP SERIALIZABLE .RS 4 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 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 them will be rolled back with a serialization_failure error\&. .RE The SQL standard defines one additional level, READ UNCOMMITTED\&. In PostgreSQL READ UNCOMMITTED is treated as READ COMMITTED\&. .PP The transaction isolation level cannot be changed after the first query or data\-modification statement (\fBSELECT\fR, \fBINSERT\fR, \fBDELETE\fR, \fBUPDATE\fR, \fBFETCH\fR, or \fBCOPY\fR) of a transaction has been executed\&. See Chapter\ \&13 for more information about transaction isolation and concurrency control\&. .PP 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; COMMENT, 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\&. .PP The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and READ ONLY\&. When all three of these properties are selected for 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 canceled by a serialization failure\&. This mode is well suited for long\-running reports or backups\&. .PP The SET TRANSACTION SNAPSHOT command allows a new transaction to run with the same snapshot as an existing transaction\&. The pre\-existing transaction must have exported its snapshot with the pg_export_snapshot function (see Section\ \&9.27.5)\&. That function returns a snapshot identifier, which must be given to SET TRANSACTION SNAPSHOT to specify which snapshot is to be imported\&. The identifier must be written as a string literal in this command, for example \*(Aq00000003\-0000001B\-1\*(Aq\&. SET TRANSACTION SNAPSHOT can only be executed at the start of a transaction, before the first query or data\-modification statement (\fBSELECT\fR, \fBINSERT\fR, \fBDELETE\fR, \fBUPDATE\fR, \fBFETCH\fR, or \fBCOPY\fR) of the transaction\&. Furthermore, the transaction must already be set to SERIALIZABLE or REPEATABLE READ isolation level (otherwise, the snapshot would be discarded immediately, since READ COMMITTED mode takes a new snapshot for each command)\&. If the importing transaction uses SERIALIZABLE isolation level, then the transaction that exported the snapshot must also use that isolation level\&. Also, a non\-read\-only serializable transaction cannot import a snapshot from a read\-only transaction\&. .SH "NOTES" .PP If \fBSET TRANSACTION\fR is executed without a prior \fBSTART TRANSACTION\fR or \fBBEGIN\fR, it emits a warning and otherwise has no effect\&. .PP It is possible to dispense with \fBSET TRANSACTION\fR by instead specifying the desired \fItransaction_modes\fR in \fBBEGIN\fR or \fBSTART TRANSACTION\fR\&. But that option is not available for \fBSET TRANSACTION SNAPSHOT\fR\&. .PP The session default transaction modes can also be set or examined via the configuration parameters default_transaction_isolation, default_transaction_read_only, and default_transaction_deferrable\&. (In fact \fBSET SESSION CHARACTERISTICS\fR is just a verbose equivalent for setting these variables with \fBSET\fR\&.) This means the defaults can be set in the configuration file, via \fBALTER DATABASE\fR, etc\&. Consult Chapter\ \&19 for more information\&. .PP The current transaction\*(Aqs modes can similarly be set or examined via the configuration parameters transaction_isolation, transaction_read_only, and transaction_deferrable\&. Setting one of these parameters acts the same as the corresponding \fBSET TRANSACTION\fR option, with the same restrictions on when it can be done\&. However, these parameters cannot be set in the configuration file, or from any source other than live SQL\&. .SH "EXAMPLES" .PP To begin a new transaction with the same snapshot as an already existing transaction, first export the snapshot from the existing transaction\&. That will return the snapshot identifier, for example: .sp .if n \{\ .RS 4 .\} .nf BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT pg_export_snapshot(); pg_export_snapshot \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- 00000003\-0000001B\-1 (1 row) .fi .if n \{\ .RE .\} .sp Then give the snapshot identifier in a \fBSET TRANSACTION SNAPSHOT\fR command at the beginning of the newly opened transaction: .sp .if n \{\ .RS 4 .\} .nf BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION SNAPSHOT \*(Aq00000003\-0000001B\-1\*(Aq; .fi .if n \{\ .RE .\} .SH "COMPATIBILITY" .PP These commands are defined in the SQL standard, except for the DEFERRABLE transaction mode and the \fBSET TRANSACTION SNAPSHOT\fR form, which are PostgreSQL extensions\&. .PP SERIALIZABLE is the default transaction isolation level in the standard\&. In PostgreSQL the default is ordinarily READ COMMITTED, but you can change it as mentioned above\&. .PP 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 PostgreSQL server\&. .PP The SQL standard requires commas between successive \fItransaction_modes\fR, but for historical reasons PostgreSQL allows the commas to be omitted\&.