Sophie

Sophie

distrib > Mandriva > 2008.1 > x86_64 > media > main-testing > by-pkgid > bab02a23fa9f3df8d66a9a3231b50245 > files > 817

postgresql8.3-docs-8.3.6-2mdv2008.1.x86_64.rpm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>SET TRANSACTION</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.3.6 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="SQL Commands"
HREF="sql-commands.html"><LINK
REL="PREVIOUS"
TITLE="SET SESSION AUTHORIZATION"
HREF="sql-set-session-authorization.html"><LINK
REL="NEXT"
TITLE="SHOW"
HREF="sql-show.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2009-02-03T04:34:16"></HEAD
><BODY
CLASS="REFENTRY"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.3.6 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-set-session-authorization.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-set-session-authorization.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="sql-show.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="sql-show.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
><A
NAME="SQL-SET-TRANSACTION"
></A
>SET TRANSACTION</H1
><DIV
CLASS="REFNAMEDIV"
><A
NAME="AEN60552"
></A
><H2
>Name</H2
>SET TRANSACTION&nbsp;--&nbsp;set the characteristics of the current transaction</DIV
><A
NAME="AEN60555"
></A
><DIV
CLASS="REFSYNOPSISDIV"
><A
NAME="AEN60557"
></A
><H2
>Synopsis</H2
><PRE
CLASS="SYNOPSIS"
>SET TRANSACTION <TT
CLASS="REPLACEABLE"
><I
>transaction_mode</I
></TT
> [, ...]
SET SESSION CHARACTERISTICS AS TRANSACTION <TT
CLASS="REPLACEABLE"
><I
>transaction_mode</I
></TT
> [, ...]

where <TT
CLASS="REPLACEABLE"
><I
>transaction_mode</I
></TT
> is one of:

    ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
    READ WRITE | READ ONLY</PRE
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN60562"
></A
><H2
>Description</H2
><P
>   The <TT
CLASS="COMMAND"
>SET TRANSACTION</TT
> command sets the
   characteristics of the current transaction. It has no effect on any
   subsequent transactions.  <TT
CLASS="COMMAND"
>SET SESSION
   CHARACTERISTICS</TT
> sets the default transaction
   characteristics for subsequent transactions of a session.  These
   defaults can be overridden by <TT
CLASS="COMMAND"
>SET TRANSACTION</TT
>
   for an individual transaction.
  </P
><P
>   The available transaction characteristics are the transaction
   isolation level and the transaction access mode (read/write or
   read-only).
  </P
><P
>   The isolation level of a transaction determines what data the
   transaction can see when other transactions are running concurrently:

   <P
></P
></P><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="LITERAL"
>READ COMMITTED</TT
></DT
><DD
><P
>       A statement can only see rows committed before it began. This
       is the default.
      </P
></DD
><DT
><TT
CLASS="LITERAL"
>SERIALIZABLE</TT
></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
></DL
></DIV
><P>

   The SQL standard defines two additional levels, <TT
CLASS="LITERAL"
>READ
   UNCOMMITTED</TT
> and <TT
CLASS="LITERAL"
>REPEATABLE READ</TT
>.
   In <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> <TT
CLASS="LITERAL"
>READ
   UNCOMMITTED</TT
> is treated as
   <TT
CLASS="LITERAL"
>READ COMMITTED</TT
>, while <TT
CLASS="LITERAL"
>REPEATABLE
   READ</TT
> is treated as <TT
CLASS="LITERAL"
>SERIALIZABLE</TT
>.
  </P
><P
>   The transaction isolation level cannot be changed after the first query or
   data-modification statement (<TT
CLASS="COMMAND"
>SELECT</TT
>,
   <TT
CLASS="COMMAND"
>INSERT</TT
>, <TT
CLASS="COMMAND"
>DELETE</TT
>,
   <TT
CLASS="COMMAND"
>UPDATE</TT
>, <TT
CLASS="COMMAND"
>FETCH</TT
>, or
   <TT
CLASS="COMMAND"
>COPY</TT
>) of a transaction has been executed.  See
   <A
HREF="mvcc.html"
>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: <TT
CLASS="LITERAL"
>INSERT</TT
>, <TT
CLASS="LITERAL"
>UPDATE</TT
>,
   <TT
CLASS="LITERAL"
>DELETE</TT
>, and <TT
CLASS="LITERAL"
>COPY FROM</TT
> if the
   table they would write to is not a temporary table; all
   <TT
CLASS="LITERAL"
>CREATE</TT
>, <TT
CLASS="LITERAL"
>ALTER</TT
>, and
   <TT
CLASS="LITERAL"
>DROP</TT
> commands; <TT
CLASS="LITERAL"
>COMMENT</TT
>,
   <TT
CLASS="LITERAL"
>GRANT</TT
>, <TT
CLASS="LITERAL"
>REVOKE</TT
>,
   <TT
CLASS="LITERAL"
>TRUNCATE</TT
>; and <TT
CLASS="LITERAL"
>EXPLAIN ANALYZE</TT
>
   and <TT
CLASS="LITERAL"
>EXECUTE</TT
> 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
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN60610"
></A
><H2
>Notes</H2
><P
>   If <TT
CLASS="COMMAND"
>SET TRANSACTION</TT
> is executed without a prior
   <TT
CLASS="COMMAND"
>START TRANSACTION</TT
> or  <TT
CLASS="COMMAND"
>BEGIN</TT
>,
   it will appear to have no effect, since the transaction will immediately
   end.
  </P
><P
>   It is possible to dispense with <TT
CLASS="COMMAND"
>SET TRANSACTION</TT
>
   by instead specifying the desired <TT
CLASS="REPLACEABLE"
><I
>transaction_modes</I
></TT
> in
   <TT
CLASS="COMMAND"
>BEGIN</TT
> or <TT
CLASS="COMMAND"
>START TRANSACTION</TT
>.
  </P
><P
>   The session default transaction modes can also be set by setting the
   configuration parameters <A
HREF="runtime-config-client.html#GUC-DEFAULT-TRANSACTION-ISOLATION"
>default_transaction_isolation</A
>
   and <A
HREF="runtime-config-client.html#GUC-DEFAULT-TRANSACTION-READ-ONLY"
>default_transaction_read_only</A
>.
   (In fact <TT
CLASS="COMMAND"
>SET SESSION CHARACTERISTICS</TT
> is just a
   verbose equivalent for setting these variables with <TT
CLASS="COMMAND"
>SET</TT
>.)
   This means the defaults can be set in the configuration file, via
   <TT
CLASS="COMMAND"
>ALTER DATABASE</TT
>, etc.  Consult <A
HREF="runtime-config.html"
>Chapter 18</A
>
   for more information.
  </P
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="R1-SQL-SET-TRANSACTION-3"
></A
><H2
>Compatibility</H2
><P
>   Both commands are defined in the <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> standard.
   <TT
CLASS="LITERAL"
>SERIALIZABLE</TT
> is the default transaction
   isolation level in the standard.  In
   <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> the default is ordinarily
   <TT
CLASS="LITERAL"
>READ COMMITTED</TT
>, but you can change it as
   mentioned above.  Because of lack of predicate locking, the
   <TT
CLASS="LITERAL"
>SERIALIZABLE</TT
> level is not truly
   serializable. See <A
HREF="mvcc.html"
>Chapter 13</A
> for details.
  </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 <TT
CLASS="REPLACEABLE"
><I
>transaction_modes</I
></TT
>, but for historical
   reasons <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> allows the commas to be
   omitted.
  </P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-set-session-authorization.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="sql-show.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>SET SESSION AUTHORIZATION</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="sql-commands.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>SHOW</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>

Perl :: Catalyst :: PostgreSQL :: RPM Valid HTML 4.01 Transitional