Skip Headers
Oracle® Database PL/SQL User's Guide and Reference
10g Release 2 (10.2)

Part Number B14261-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

SET TRANSACTION Statement

The SET TRANSACTION statement begins a read-only or read-write transaction, establishes an isolation level, or assigns the current transaction to a specified rollback segment. Read-only transactions are useful for running multiple queries against one or more tables while other users update the same tables. For more information, see "Setting Transaction Properties with SET TRANSACTION".

For additional information on the SET TRANSACTION SQL statement, see Oracle Database SQL Reference.

Syntax

set transaction ::=

Description of set_transaction.gif follows
Description of the illustration set_transaction.gif

Keyword and Parameter Description

READ ONLY

Establishes the current transaction as read-only, so that subsequent queries see only changes committed before the transaction began. The use of READ ONLY does not affect other users or transactions.

READ WRITE

Establishes the current transaction as read-write. The use of READ WRITE does not affect other users or transactions. If the transaction executes a data manipulation statement, Oracle assigns the transaction to a rollback segment.

ISOLATION LEVEL

Specifies how to handle transactions that modify the database.

SERIALIZABLE: If a serializable transaction tries to execute a SQL data manipulation statement that modifies any table already modified by an uncommitted transaction, the statement fails.

To enable SERIALIZABLE mode, your DBA must set the Oracle initialization parameter COMPATIBLE to 7.3.0 or higher.

READ COMMITTED: If a transaction includes SQL data manipulation statements that require row locks held by another transaction, the statement waits until the row locks are released.

USE ROLLBACK SEGMENT

Assigns the current transaction to the specified rollback segment and establishes the transaction as read-write. You cannot use this parameter with the READ ONLY parameter in the same transaction because read-only transactions do not generate rollback information.

NAME

Specifies a name or comment text for the transaction. This is better than using the COMMIT COMMENT feature because the name is available while the transaction is running, making it easier to monitor long-running and in-doubt transactions.

Usage Notes

The SET TRANSACTION statement must be the first SQL statement in the transaction and can appear only once in the transaction.

Examples

For examples, see the following:


Example 6-40, "Using SET TRANSACTION to Begin a Read-only Transaction"

Related Topics


"COMMIT Statement"
"ROLLBACK Statement"
"SAVEPOINT Statement"