~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security

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

.PP

The isolation level of a transaction determines what data the transaction can see when other transactions are running concurrently:

.PP

READ COMMITTED

.RS 4

A statement can only see rows committed before it began\&. This is the default\&.

.RE

.PP

REPEATABLE READ

.RS 4

All statements of the current transaction can only see rows committed before the first query or data\-modification statement was executed in this transaction\&.

.RE

.PP

SERIALIZABLE

.RS 4

All statements of the current transaction can only see rows committed before the first query or data\-modification statement was executed in this transaction\&. If a pattern of reads and writes among concurrent serializable transactions would create a situation which could not have occurred for any serial (one\-at\-a\-time) execution of those transactions, one of them will be rolled back with a

serialization_failureSQLSTATE\&.

.RE

The SQL standard defines one additional level,

READ UNCOMMITTED\&. In

PostgreSQLREAD UNCOMMITTED

is treated as

READ COMMITTED\&.

.PP

The transaction isolation level cannot be changed after the first query or data\-modification statement (SELECT,

INSERT,

DELETE,

UPDATE,

FETCH, or

COPY) of a transaction has been executed\&. See

Chapter 13, Concurrency Control, in the documentation

for more information about transaction isolation and concurrency control\&.

.PP

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 FROM

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

100

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\&.

101

.PP

102

The

103

DEFERRABLE

104

transaction property has no effect unless the transaction is also

105

SERIALIZABLE

106

and

107

READ ONLY\&. When all of these properties are set on a transaction, the transaction may block when first acquiring its snapshot, after which it is able to run without the normal overhead of a

108

SERIALIZABLE

109

transaction and without any risk of contributing to or being cancelled by a serialization failure\&. This mode is well suited for long\-running reports or backups\&.

110

.SH "NOTES"

111

.PP

112

113

SET TRANSACTION

114

is executed without a prior

115

START TRANSACTION

116

117

BEGIN, it will appear to have no effect, since the transaction will immediately end\&.

118

.PP

119

It is possible to dispense with

120

SET TRANSACTION

121

by instead specifying the desired

122

\fItransaction_modes\fR

123

124

BEGIN

125

126

START TRANSACTION\&.

127

.PP

128

The session default transaction modes can also be set by setting the configuration parameters

129

default_transaction_isolation,

130

default_transaction_read_only, and

131

default_transaction_deferrable\&. (In fact

132

SET SESSION CHARACTERISTICS

133

is just a verbose equivalent for setting these variables with

134

SET\&.) This means the defaults can be set in the configuration file, via

135

ALTER DATABASE, etc\&. Consult

136

Chapter 18, Server Configuration, in the documentation

137

for more information\&.

138

.SH "COMPATIBILITY"

139

.PP

140

Both commands are defined in the

141

SQL

142

standard\&.

143

SERIALIZABLE

144

is the default transaction isolation level in the standard\&. In

145

PostgreSQL

146

the default is ordinarily

147

READ COMMITTED, but you can change it as mentioned above\&.

148

.PP

149

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

150

PostgreSQL

151

server\&.

152

.PP

153

The

154

DEFERRABLE\fItransaction_mode\fR

155

is a

156

PostgreSQL

157

language extension\&.

158

.PP

159

The SQL standard requires commas between successive

160

\fItransaction_modes\fR, but for historical reasons

161

PostgreSQL

162

allows the commas to be omitted\&.

Older »