<!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.0.11 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="2007-02-02T03:57:22"></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.0.11 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="AEN45519" ></A ><H2 >Name</H2 >SET TRANSACTION -- set the characteristics of the current transaction</DIV ><A NAME="AEN45522" ></A ><DIV CLASS="REFSYNOPSISDIV" ><A NAME="AEN45524" ></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="AEN45529" ></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 12</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="AEN45577" ></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.html#GUC-DEFAULT-TRANSACTION-ISOLATION" >default_transaction_isolation</A > and <A HREF="runtime-config.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" >Section 16.4</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 12</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 >