|PL/SQL User's Guide and Reference
Release 2 (9.2)
Part Number A96624-01
PL/SQL Language Elements, 47 of 52
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".
This clause establishes the current transaction as read-only. If a transaction is set to
ONLY, subsequent queries see only changes committed before the transaction began. The use of
ONLY does not affect other users or transactions.
This clause establishes the current transaction as read-write. The use of
WRITE does not affect other users or transactions. If the transaction executes a data manipulation statement, Oracle assigns the transaction to a rollback segment.
This clause specifies how transactions that modify the database are handled. When you specify
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.
SERIALIZABLE mode, your DBA must set the Oracle initialization parameter
COMPATIBLE to 7.3.0 or higher.
When you specify
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.
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
ONLY parameter in the same transaction because read-only transactions do not generate rollback information.
Lets you specify a name or some 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.
TRANSACTION statement must be the first SQL statement in your transaction and can appear only once in the transaction.
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