Using SET TRANSACTION Statement in Oracle

This Oracle tutorial explains how to use the Oracle SET TRANSACTION announcement with syntax and examples.

Description

In Oracle, the SET TRANSACTION declaration is used to set a transaction as read-only, set a transaction as read/write, set a transaction’s isolation level, assign a name to a transaction, or assign a rollback phase to a transaction.

Syntax

The syntax for the SET TRANSACTION assertion in Oracle/PLSQL is:

SET TRANSACTION [ READ ONLY | READ WRITE ]
                [ ISOLATION LEVEL [ SERIALIZE | READ COMMITED ]
                [ USE ROLLBACK SEGMENT 'segment_name' ]
                [ NAME 'transaction_name' ];

Parameters or Arguments

READ ONLY Optional. If specified, it units the transaction as a read-only transaction. READ WRITE Optional. If specified, it units the transaction as a read/write transaction. ISOLATION LEVEL Optional. If specified, it has two options: ISOLATION LEVEL SERIALIZE – If a transaction tries to replace a resource that has been up to date by another transaction and uncommitted, the transaction will fail. ISOLATION LEVEL READ COMMITTED – If a transaction requires row locks held by way of another transaction, the transaction will wait till the row locks are released. USE ROLLBACK SEGMENT Optional. If specified, it assigns the transaction to a rollback phase identified by way of ‘segment_name’ which is the section name enclosed in quotes. NAME Assigns a name to the transaction identified by ‘transaction_name’ which is enclosed in quotes.

Example

Let’s seem to be at an example that suggests how to use the SET TRANSACTION assertion in Oracle.

READ ONLY

First, let’s seem at how to set a transaction as read-only using the SET TRANSACTION statement.

For example:

SET TRANSACTION READ ONLY NAME 'RO_example';

This SET TRANSACTION example would set the modern-day transaction to read-only and assign it the name of ‘RO_example’.

READ WRITE

Next, let’s seem to be at how to set a transaction as read/write the use of the SET TRANSACTION statement.

For example:

SET TRANSACTION READ WRITE NAME 'RW_example';