| <!-- $PostgreSQL: pgsql/doc/src/sgml/ref/set_transaction.sgml,v 1.25 2006/09/16 00:30:20 momjian 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 <replaceable class="parameter">transaction_mode</replaceable> [, ...] |
| SET SESSION CHARACTERISTICS AS TRANSACTION <replaceable class="parameter">transaction_mode</replaceable> [, ...] |
| |
| where <replaceable class="parameter">transaction_mode</replaceable> is one of: |
| |
| ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } |
| READ WRITE | READ ONLY |
| </synopsis> |
| </refsynopsisdiv> |
| |
| <refsect1> |
| <title>Description</title> |
| |
| <para> |
| The <command>SET TRANSACTION</command> command sets the |
| characteristics of the current transaction. It has no effect on any |
| subsequent transactions. <command>SET SESSION |
| CHARACTERISTICS</command> sets the default transaction |
| characteristics for subsequent transactions of a session. These |
| defaults can be overridden by <command>SET TRANSACTION</command> |
| 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> |
| All statements of the current transaction can only see rows committed |
| before the first query or data-modification statement was executed in |
| this transaction. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| |
| The SQL standard defines two additional levels, <literal>READ |
| UNCOMMITTED</literal> and <literal>REPEATABLE READ</literal>. |
| In <productname>PostgreSQL</productname> <literal>READ |
| UNCOMMITTED</literal> is treated as |
| <literal>READ COMMITTED</literal>, while <literal>REPEATABLE |
| READ</literal> is treated as <literal>SERIALIZABLE</literal>. |
| </para> |
| |
| <para> |
| The transaction isolation level cannot be changed after the first query or |
| data-modification statement (<command>SELECT</command>, |
| <command>INSERT</command>, <command>DELETE</command>, |
| <command>UPDATE</command>, <command>FETCH</command>, or |
| <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 FROM</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 all writes to disk. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Notes</title> |
| |
| <para> |
| If <command>SET TRANSACTION</command> is executed without a prior |
| <command>START TRANSACTION</command> or <command>BEGIN</command>, |
| it will appear to have no effect, since the transaction will immediately |
| end. |
| </para> |
| |
| <para> |
| It is possible to dispense with <command>SET TRANSACTION</command> |
| by instead specifying the desired <replaceable |
| class="parameter">transaction_modes</replaceable> in |
| <command>BEGIN</command> or <command>START TRANSACTION</command>. |
| </para> |
| |
| <para> |
| The session default transaction modes can also be set by setting the |
| configuration parameters <xref linkend="guc-default-transaction-isolation"> |
| and <xref linkend="guc-default-transaction-read-only">. |
| (In fact <command>SET SESSION CHARACTERISTICS</command> is just a |
| verbose equivalent for setting these variables with <command>SET</>.) |
| This means the defaults can be set in the configuration file, via |
| <command>ALTER DATABASE</>, etc. 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 |
| mentioned above. Because of lack of predicate locking, 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 specific to embedded SQL, and therefore is |
| not implemented in the <productname>PostgreSQL</productname> server. |
| </para> |
| |
| <para> |
| The SQL standard requires commas between successive <replaceable |
| class="parameter">transaction_modes</replaceable>, but for historical |
| reasons <productname>PostgreSQL</productname> allows the commas to be |
| omitted. |
| </para> |
| </refsect1> |
| </refentry> |