diff options
Diffstat (limited to 'doc/src/sgml/man7/SET_TRANSACTION.7')
| -rw-r--r-- | doc/src/sgml/man7/SET_TRANSACTION.7 | 242 |
1 files changed, 242 insertions, 0 deletions
diff --git a/doc/src/sgml/man7/SET_TRANSACTION.7 b/doc/src/sgml/man7/SET_TRANSACTION.7 new file mode 100644 index 0000000..acbe84a --- /dev/null +++ b/doc/src/sgml/man7/SET_TRANSACTION.7 @@ -0,0 +1,242 @@ +'\" t +.\" Title: SET TRANSACTION +.\" Author: The PostgreSQL Global Development Group +.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/> +.\" Date: 2023 +.\" Manual: PostgreSQL 15.5 Documentation +.\" Source: PostgreSQL 15.5 +.\" Language: English +.\" +.TH "SET TRANSACTION" "7" "2023" "PostgreSQL 15.5" "PostgreSQL 15.5 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, +\fBMERGE\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: +\fBINSERT\fR, +\fBUPDATE\fR, +\fBDELETE\fR, +\fBMERGE\fR, and +\fBCOPY FROM\fR +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, +\fBMERGE\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\ \&20 +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\&. |