1SET TRANSACTION(7)               SQL Commands               SET TRANSACTION(7)
2
3
4

NAME

6       SET TRANSACTION - set the characteristics of the current transaction
7
8

SYNOPSIS

10       SET TRANSACTION transaction_mode [, ...]
11       SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]
12
13       where transaction_mode is one of:
14
15           ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
16           READ WRITE | READ ONLY
17
18

DESCRIPTION

20       The  SET  TRANSACTION  command  sets the characteristics of the current
21       transaction. It has no effect on any subsequent transactions. SET  SES‐
22       SION  CHARACTERISTICS  sets the default transaction characteristics for
23       subsequent transactions of a session. These defaults can be  overridden
24       by SET TRANSACTION for an individual transaction.
25
26       The available transaction characteristics are the transaction isolation
27       level and the transaction access mode (read/write or read-only).
28
29       The isolation level of a transaction determines what data the  transac‐
30       tion can see when other transactions are running concurrently:
31
32       READ COMMITTED
33              A statement can only see rows committed before it began. This is
34              the default.
35
36       SERIALIZABLE
37              All statements of the current transaction can only see rows com‐
38              mitted before the first query or data-modification statement was
39              executed in this transaction.
40
41       The SQL standard defines two additional levels,  READ  UNCOMMITTED  and
42       REPEATABLE  READ.   In  PostgreSQL  READ UNCOMMITTED is treated as READ
43       COMMITTED, while REPEATABLE READ is treated as SERIALIZABLE.
44
45       The transaction isolation level cannot be changed after the first query
46       or  data-modification statement (SELECT, INSERT, DELETE, UPDATE, FETCH,
47       or COPY) of a transaction has been executed. See in  the  documentation
48       for  more  information about transaction isolation and concurrency con‐
49       trol.
50
51       The transaction access  mode  determines  whether  the  transaction  is
52       read/write  or read-only. Read/write is the default. When a transaction
53       is read-only,  the  following  SQL  commands  are  disallowed:  INSERT,
54       UPDATE, DELETE, and COPY FROM if the table they would write to is not a
55       temporary table; all CREATE, ALTER, and DROP commands; COMMENT,  GRANT,
56       REVOKE,  TRUNCATE;  and EXPLAIN ANALYZE and EXECUTE if the command they
57       would execute is among those listed. This is  a  high-level  notion  of
58       read-only that does not prevent all writes to disk.
59

NOTES

61       If  SET  TRANSACTION  is  executed without a prior START TRANSACTION or
62       BEGIN, it will appear to have no effect,  since  the  transaction  will
63       immediately end.
64
65       It  is  possible to dispense with SET TRANSACTION by instead specifying
66       the desired transaction_modes in BEGIN or START TRANSACTION.
67
68       The session default transaction modes can also be set  by  setting  the
69       configuration      parameters     default_transaction_isolation     and
70       default_transaction_read_only.  (In fact SET SESSION CHARACTERISTICS is
71       just  a verbose equivalent for setting these variables with SET.)  This
72       means the defaults can be set in  the  configuration  file,  via  ALTER
73       DATABASE, etc. Consult in the documentation for more information.
74

COMPATIBILITY

76       Both  commands  are  defined  in the SQL standard.  SERIALIZABLE is the
77       default transaction isolation level in the standard. In PostgreSQL  the
78       default  is  ordinarily  READ  COMMITTED, but you can change it as men‐
79       tioned above. Because of lack of predicate  locking,  the  SERIALIZABLE
80       level is not truly serializable. See in the documentation for details.
81
82       In the SQL standard, there is one other transaction characteristic that
83       can be set with these commands: the size of the diagnostics area.  This
84       concept  is  specific to embedded SQL, and therefore is not implemented
85       in the PostgreSQL server.
86
87       The SQL standard requires commas between successive  transaction_modes,
88       but for historical reasons PostgreSQL allows the commas to be omitted.
89
90
91
92SQL - Language Statements         2011-09-22                SET TRANSACTION(7)
Impressum