/usr/man/cat.l/set_transaction.l.Z(/usr/man/cat.l/set_transaction.l.Z)

NAME

       SET TRANSACTION - set the characteristics of the current transaction

SYNOPSIS

       SET TRANSACTION
           [ ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE } ] [ READ WRITE | READ ONLY ]
       SET SESSION CHARACTERISTICS AS TRANSACTION
           [ ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE } ] [ READ WRITE | READ ONLY ]

DESCRIPTION

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

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

       The isolation level of a transaction determines what data the  transac-
       tion can see when other transactions are running concurrently.

       READ COMMITTED
              A statement can only see rows committed before it began. This is
              the default.

       SERIALIZABLE
              The current transaction can only see rows committed before first
              query or data-modification statement was executed in this trans-
              action.

              Tip: Intuitively, serializable means that two concurrent  trans-
              actions  will leave the database in the same state as if the two
              has been executed strictly after one another in either order.

       The transaction isolation level cannot be set after the first query  or
       data-modification  statement  (SELECT,  INSERT,  DELETE, UPDATE, FETCH,
       COPY) of a transaction has been executed. See the chapter called ``Con-
       currency  Control''  in  the  documentation  for more information about
       transaction isolation and concurrency control.

       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:  INSERT,
       UPDATE,  DELETE,  and COPY TO if the table they would write to is not a
       temporary table; all CREATE, ALTER, and DROP commands; COMMENT,  GRANT,
       REVOKE,  TRUNCATE;  and EXPLAIN ANALYZE and EXECUTE 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.

NOTES

       The  session  default  transaction isolation level can also be set with
       the command

       SET default_transaction_isolation = 'value'

       and in the configuration file. Consult the  section  called  ``Run-time
       Configuration'' in the documentation for more information.

COMPATIBILITY

       Both  commands  are  defined  in the SQL standard.  SERIALIZABLE is the
       default transaction isolation level in the standard; in PostgreSQL  the
       default  is  ordinarily  READ  COMMITTED,  but  you  can  change  it as
       described above. PostgreSQL does not provide the isolation levels  READ
       UNCOMMITTED  and  REPEATABLE  READ. Because of multiversion concurrency
       control, the SERIALIZABLE level is  not  truly  serializable.  See  the
       chapter   called  ``Concurrency  Control''  in  the  documentation  for
       details.

       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.

SQL - Language Statements         2003-11-02                SET TRANSACTION(l)