1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>SET TRANSACTION</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-set-session-authorization.html" title="SET SESSION AUTHORIZATION" /><link rel="next" href="sql-show.html" title="SHOW" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">SET TRANSACTION</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-set-session-authorization.html" title="SET SESSION AUTHORIZATION">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-show.html" title="SHOW">Next</a></td></tr></table><hr></hr></div><div class="refentry" id="SQL-SET-TRANSACTION"><div class="titlepage"></div><a id="id-1.9.3.177.1" class="indexterm"></a><a id="id-1.9.3.177.2" class="indexterm"></a><a id="id-1.9.3.177.3" class="indexterm"></a><a id="id-1.9.3.177.4" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">SET TRANSACTION</span></h2><p>SET TRANSACTION — set the characteristics of the current transaction</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
SET TRANSACTION <em class="replaceable"><code>transaction_mode</code></em> [, ...]
SET TRANSACTION SNAPSHOT <em class="replaceable"><code>snapshot_id</code></em>
SET SESSION CHARACTERISTICS AS TRANSACTION <em class="replaceable"><code>transaction_mode</code></em> [, ...]
<span class="phrase">where <em class="replaceable"><code>transaction_mode</code></em> is one of:</span>
ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
READ WRITE | READ ONLY
[ NOT ] DEFERRABLE
</pre></div><div class="refsect1" id="id-1.9.3.177.8"><h2>Description</h2><p>
The <code class="command">SET TRANSACTION</code> command sets the
characteristics of the current transaction. It has no effect on any
subsequent transactions. <code class="command">SET SESSION
CHARACTERISTICS</code> sets the default transaction
characteristics for subsequent transactions of a session. These
defaults can be overridden by <code class="command">SET TRANSACTION</code>
for an individual transaction.
</p><p>
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.
</p><p>
The isolation level of a transaction determines what data the
transaction can see when other transactions are running concurrently:
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">READ COMMITTED</code></span></dt><dd><p>
A statement can only see rows committed before it began. This
is the default.
</p></dd><dt><span class="term"><code class="literal">REPEATABLE READ</code></span></dt><dd><p>
All statements of the current transaction can only see rows committed
before the first query or data-modification statement was executed in
this transaction.
</p></dd><dt><span class="term"><code class="literal">SERIALIZABLE</code></span></dt><dd><p>
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
<code class="literal">serialization_failure</code> error.
</p></dd></dl></div><p>
The SQL standard defines one additional level, <code class="literal">READ
UNCOMMITTED</code>.
In <span class="productname">PostgreSQL</span> <code class="literal">READ
UNCOMMITTED</code> is treated as <code class="literal">READ COMMITTED</code>.
</p><p>
The transaction isolation level cannot be changed after the first query or
data-modification statement (<code class="command">SELECT</code>,
<code class="command">INSERT</code>, <code class="command">DELETE</code>,
<code class="command">UPDATE</code>, <code class="command">FETCH</code>, or
<code class="command">COPY</code>) of a transaction has been executed. See
<a class="xref" href="mvcc.html" title="Chapter 13. Concurrency Control">Chapter 13</a> for more information about transaction
isolation and concurrency control.
</p><p>
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: <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>,
<code class="literal">DELETE</code>, and <code class="literal">COPY FROM</code> if the
table they would write to is not a temporary table; all
<code class="literal">CREATE</code>, <code class="literal">ALTER</code>, and
<code class="literal">DROP</code> commands; <code class="literal">COMMENT</code>,
<code class="literal">GRANT</code>, <code class="literal">REVOKE</code>,
<code class="literal">TRUNCATE</code>; and <code class="literal">EXPLAIN ANALYZE</code>
and <code class="literal">EXECUTE</code> 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.
</p><p>
The <code class="literal">DEFERRABLE</code> transaction property has no effect
unless the transaction is also <code class="literal">SERIALIZABLE</code> and
<code class="literal">READ ONLY</code>. 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
<code class="literal">SERIALIZABLE</code> 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.
</p><p>
The <code class="literal">SET TRANSACTION SNAPSHOT</code> command allows a new
transaction to run with the same <em class="firstterm">snapshot</em> as an existing
transaction. The pre-existing transaction must have exported its snapshot
with the <code class="literal">pg_export_snapshot</code> function (see <a class="xref" href="functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION" title="9.27.5. Snapshot Synchronization Functions">Section 9.27.5</a>). That function returns a
snapshot identifier, which must be given to <code class="literal">SET TRANSACTION
SNAPSHOT</code> to specify which snapshot is to be imported. The
identifier must be written as a string literal in this command, for example
<code class="literal">'000003A1-1'</code>.
<code class="literal">SET TRANSACTION SNAPSHOT</code> can only be executed at the
start of a transaction, before the first query or
data-modification statement (<code class="command">SELECT</code>,
<code class="command">INSERT</code>, <code class="command">DELETE</code>,
<code class="command">UPDATE</code>, <code class="command">FETCH</code>, or
<code class="command">COPY</code>) of the transaction. Furthermore, the transaction
must already be set to <code class="literal">SERIALIZABLE</code> or
<code class="literal">REPEATABLE READ</code> isolation level (otherwise, the snapshot
would be discarded immediately, since <code class="literal">READ COMMITTED</code> mode takes
a new snapshot for each command). If the importing transaction uses
<code class="literal">SERIALIZABLE</code> 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.
</p></div><div class="refsect1" id="id-1.9.3.177.9"><h2>Notes</h2><p>
If <code class="command">SET TRANSACTION</code> is executed without a prior
<code class="command">START TRANSACTION</code> or <code class="command">BEGIN</code>,
it emits a warning and otherwise has no effect.
</p><p>
It is possible to dispense with <code class="command">SET TRANSACTION</code>
by instead specifying the desired <em class="replaceable"><code>transaction_modes</code></em> in
<code class="command">BEGIN</code> or <code class="command">START TRANSACTION</code>.
But that option is not available for <code class="command">SET TRANSACTION
SNAPSHOT</code>.
</p><p>
The session default transaction modes can also be set or examined via the
configuration parameters <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TRANSACTION-ISOLATION">default_transaction_isolation</a>,
<a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TRANSACTION-READ-ONLY">default_transaction_read_only</a>, and
<a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TRANSACTION-DEFERRABLE">default_transaction_deferrable</a>.
(In fact <code class="command">SET SESSION CHARACTERISTICS</code> is just a
verbose equivalent for setting these variables with <code class="command">SET</code>.)
This means the defaults can be set in the configuration file, via
<code class="command">ALTER DATABASE</code>, etc. Consult <a class="xref" href="runtime-config.html" title="Chapter 20. Server Configuration">Chapter 20</a>
for more information.
</p><p>
The current transaction's modes can similarly be set or examined via the
configuration parameters <a class="xref" href="runtime-config-client.html#GUC-TRANSACTION-ISOLATION">transaction_isolation</a>,
<a class="xref" href="runtime-config-client.html#GUC-TRANSACTION-READ-ONLY">transaction_read_only</a>, and
<a class="xref" href="runtime-config-client.html#GUC-TRANSACTION-DEFERRABLE">transaction_deferrable</a>. Setting one of these
parameters acts the same as the corresponding <code class="command">SET
TRANSACTION</code> 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.
</p></div><div class="refsect1" id="id-1.9.3.177.10"><h2>Examples</h2><p>
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:
</p><pre class="programlisting">
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();
pg_export_snapshot
---------------------
00000003-0000001B-1
(1 row)
</pre><p>
Then give the snapshot identifier in a <code class="command">SET TRANSACTION
SNAPSHOT</code> command at the beginning of the newly opened
transaction:
</p><pre class="programlisting">
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '00000003-0000001B-1';
</pre></div><div class="refsect1" id="R1-SQL-SET-TRANSACTION-3"><h2>Compatibility</h2><p>
These commands are defined in the <acronym class="acronym">SQL</acronym> standard,
except for the <code class="literal">DEFERRABLE</code> transaction mode
and the <code class="command">SET TRANSACTION SNAPSHOT</code> form, which are
<span class="productname">PostgreSQL</span> extensions.
</p><p>
<code class="literal">SERIALIZABLE</code> is the default transaction
isolation level in the standard. In
<span class="productname">PostgreSQL</span> the default is ordinarily
<code class="literal">READ COMMITTED</code>, but you can change it as
mentioned above.
</p><p>
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 <span class="productname">PostgreSQL</span> server.
</p><p>
The SQL standard requires commas between successive <em class="replaceable"><code>transaction_modes</code></em>, but for historical
reasons <span class="productname">PostgreSQL</span> allows the commas to be
omitted.
</p></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-set-session-authorization.html" title="SET SESSION AUTHORIZATION">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-show.html" title="SHOW">Next</a></td></tr><tr><td width="40%" align="left" valign="top">SET SESSION AUTHORIZATION </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> SHOW</td></tr></table></div></body></html>
|
