<?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><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 11.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.26.5. Snapshot Synchronization Functions">Section 9.26.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 by setting 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 19. Server Configuration">Chapter 19</a>
for more information.
</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 class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-set-session-authorization.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-show.html">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">Home</a></td><td width="40%" align="right" valign="top"> SHOW</td></tr></table></div></body></html>
