summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/sql-set-transaction.html
blob: 41dac01b0ff700c28c6319dadc06f962db566399 (plain)
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
177
178
179
<?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 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 15.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 /></div><div class="refentry" id="SQL-SET-TRANSACTION"><div class="titlepage"></div><a id="id-1.9.3.178.1" class="indexterm"></a><a id="id-1.9.3.178.2" class="indexterm"></a><a id="id-1.9.3.178.3" class="indexterm"></a><a id="id-1.9.3.178.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.178.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">MERGE</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="command">INSERT</code>, <code class="command">UPDATE</code>,
   <code class="command">DELETE</code>, <code class="command">MERGE</code>, and
   <code class="command">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">'00000003-0000001B-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">MERGE</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.178.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.178.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 class="navfooter"><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 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> SHOW</td></tr></table></div></body></html>