Oracle never lets a reader wait for a writer, or a writer for a reader. Every query sees a consistent snapshot of the data as of the moment it started, rebuilt from undo when rows change meanwhile. That is the default isolation level, READ COMMITTED. SET TRANSACTION extends the snapshot to a whole transaction, for reports that must add up across queries, or for logic that must not overwrite changes it never saw.
Code for This Guide
The main examples are in the examples/transactions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
set transaction {read only | read write
| isolation level {serializable | read committed}} [name 'text'];SET TRANSACTION must be the first statement of a transaction, and applies until it ends.
| Level | Each query sees | Changes allowed |
|---|---|---|
| READ COMMITTED (default) | Data committed before the query started | Yes |
| SERIALIZABLE | Data committed before the transaction started, plus its own changes | Yes, but not to rows others changed since it began |
| READ ONLY | Data committed before the transaction started | No |
A Serializable Transaction
Example:
set transaction isolation level serializable; select dbms_transaction.local_transaction_id is not null as has_transaction; select count(*) as flights from flights; commit;
Output:
Transaction ISOLATION succeeded.
HAS_TRANSACTION
__________________
true
FLIGHTS
__________
2856
Commit complete.After SET TRANSACTION, the transaction exists even before any change, as DBMS_TRANSACTION shows, and every query in it sees the same snapshot.
When Serializable Refuses an Update
Here another transaction, an autonomous one, changes a row and commits after the serializable transaction began. When the serializable transaction then updates the same row, Oracle refuses.
Example:
set transaction isolation level serializable; select status from aircraft where tail_number = 'A6-NAE'; -- another transaction changes the row and commits after this transaction began declare pragma autonomous_transaction; begin update aircraft set status = status where tail_number = 'A6-NAE'; commit; end; / -- this transaction may not overwrite a change it could not see update aircraft set status = status where tail_number = 'A6-NAE'; rollback;
Output:
Transaction ISOLATION succeeded. STATUS _________ ACTIVE PL/SQL procedure successfully completed. Error starting at line : 13 In command - update aircraft set status = status where tail_number = 'A6-NAE' Error at Command Line : 13 Column : 8 Error report - SQL Error: ORA-08177: can't serialize access for this transaction Rollback complete.
ORA-08177 protects the transaction from overwriting a change it could not see. The application should roll back and retry the whole transaction.
A Read-Only Transaction for Reports
READ ONLY gives a stable snapshot for several queries and forbids changes, which suits month-end reports whose totals must agree with each other. NAME labels the transaction for monitoring.
Example:
set transaction read only name 'month-end report'; select count(*) as bookings from bookings; update bookings set status = status where booking_id = 1; commit;
Output:
Transaction READ succeeded.
BOOKINGS
___________
700
Error starting at line : 3
In command -
update bookings set status = status where booking_id = 1
Error at Command Line : 3 Column : 8
Error report -
SQL Error: ORA-01456: may not perform insert, delete, update operation inside a READ ONLY
transaction
Commit complete.The UPDATE fails with ORA-01456; COMMIT ends the read-only transaction.
Things to Know
- Long serializable or read-only transactions need enough undo to rebuild old data; if it is overwritten, queries fail with ORA-01555.
- ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE sets the level for every transaction of the session.
- Oracle has no READ UNCOMMITTED: queries never see other transactions' uncommitted changes.
Related Guides
Conclusion
Oracle's default READ COMMITTED gives each query a consistent snapshot. SERIALIZABLE extends it to the transaction and refuses updates to rows changed since it began with ORA-08177, and READ ONLY gives reports one stable snapshot with no changes allowed. Set either with SET TRANSACTION as the first statement.
