SET TRANSACTION

SET TRANSACTION:

A transaction is a set of one or more statements that are executed together as a unit, so either all of the statements are executed, or none of the statements is executed, it can be used when you do not want one statement to take effect unless another one completes.

Use the SET TRANSACTION statement to establish the current transaction as read only or read write, establish its isolation level, or assign it to a specified rollback segment.

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. Oracle Database implicitly commits the current transaction before and after executing a data definition language (DDL) statement.

SET TRANSACTION

{ { READ { ONLY | WRITE }

| ISOLATION LEVEL

{ SERIALIZABLE | READ COMMITTED }

| USE ROLLBACK SEGMENT rollback_segment

}

[ NAME 'text' ]

| NAME ‘text’

} ;

READ ONLY

The READ ONLY clause establishes the current transaction as a read-only transaction. This clause established transaction-level read consistency. It can not change data, hence the DML statements update, insert and delete are not possible. All subsequent queries in that transaction see only changes that were committed before the transaction began. Read-only transactions are useful for reports that run multiple queries against one or more tables while other users update these same tables. This clause is not supported for the user “SYS”. That is, queries by SYS will return changes made during the transaction even if SYS has set the transaction to be READ ONLY.

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

• Subqueries—that is, SELECT statements without the for_update_clause
• LOCK TABLE
• SET ROLE
• ALTER SESSION
• ALTER SYSTEM

READ WRITE

Specify READ WRITE to establish the current transaction as a read/write transaction. This clause establishes statement-level read consistency, which is the default. 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.

It’s important to notice that you cannot toggle between transaction-level and statement-level read consistency in the same transaction.

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

This specifies the rollback segment to be used for this transaction. A transaction can not be read only when this clause is used.

Notice that this clause is relevant and valid only if you are using rollback segments for undo. Oracle strongly recommends that you use automatic undo management to handle undo space. If you follow this recommendation and run your database in automatic undo mode, then Oracle Database ignores this clause.

NAME Clause

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.

Example for setting Transactions:

The following statements could be run at midnight of the last day of every month to count the products and quantities on hand in the Toronto warehouse in the sample Order Entry (oe) schema. This report would not be affected by any other user who might be adding or removing inventory to a different warehouse.

COMMIT;

SET TRANSACTION READ ONLY NAME ‘Toronto’;

SELECT product_id, quantity_on_hand FROM inventories

WHERE warehouse_id = 5;

COMMIT;

The first COMMIT statement ensures that SET TRANSACTION is the first statement in the transaction. The last COMMIT statement does not actually make permanent any changes to the database. It simply ends the read-only transaction.