Oracle7 Server SQL Reference

SET TRANSACTION

Purpose

For the current transaction:

establish as a read-only or read-write transaction

establish the isolation level

assign the transaction to a specified rollback segment

Prerequisites

If you use a SET TRANSACTION statement, it must be the first statement in your transaction. However, every transaction need not have a SET TRANSACTION statement.

Syntax

Keywords and Parameters

READ ONLY

establishes the current transaction as a read-only transaction.

READ WRITE

establishes the current transaction as a read-write transaction.

ISOLATION LEVEL

specifies how transactions containing database modifications are handled.

SERIALIZABLE

use the serializable transaction isolation mode as specified in SQL92. That is, if a serializable transaction attempts to execute a DML statement that updates any resource that may have been updated in an uncommitted transaction at the start of the serializable transaction, then the DML statement fails. The COMPATIBLE initialization parameter must be set to 7.3.0 or higher for SERIALIZABLE mode to work.

READ COMMITTED

use the default Oracle transaction behavior. Thus, if the transaction contains DML that require row locks held by another transaction, then the DML statement will wait until the row locks are released.

USE ROLLBACK SEGMENT

assigns the current transaction to the specified rollback segment. This option also establishes the transaction as a read-write transaction.

You cannot use the READ ONLY option and the USE ROLLBACK SEGMENT clause in a single SET TRANSACTION statement or in different statements in the same transaction. Read-only transactions do not generate rollback information and therefore are not assigned rollback segments.

Usage Notes

The operations performed by a SET TRANSACTION statement affect only your current transaction, not other users or other transactions. Your transaction ends whenever you issue a COMMIT or ROLLBACK statement. Note also that Oracle7 implicitly commits the current transaction before and after executing a Data Definition Language statement.

Establishing Read-only Transactions

The default state for all transactions is statement level read consistency. You can explicitly specify this state by issuing a SET TRANSACTION statement with the READ WRITE option.

You can establish transaction level read consistency by issuing a SET TRANSACTION statement with the READ ONLY option. After a transaction has been established as read-only, all subsequent queries in that transaction only see changes committed before the transaction began. Read-only transactions are very useful for reports that run multiple queries against one or more tables while other users update these same tables.

Only the following statements are permitted in a read-only transaction:

SELECT (except statements with the FOR UPDATE clause)

LOCK TABLE

SET ROLE

ALTER SESSION

ALTER SYSTEM

INSERT, UPDATE, and DELETE statements and SELECT statements with the FOR UPDATE clause are not permitted. Any Data Definition Language statement implicitly ends the read-only transaction.

The read consistency that read-only transactions provide is implemented in the same way as statement-level read consistency. Every statement by default uses a consistent view of the data as of the time the statement is issued. Read-only transactions present a consistent view of the data as of the time that the SET TRANSACTION READ ONLY statement is issued. Read-only transactions provide read consistency is for all nodes accessed by distributed queries and local queries.

You cannot toggle between transaction level read consistency and statement level read consistency in the same transaction. A SET TRANSACTION statement can only be issued as the first statement of a transaction.

Example I

The following statements could be run at midnight of the last day of every month to count how many ships and containers the company owns. This report would not be affected by any other user who might be adding or removing ships and/or containers.

COMMIT

SET TRANSACTION READ ONLY

SELECT COUNT(*) FROM ship

SELECT COUNT(*) FROM container

COMMIT

The last COMMIT statement does not actually make permanent any changes to the database. It ends the read-only transaction.

Assigning Transactions to Rollback Segments

If you issue a Data Manipulation Language statement in a transaction, Oracle7 assigns the transaction to a rollback segment. The rollback segment holds the information necessary to undo the changes made by the transaction. You can issue a SET TRANSACTION statement with the USE ROLLBACK SEGMENT clause to choose a specific rollback segment for your transaction. If you do not choose a rollback segment, Oracle7 chooses one randomly and assigns your transaction to it.

SET TRANSACTION allows you to assign transactions of different types to rollback segments of different sizes:

Assign OLTP transactions, or small transactions containing only a few Data Manipulation Language statements that modify only a few rows, to small rollback segments if there are no long-running queries concurrently reading the same tables. Small rollback segments are more likely to remain in memory.

Assign transactions that modify tables that are concurrently being read by long-running queries to large rollback segments so that the rollback information needed for the read consistent queries is not overwritten.

Assign transactions with bulk Data Manipulation Language statements, or statements that insert, update, or delete large amounts of data, to rollback segments large enough to hold the rollback information for the transaction.

Example II

The following statement assigns your current transaction to the rollback segment OLTP_5:

SET TRANSACTION USE ROLLBACK SEGMENT oltp_5