1 <!-- doc/src/sgml/ref/set_transaction.sgml -->
2 <refentry id="SQL-SET-TRANSACTION">
4 <refentrytitle>SET TRANSACTION</refentrytitle>
5 <manvolnum>7</manvolnum>
6 <refmiscinfo>SQL - Language Statements</refmiscinfo>
10 <refname>SET TRANSACTION</refname>
11 <refpurpose>set the characteristics of the current transaction</refpurpose>
14 <indexterm zone="sql-set-transaction">
15 <primary>SET TRANSACTION</primary>
19 <primary>transaction isolation level</primary>
20 <secondary>setting</secondary>
24 <primary>read-only transaction</primary>
25 <secondary>setting</secondary>
29 <primary>deferrable transaction</primary>
30 <secondary>setting</secondary>
35 SET TRANSACTION <replaceable class="parameter">transaction_mode</replaceable> [, ...]
36 SET TRANSACTION SNAPSHOT <replaceable class="parameter">snapshot_id</replaceable>
37 SET SESSION CHARACTERISTICS AS TRANSACTION <replaceable class="parameter">transaction_mode</replaceable> [, ...]
39 <phrase>where <replaceable class="parameter">transaction_mode</replaceable> is one of:</phrase>
41 ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
42 READ WRITE | READ ONLY
48 <title>Description</title>
51 The <command>SET TRANSACTION</command> command sets the
52 characteristics of the current transaction. It has no effect on any
53 subsequent transactions. <command>SET SESSION
54 CHARACTERISTICS</command> sets the default transaction
55 characteristics for subsequent transactions of a session. These
56 defaults can be overridden by <command>SET TRANSACTION</command>
57 for an individual transaction.
61 The available transaction characteristics are the transaction
62 isolation level, the transaction access mode (read/write or
63 read-only), and the deferrable mode.
64 In addition, a snapshot can be selected, though only for the current
65 transaction, not as a session default.
69 The isolation level of a transaction determines what data the
70 transaction can see when other transactions are running concurrently:
74 <term><literal>READ COMMITTED</literal></term>
77 A statement can only see rows committed before it began. This
84 <term><literal>REPEATABLE READ</literal></term>
87 All statements of the current transaction can only see rows committed
88 before the first query or data-modification statement was executed in
95 <term><literal>SERIALIZABLE</literal></term>
98 All statements of the current transaction can only see rows committed
99 before the first query or data-modification statement was executed in
100 this transaction. If a pattern of reads and writes among concurrent
101 serializable transactions would create a situation which could not
102 have occurred for any serial (one-at-a-time) execution of those
103 transactions, one of them will be rolled back with a
104 <literal>serialization_failure</literal> error.
110 The SQL standard defines one additional level, <literal>READ
111 UNCOMMITTED</literal>.
112 In <productname>PostgreSQL</productname> <literal>READ
113 UNCOMMITTED</literal> is treated as <literal>READ COMMITTED</literal>.
117 The transaction isolation level cannot be changed after the first query or
118 data-modification statement (<command>SELECT</command>,
119 <command>INSERT</command>, <command>DELETE</command>,
120 <command>UPDATE</command>, <command>FETCH</command>, or
121 <command>COPY</command>) of a transaction has been executed. See
122 <xref linkend="mvcc"> for more information about transaction
123 isolation and concurrency control.
127 The transaction access mode determines whether the transaction is
128 read/write or read-only. Read/write is the default. When a
129 transaction is read-only, the following SQL commands are
130 disallowed: <literal>INSERT</literal>, <literal>UPDATE</literal>,
131 <literal>DELETE</literal>, and <literal>COPY FROM</literal> if the
132 table they would write to is not a temporary table; all
133 <literal>CREATE</literal>, <literal>ALTER</literal>, and
134 <literal>DROP</literal> commands; <literal>COMMENT</literal>,
135 <literal>GRANT</literal>, <literal>REVOKE</literal>,
136 <literal>TRUNCATE</literal>; and <literal>EXPLAIN ANALYZE</literal>
137 and <literal>EXECUTE</literal> if the command they would execute is
138 among those listed. This is a high-level notion of read-only that
139 does not prevent all writes to disk.
143 The <literal>DEFERRABLE</literal> transaction property has no effect
144 unless the transaction is also <literal>SERIALIZABLE</literal> and
145 <literal>READ ONLY</literal>. When all three of these properties are
147 transaction, the transaction may block when first acquiring its snapshot,
148 after which it is able to run without the normal overhead of a
149 <literal>SERIALIZABLE</literal> transaction and without any risk of
150 contributing to or being canceled by a serialization failure. This mode
151 is well suited for long-running reports or backups.
155 The <literal>SET TRANSACTION SNAPSHOT</literal> command allows a new
156 transaction to run with the same <firstterm>snapshot</> as an existing
157 transaction. The pre-existing transaction must have exported its snapshot
158 with the <literal>pg_export_snapshot</literal> function (see <xref
159 linkend="functions-snapshot-synchronization">). That function returns a
160 snapshot identifier, which must be given to <literal>SET TRANSACTION
161 SNAPSHOT</literal> to specify which snapshot is to be imported. The
162 identifier must be written as a string literal in this command, for example
163 <literal>'000003A1-1'</>.
164 <literal>SET TRANSACTION SNAPSHOT</literal> can only be executed at the
165 start of a transaction, before the first query or
166 data-modification statement (<command>SELECT</command>,
167 <command>INSERT</command>, <command>DELETE</command>,
168 <command>UPDATE</command>, <command>FETCH</command>, or
169 <command>COPY</command>) of the transaction. Furthermore, the transaction
170 must already be set to <literal>SERIALIZABLE</literal> or
171 <literal>REPEATABLE READ</literal> isolation level (otherwise, the snapshot
172 would be discarded immediately, since <literal>READ COMMITTED</> mode takes
173 a new snapshot for each command). If the importing transaction uses
174 <literal>SERIALIZABLE</literal> isolation level, then the transaction that
175 exported the snapshot must also use that isolation level. Also, a
176 non-read-only serializable transaction cannot import a snapshot from a
177 read-only transaction.
186 If <command>SET TRANSACTION</command> is executed without a prior
187 <command>START TRANSACTION</command> or <command>BEGIN</command>,
188 it will appear to have no effect, since the transaction will immediately
193 It is possible to dispense with <command>SET TRANSACTION</command>
194 by instead specifying the desired <replaceable
195 class="parameter">transaction_modes</replaceable> in
196 <command>BEGIN</command> or <command>START TRANSACTION</command>.
197 But that option is not available for <command>SET TRANSACTION
202 The session default transaction modes can also be set by setting the
203 configuration parameters <xref linkend="guc-default-transaction-isolation">,
204 <xref linkend="guc-default-transaction-read-only">, and
205 <xref linkend="guc-default-transaction-deferrable">.
206 (In fact <command>SET SESSION CHARACTERISTICS</command> is just a
207 verbose equivalent for setting these variables with <command>SET</>.)
208 This means the defaults can be set in the configuration file, via
209 <command>ALTER DATABASE</>, etc. Consult <xref linkend="runtime-config">
210 for more information.
215 <title>Examples</title>
218 To begin a new transaction with the same snapshot as an already
219 existing transaction, first export the snapshot from the existing
220 transaction. That will return the snapshot identifier, for example:
223 BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
224 SELECT pg_export_snapshot();
231 Then give the snapshot identifier in a <command>SET TRANSACTION
232 SNAPSHOT</command> command at the beginning of the newly opened
236 BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
237 SET TRANSACTION SNAPSHOT '000003A1-1';
242 <refsect1 id="R1-SQL-SET-TRANSACTION-3">
243 <title>Compatibility</title>
246 These commands are defined in the <acronym>SQL</acronym> standard,
247 except for the <literal>DEFERRABLE</literal> transaction mode
248 and the <command>SET TRANSACTION SNAPSHOT</> form, which are
249 <productname>PostgreSQL</productname> extensions.
253 <literal>SERIALIZABLE</literal> is the default transaction
254 isolation level in the standard. In
255 <productname>PostgreSQL</productname> the default is ordinarily
256 <literal>READ COMMITTED</literal>, but you can change it as
261 In the SQL standard, there is one other transaction characteristic
262 that can be set with these commands: the size of the diagnostics
263 area. This concept is specific to embedded SQL, and therefore is
264 not implemented in the <productname>PostgreSQL</productname> server.
268 The SQL standard requires commas between successive <replaceable
269 class="parameter">transaction_modes</replaceable>, but for historical
270 reasons <productname>PostgreSQL</productname> allows the commas to be