SET TRANSACTION ISOLATION

Description

Explicitly sets the isolation level for a transaction. Isolation levels specify the degree to which one transaction can modify data or database objects being used by another concurrent transaction.

Syntax

SET TRANSACTION ISOLATION LEVEL  isolation_level ;

isolation_level ::

        READ UNCOMMITTED

      | READ COMMITTED

      | REPEATABLE READ

      | SERIALIZABLE

Arguments

READ UNCOMMITTED

Allows dirty reads, non-repeatable reads, and phantoms (described below in Notes).

READ COMMITTED

Default. Prohibits dirty reads; allows non-repeatable reads and phantoms.

REPEATABLE READ

Prohibits dirty reads and non-repeatable reads; allows phantoms.

SERIALIZABLE

Prohibits dirty reads, non-repeatable reads, and phantoms (see the following notes). It guarantees that concurrent transactions will not affect each other; they behave as if they were executing serially, not concurrently.

Notes

SET TRANSACTION allows the user to choose the isolation level for future transactions. If a transaction is currently active, SET TRANSACTION generates an error.

The isolation level specifies the degree to which one transaction is isolated from the effects of concurrent access of the database by other transactions. The appropriate level of isolation depends on how a transaction needs to be isolated from effects of another transaction. Higher isolation levels provide greater data consistency to the user’s transaction but reduce access to data by concurrent transactions.

The isolation level SERIALIZABLE guarantees the highest consistency. The isolation level READ UNCOMMITTED guarantees the least consistency. Only READ COMMITTED and REPEATABLE READ are supported. The ANSI/ISO standard defines isolation levels in terms of the of the inconsistencies they allow, as detailed next:

Permitted Inconsistencies in Transactions

Dirty read	Allows the transaction to read a row that has been inserted or modified by another transaction, but not committed. If the other transaction rolls back its changes, the transaction will have read a row that never existed, in the sense that it was never committed.
Non-repeatable read	Allows the transaction to read a row that another transaction modifies or deletes before the next read operation. If the other transaction commits the change, the transaction will receive modified values, or discover the row is deleted, on subsequent read operations.
Phantom	Allows the transaction to read a range of rows that satisfies a given search condition. If another transaction adds rows before a second read operation using the same search condition, then the transaction receives a different collection of rows with the same search condition.

Authorization

None.

SQL Compliance	SQL-92.
Environment	Embedded SQL and interactive
Related Statements	COMMIT, ROLLBACK