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.
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.
- Assigns a name to the transaction identified by 'transaction_name' which is enclosed in quotes.
Let's look at an example that shows how to use the SET TRANSACTION statement in Oracle.
First, let's look at how to set a transaction as read-only using the SET TRANSACTION statement.
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'.
Next, let's look at how to set a transaction as read/write using the SET TRANSACTION statement.
SET TRANSACTION READ WRITE NAME 'RW_example';