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 SESSION CHARACTERISTICS AS TRANSACTION <replaceable class="parameter">transaction_mode</replaceable> [, ...]
38
<phrase>where <replaceable class="parameter">transaction_mode</replaceable> is one of:</phrase>
40
ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
41
READ WRITE | READ ONLY
47
<title>Description</title>
50
The <command>SET TRANSACTION</command> command sets the
51
characteristics of the current transaction. It has no effect on any
52
subsequent transactions. <command>SET SESSION
53
CHARACTERISTICS</command> sets the default transaction
54
characteristics for subsequent transactions of a session. These
55
defaults can be overridden by <command>SET TRANSACTION</command>
56
for an individual transaction.
60
The available transaction characteristics are the transaction
61
isolation level, the transaction access mode (read/write or
62
read-only), and the deferrable mode.
66
The isolation level of a transaction determines what data the
67
transaction can see when other transactions are running concurrently:
71
<term><literal>READ COMMITTED</literal></term>
74
A statement can only see rows committed before it began. This
81
<term><literal>REPEATABLE READ</literal></term>
84
All statements of the current transaction can only see rows committed
85
before the first query or data-modification statement was executed in
92
<term><literal>SERIALIZABLE</literal></term>
95
All statements of the current transaction can only see rows committed
96
before the first query or data-modification statement was executed in
97
this transaction. If a pattern of reads and writes among concurrent
98
serializable transactions would create a situation which could not
99
have occurred for any serial (one-at-a-time) execution of those
100
transactions, one of them will be rolled back with a
101
<literal>serialization_failure</literal> <literal>SQLSTATE</literal>.
107
The SQL standard defines one additional level, <literal>READ
108
UNCOMMITTED</literal>.
109
In <productname>PostgreSQL</productname> <literal>READ
110
UNCOMMITTED</literal> is treated as <literal>READ COMMITTED</literal>.
114
The transaction isolation level cannot be changed after the first query or
115
data-modification statement (<command>SELECT</command>,
116
<command>INSERT</command>, <command>DELETE</command>,
117
<command>UPDATE</command>, <command>FETCH</command>, or
118
<command>COPY</command>) of a transaction has been executed. See
119
<xref linkend="mvcc"> for more information about transaction
120
isolation and concurrency control.
124
The transaction access mode determines whether the transaction is
125
read/write or read-only. Read/write is the default. When a
126
transaction is read-only, the following SQL commands are
127
disallowed: <literal>INSERT</literal>, <literal>UPDATE</literal>,
128
<literal>DELETE</literal>, and <literal>COPY FROM</literal> if the
129
table they would write to is not a temporary table; all
130
<literal>CREATE</literal>, <literal>ALTER</literal>, and
131
<literal>DROP</literal> commands; <literal>COMMENT</literal>,
132
<literal>GRANT</literal>, <literal>REVOKE</literal>,
133
<literal>TRUNCATE</literal>; and <literal>EXPLAIN ANALYZE</literal>
134
and <literal>EXECUTE</literal> if the command they would execute is
135
among those listed. This is a high-level notion of read-only that
136
does not prevent all writes to disk.
140
The <literal>DEFERRABLE</literal> transaction property has no effect
141
unless the transaction is also <literal>SERIALIZABLE</literal> and
142
<literal>READ ONLY</literal>. When all of these properties are set on a
143
transaction, the transaction may block when first acquiring its snapshot,
144
after which it is able to run without the normal overhead of a
145
<literal>SERIALIZABLE</literal> transaction and without any risk of
146
contributing to or being cancelled by a serialization failure. This mode
147
is well suited for long-running reports or backups.
155
If <command>SET TRANSACTION</command> is executed without a prior
156
<command>START TRANSACTION</command> or <command>BEGIN</command>,
157
it will appear to have no effect, since the transaction will immediately
162
It is possible to dispense with <command>SET TRANSACTION</command>
163
by instead specifying the desired <replaceable
164
class="parameter">transaction_modes</replaceable> in
165
<command>BEGIN</command> or <command>START TRANSACTION</command>.
169
The session default transaction modes can also be set by setting the
170
configuration parameters <xref linkend="guc-default-transaction-isolation">,
171
<xref linkend="guc-default-transaction-read-only">, and
172
<xref linkend="guc-default-transaction-deferrable">.
173
(In fact <command>SET SESSION CHARACTERISTICS</command> is just a
174
verbose equivalent for setting these variables with <command>SET</>.)
175
This means the defaults can be set in the configuration file, via
176
<command>ALTER DATABASE</>, etc. Consult <xref linkend="runtime-config">
177
for more information.
181
<refsect1 id="R1-SQL-SET-TRANSACTION-3">
182
<title>Compatibility</title>
185
Both commands are defined in the <acronym>SQL</acronym> standard.
186
<literal>SERIALIZABLE</literal> is the default transaction
187
isolation level in the standard. In
188
<productname>PostgreSQL</productname> the default is ordinarily
189
<literal>READ COMMITTED</literal>, but you can change it as
194
In the SQL standard, there is one other transaction characteristic
195
that can be set with these commands: the size of the diagnostics
196
area. This concept is specific to embedded SQL, and therefore is
197
not implemented in the <productname>PostgreSQL</productname> server.
201
The <literal>DEFERRABLE</literal>
202
<replaceable class="parameter">transaction_mode</replaceable>
203
is a <productname>PostgreSQL</productname> language extension.
207
The SQL standard requires commas between successive <replaceable
208
class="parameter">transaction_modes</replaceable>, but for historical
209
reasons <productname>PostgreSQL</productname> allows the commas to be