set_transaction.sgml

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/ref/set_transaction.sgml,v 1.18 2003/11/06 22:08:14 petere Exp $ -->
<refentry id="SQL-SET-TRANSACTION">
 <refmeta>
  <refentrytitle id="SQL-SET-TRANSACTION-TITLE">SET TRANSACTION</refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>SET TRANSACTION</refname>
  <refpurpose>set the characteristics of the current transaction</refpurpose>
 </refnamediv>

 <indexterm zone="sql-set-transaction">
  <primary>SET TRANSACTION</primary>
 </indexterm>

 <refsynopsisdiv>
<synopsis>
SET TRANSACTION
    [ ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } ]
    [ READ WRITE | READ ONLY ]

SET SESSION CHARACTERISTICS AS TRANSACTION
    [ ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } ]
    [ READ WRITE | READ ONLY ]
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   The <command>SET TRANSACTION</command> command sets the transaction
   characteristics of the current transaction. It has no effect on any
   subsequent transactions.  <command>SET SESSION
   CHARACTERISTICS</command> sets the default transaction
   characteristics for each transaction of a session.  <command>SET
   TRANSACTION</command> can override it for an individual
   transaction.
  </para>

  <para>
   The available transaction characteristics are the transaction
   isolation level and the transaction access mode (read/write or
   read-only).
  </para>

  <para>
   The isolation level of a transaction determines what data the
   transaction can see when other transactions are running concurrently.

   <variablelist>
    <varlistentry>
     <term><literal>READ COMMITTED</literal></term>
     <listitem>
      <para>
       A statement can only see rows committed before it began. This
       is the default.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>SERIALIZABLE</literal></term>
     <listitem>
      <para>
       The current transaction can only see rows committed before
       first query or data-modification statement was executed in this transaction.
      </para>
      <tip>
       <para>
        Intuitively, serializable means that two concurrent
        transactions will leave the database in the same state as if
        the two has been executed strictly after one another in either
        order.
       </para>
      </tip>
     </listitem>
    </varlistentry>
   </variablelist>

   The level <literal>READ UNCOMMITTED</literal> is mapped to
   <literal>READ COMMITTED</literal>, the level <literal>REPEATABLE
   READ</literal> is mapped to <literal>SERIALIZABLE</literal>, The
   transaction isolation level cannot be set after the first query or
   data-modification statement (<command>SELECT</command>,
   <command>INSERT</command>, <command>DELETE</command>,
   <command>UPDATE</command>, <command>FETCH</command>,
   <command>COPY</command>) of a transaction has been executed.  See
   <xref linkend="mvcc"> for more information about transaction
   isolation and concurrency control.
  </para>

  <para>
   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: <literal>INSERT</literal>, <literal>UPDATE</literal>,
   <literal>DELETE</literal>, and <literal>COPY TO</literal> if the
   table they would write to is not a temporary table; all
   <literal>CREATE</literal>, <literal>ALTER</literal>, and
   <literal>DROP</literal> commands; <literal>COMMENT</literal>,
   <literal>GRANT</literal>, <literal>REVOKE</literal>,
   <literal>TRUNCATE</literal>; and <literal>EXPLAIN ANALYZE</literal>
   and <literal>EXECUTE</literal> if the command they would execute is
   among those listed.  This is a high-level notion of read-only that
   does not prevent writes to disk.
  </para>
 </refsect1>

 <refsect1>
  <title>Notes</title>

  <para>
   The session default transaction isolation level can also be set
   with the command
<programlisting>
SET default_transaction_isolation = '<replaceable>value</replaceable>'
</programlisting>
   and in the configuration file.  Consult <xref linkend="runtime-config"> for more
   information.
  </para>
 </refsect1>

 <refsect1 id="R1-SQL-SET-TRANSACTION-3">
  <title>Compatibility</title>

  <para>
   Both commands are defined in the <acronym>SQL</acronym> standard.
   <literal>SERIALIZABLE</literal> is the default transaction
   isolation level in the standard; in
   <productname>PostgreSQL</productname> the default is ordinarily
   <literal>READ COMMITTED</literal>, but you can change it as
   described above.  Because of multiversion concurrency control, the
   <literal>SERIALIZABLE</literal> level is not truly
   serializable. See <xref linkend="mvcc"> for details.
  </para>

  <para>
   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 only for use in embedded SQL.
  </para>
 </refsect1>
</refentry>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->