Oracle / PLSQL: SET TRANSACTION Statement

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

Description

In Oracle, the SET TRANSACTION statement 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 segment to a transaction.

Syntax

The syntax for the SET TRANSACTION statement 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 sets the transaction as a read-only transaction.

READ WRITE

Optional. If specified, it sets the transaction as a read/write transaction.

ISOLATION LEVEL

Optional. If specified, it has two options:

• ISOLATION LEVEL SERIALIZE - If a transaction attempts to update a resource that has been updated by another transaction and uncommitted, the transaction will fail.
• ISOLATION LEVEL READ COMMITTED - If a transaction requires row locks held by another transaction, the transaction will wait until the row locks are released.

USE ROLLBACK SEGMENT

Optional. If specified, it assigns the transaction to a rollback segment identified by 'segment_name' which is the segment name enclosed in quotes.

NAME

Assigns a name to the transaction identified by 'transaction_name' which is enclosed in quotes.

Example

Let's look at an example that shows how to use the SET TRANSACTION statement in Oracle.

READ ONLY

First, let's look 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 current transaction to read-only and assign it the name of 'RO_example'.

READ WRITE

Next, let's look at how to set a transaction as read/write using the SET TRANSACTION statement.

For example:

SET TRANSACTION READ WRITE NAME 'RW_example';