SET TRANSACTION in PL/SQL – Syntax, Types, and Use Cases

Managing transactions effectively is crucial in database programming. Oracle PL/SQL provides the SET TRANSACTION command to configure how a transaction behaves before any SQL statements are executed. In this guide, we’ll explore the full power of SET TRANSACTION in PL/SQL, including syntax, types, isolation levels, and best practices.

What is SET TRANSACTION in PL/SQL?

The SET TRANSACTION command in PL/SQL allows developers to specify characteristics of a transaction before it begins. These characteristics include whether the transaction is read-only, its isolation level, and in legacy systems, which rollback segment to use.

Important: SET TRANSACTION in PL/SQL must be the first statement in a transaction block. If a transaction has already started, you need to issue a COMMIT or ROLLBACK before using this command.

Syntax of SET TRANSACTION

SET TRANSACTION READ ONLY;
SET TRANSACTION READ WRITE;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION USE ROLLBACK SEGMENT segment_name;

Each clause adjusts how the transaction behaves.

Types of SET TRANSACTION in PL/SQL

Command	Description
`READ ONLY`	Only allows `SELECT` operations. Ideal for reports where consistency across multiple reads is critical.
`READ WRITE`	Allows DML operations like `INSERT`, `UPDATE`, `DELETE`. This is the default setting.
`ISOLATION LEVEL READ COMMITTED`	Ensures only committed data is visible. Transactions wait if data is locked.
`ISOLATION LEVEL SERIALIZABLE`	Ensures full serializability. If conflict arises, the transaction fails instead of waiting.
`USE ROLLBACK SEGMENT`	(Legacy) Specifies the rollback segment for the transaction. Mostly obsolete due to Automatic Undo Management.

Real-World Use Cases of SET TRANSACTION

1. Read-Only Reporting

You can use SET TRANSACTION READ ONLY when running reports that require consistent data even if other users are modifying the database.

2. Data Integrity in Multi-User Systems

Using ISOLATION LEVEL SERIALIZABLE can help prevent anomalies in concurrent environments by ensuring that each transaction sees a consistent view of data.

3. Legacy Database Support

In older Oracle versions, specifying a rollback segment was necessary. Though rarely used today, knowing this helps in maintaining legacy systems.

Example: SET TRANSACTION READ ONLY in PL/SQL

SET TRANSACTION READ ONLY;

BEGIN
  FOR emp IN (SELECT * FROM employees WHERE department_id = 10) LOOP
    DBMS_OUTPUT.PUT_LINE(emp.first_name || ' ' || emp.last_name);
  END LOOP;
END;

This block runs in read-only mode and ensures consistent results even if the underlying employees table changes during execution.

Best Practices

Always use SET TRANSACTION at the start of your block.
For read-heavy operations, prefer READ ONLY mode.
For high-concurrency systems, carefully choose the isolation level.
In newer Oracle versions, avoid using rollback segments directly. Use Automatic Undo Management instead.

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.