doc/src/sgml/ref/set_transaction.sgml

   1 <!--
   2 doc/src/sgml/ref/set_transaction.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="sql-set-transaction">
   7  <indexterm zone="sql-set-transaction">
   8   <primary>SET TRANSACTION</primary>
   9  </indexterm>
  10
  11  <indexterm>
  12   <primary>transaction isolation level</primary>
  13   <secondary>setting</secondary>
  14  </indexterm>
  15
  16  <indexterm>
  17   <primary>read-only transaction</primary>
  18   <secondary>setting</secondary>
  19  </indexterm>
  20
  21  <indexterm>
  22   <primary>deferrable transaction</primary>
  23   <secondary>setting</secondary>
  24  </indexterm>
  25
  26  <refmeta>
  27   <refentrytitle>SET TRANSACTION</refentrytitle>
  28   <manvolnum>7</manvolnum>
  29   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  30  </refmeta>
  31
  32  <refnamediv>
  33   <refname>SET TRANSACTION</refname>
  34   <refpurpose>set the characteristics of the current transaction</refpurpose>
  35  </refnamediv>
  36
  37  <refsynopsisdiv>
  38 <synopsis>
  39 SET TRANSACTION <replaceable class="parameter">transaction_mode</replaceable> [, ...]
  40 SET TRANSACTION SNAPSHOT <replaceable class="parameter">snapshot_id</replaceable>
  41 SET SESSION CHARACTERISTICS AS TRANSACTION <replaceable class="parameter">transaction_mode</replaceable> [, ...]
  42
  43 <phrase>where <replaceable class="parameter">transaction_mode</replaceable> is one of:</phrase>
  44
  45     ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
  46     READ WRITE | READ ONLY
  47     [ NOT ] DEFERRABLE
  48 </synopsis>
  49  </refsynopsisdiv>
  50
  51  <refsect1>
  52   <title>Description</title>
  53
  54   <para>
  55    The <command>SET TRANSACTION</command> command sets the
  56    characteristics of the current transaction. It has no effect on any
  57    subsequent transactions.  <command>SET SESSION
  58    CHARACTERISTICS</command> sets the default transaction
  59    characteristics for subsequent transactions of a session.  These
  60    defaults can be overridden by <command>SET TRANSACTION</command>
  61    for an individual transaction.
  62   </para>
  63
  64   <para>
  65    The available transaction characteristics are the transaction
  66    isolation level, the transaction access mode (read/write or
  67    read-only), and the deferrable mode.
  68    In addition, a snapshot can be selected, though only for the current
  69    transaction, not as a session default.
  70   </para>
  71
  72   <para>
  73    The isolation level of a transaction determines what data the
  74    transaction can see when other transactions are running concurrently:
  75
  76    <variablelist>
  77     <varlistentry>
  78      <term><literal>READ COMMITTED</literal></term>
  79      <listitem>
  80       <para>
  81        A statement can only see rows committed before it began. This
  82        is the default.
  83       </para>
  84      </listitem>
  85     </varlistentry>
  86
  87     <varlistentry>
  88      <term><literal>REPEATABLE READ</literal></term>
  89      <listitem>
  90       <para>
  91        All statements of the current transaction can only see rows committed
  92        before the first query or data-modification statement was executed in
  93        this transaction.
  94       </para>
  95      </listitem>
  96     </varlistentry>
  97
  98     <varlistentry>
  99      <term><literal>SERIALIZABLE</literal></term>
 100      <listitem>
 101       <para>
 102        All statements of the current transaction can only see rows committed
 103        before the first query or data-modification statement was executed in
 104        this transaction.  If a pattern of reads and writes among concurrent
 105        serializable transactions would create a situation which could not
 106        have occurred for any serial (one-at-a-time) execution of those
 107        transactions, one of them will be rolled back with a
 108        <literal>serialization_failure</literal> error.
 109       </para>
 110      </listitem>
 111     </varlistentry>
 112    </variablelist>
 113
 114    The SQL standard defines one additional level, <literal>READ
 115    UNCOMMITTED</literal>.
 116    In <productname>PostgreSQL</productname> <literal>READ
 117    UNCOMMITTED</literal> is treated as <literal>READ COMMITTED</literal>.
 118   </para>
 119
 120   <para>
 121    The transaction isolation level cannot be changed after the first query or
 122    data-modification statement (<command>SELECT</command>,
 123    <command>INSERT</command>, <command>DELETE</command>,
 124    <command>UPDATE</command>, <command>MERGE</command>,
 125    <command>FETCH</command>, or
 126    <command>COPY</command>) of a transaction has been executed.  See
 127    <xref linkend="mvcc"/> for more information about transaction
 128    isolation and concurrency control.
 129   </para>
 130
 131   <para>
 132    The transaction access mode determines whether the transaction is
 133    read/write or read-only.  Read/write is the default.  When a
 134    transaction is read-only, the following SQL commands are
 135    disallowed: <command>INSERT</command>, <command>UPDATE</command>,
 136    <command>DELETE</command>, <command>MERGE</command>, and
 137    <command>COPY FROM</command> if the
 138    table they would write to is not a temporary table; all
 139    <literal>CREATE</literal>, <literal>ALTER</literal>, and
 140    <literal>DROP</literal> commands; <literal>COMMENT</literal>,
 141    <literal>GRANT</literal>, <literal>REVOKE</literal>,
 142    <literal>TRUNCATE</literal>; and <literal>EXPLAIN ANALYZE</literal>
 143    and <literal>EXECUTE</literal> if the command they would execute is
 144    among those listed.  This is a high-level notion of read-only that
 145    does not prevent all writes to disk.
 146   </para>
 147
 148   <para>
 149    The <literal>DEFERRABLE</literal> transaction property has no effect
 150    unless the transaction is also <literal>SERIALIZABLE</literal> and
 151    <literal>READ ONLY</literal>.  When all three of these properties are
 152    selected for a
 153    transaction, the transaction may block when first acquiring its snapshot,
 154    after which it is able to run without the normal overhead of a
 155    <literal>SERIALIZABLE</literal> transaction and without any risk of
 156    contributing to or being canceled by a serialization failure.  This mode
 157    is well suited for long-running reports or backups.
 158   </para>
 159
 160   <para>
 161    The <literal>SET TRANSACTION SNAPSHOT</literal> command allows a new
 162    transaction to run with the same <firstterm>snapshot</firstterm> as an existing
 163    transaction.  The pre-existing transaction must have exported its snapshot
 164    with the <literal>pg_export_snapshot</literal> function (see <xref
 165    linkend="functions-snapshot-synchronization"/>).  That function returns a
 166    snapshot identifier, which must be given to <literal>SET TRANSACTION
 167    SNAPSHOT</literal> to specify which snapshot is to be imported.  The
 168    identifier must be written as a string literal in this command, for example
 169    <literal>'00000003-0000001B-1'</literal>.
 170    <literal>SET TRANSACTION SNAPSHOT</literal> can only be executed at the
 171    start of a transaction, before the first query or
 172    data-modification statement (<command>SELECT</command>,
 173    <command>INSERT</command>, <command>DELETE</command>,
 174    <command>UPDATE</command>, <command>MERGE</command>,
 175    <command>FETCH</command>, or
 176    <command>COPY</command>) of the transaction.  Furthermore, the transaction
 177    must already be set to <literal>SERIALIZABLE</literal> or
 178    <literal>REPEATABLE READ</literal> isolation level (otherwise, the snapshot
 179    would be discarded immediately, since <literal>READ COMMITTED</literal> mode takes
 180    a new snapshot for each command).  If the importing transaction uses
 181    <literal>SERIALIZABLE</literal> isolation level, then the transaction that
 182    exported the snapshot must also use that isolation level.  Also, a
 183    non-read-only serializable transaction cannot import a snapshot from a
 184    read-only transaction.
 185   </para>
 186
 187  </refsect1>
 188
 189  <refsect1>
 190   <title>Notes</title>
 191
 192   <para>
 193    If <command>SET TRANSACTION</command> is executed without a prior
 194    <command>START TRANSACTION</command> or <command>BEGIN</command>,
 195    it emits a warning and otherwise has no effect.
 196   </para>
 197
 198   <para>
 199    It is possible to dispense with <command>SET TRANSACTION</command>
 200    by instead specifying the desired <replaceable
 201    class="parameter">transaction_modes</replaceable> in
 202    <command>BEGIN</command> or <command>START TRANSACTION</command>.
 203    But that option is not available for <command>SET TRANSACTION
 204    SNAPSHOT</command>.
 205   </para>
 206
 207   <para>
 208    The session default transaction modes can also be set or examined via the
 209    configuration parameters <xref linkend="guc-default-transaction-isolation"/>,
 210    <xref linkend="guc-default-transaction-read-only"/>, and
 211    <xref linkend="guc-default-transaction-deferrable"/>.
 212    (In fact <command>SET SESSION CHARACTERISTICS</command> is just a
 213    verbose equivalent for setting these variables with <command>SET</command>.)
 214    This means the defaults can be set in the configuration file, via
 215    <command>ALTER DATABASE</command>, etc.  Consult <xref linkend="runtime-config"/>
 216    for more information.
 217   </para>
 218
 219   <para>
 220    The current transaction's modes can similarly be set or examined via the
 221    configuration parameters <xref linkend="guc-transaction-isolation"/>,
 222    <xref linkend="guc-transaction-read-only"/>, and
 223    <xref linkend="guc-transaction-deferrable"/>.  Setting one of these
 224    parameters acts the same as the corresponding <command>SET
 225    TRANSACTION</command> option, with the same restrictions on when it can
 226    be done.  However, these parameters cannot be set in the configuration
 227    file, or from any source other than live SQL.
 228   </para>
 229  </refsect1>
 230
 231  <refsect1>
 232   <title>Examples</title>
 233
 234   <para>
 235    To begin a new transaction with the same snapshot as an already
 236    existing transaction, first export the snapshot from the existing
 237    transaction. That will return the snapshot identifier, for example:
 238
 239 <programlisting>
 240 BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 241 SELECT pg_export_snapshot();
 242  pg_export_snapshot
 243 ---------------------
 244  00000003-0000001B-1
 245 (1 row)
 246 </programlisting>
 247
 248    Then give the snapshot identifier in a <command>SET TRANSACTION
 249    SNAPSHOT</command> command at the beginning of the newly opened
 250    transaction:
 251
 252 <programlisting>
 253 BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 254 SET TRANSACTION SNAPSHOT '00000003-0000001B-1';
 255 </programlisting></para>
 256  </refsect1>
 257
 258  <refsect1 id="r1-sql-set-transaction-3">
 259   <title>Compatibility</title>
 260
 261   <para>
 262    These commands are defined in the <acronym>SQL</acronym> standard,
 263    except for the <literal>DEFERRABLE</literal> transaction mode
 264    and the <command>SET TRANSACTION SNAPSHOT</command> form, which are
 265    <productname>PostgreSQL</productname> extensions.
 266   </para>
 267
 268   <para>
 269    <literal>SERIALIZABLE</literal> is the default transaction
 270    isolation level in the standard.  In
 271    <productname>PostgreSQL</productname> the default is ordinarily
 272    <literal>READ COMMITTED</literal>, but you can change it as
 273    mentioned above.
 274   </para>
 275
 276   <para>
 277    In the SQL standard, there is one other transaction characteristic
 278    that can be set with these commands: the size of the diagnostics
 279    area.  This concept is specific to embedded SQL, and therefore is
 280    not implemented in the <productname>PostgreSQL</productname> server.
 281   </para>
 282
 283   <para>
 284    The SQL standard requires commas between successive <replaceable
 285    class="parameter">transaction_modes</replaceable>, but for historical
 286    reasons <productname>PostgreSQL</productname> allows the commas to be
 287    omitted.
 288   </para>
 289  </refsect1>
 290 </refentry>