PL/SQL User's Guide and Reference

SET TRANSACTION Statement

Description

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 "Using SET TRANSACTION"

Syntax

set_transaction_statement ::=

SET TRANSACTION 
   {  READ ONLY
    | READ WRITE
    | ISOLATION LEVEL {SERIALIZABLE | READ COMMITTED}
    | USE ROLLBACK SEGMENT rollback_segment_name};

Keyword and Parameter Description

READ ONLY

This clause establishes the current transaction as read-only. If a transaction is set to READ ONLY, subsequent queries see only changes committed before the transaction began. The use of READ ONLY does not affect other users or transactions.

READ WRITE

This clause 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

This clause specifies how transactions that modify the database are handled. When you specify SERIALIZABLE, if a serializable transaction trys 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.

When you specify 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

This clause 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.

Usage Notes

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

Only the SELECT INTO, OPEN, FETCH, CLOSE, LOCK TABLE, COMMIT, and ROLLBACK statements are allowed in a read-only transaction. For example, including an INSERT statement raises an exception. Also, queries cannot be FOR UPDATE.

Example

In the following example, you establish a read-only transaction:

COMMIT;  -- end previous transaction
SET TRANSACTION READ ONLY;
SELECT ... FROM emp WHERE ...
SELECT ... FROM dept WHERE ...
SELECT ... FROM emp WHERE ...
COMMIT;  -- end read-only transaction