SET TRANSACTION ISOLATION
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.
SET TRANSACTION ISOLATION LEVEL isolation_level ;
| READ COMMITTED
| REPEATABLE READ
Allows dirty reads, non-repeatable reads, and phantoms (described below in Notes).
Default. Prohibits dirty reads; allows non-repeatable reads and phantoms.
Prohibits dirty reads and non-repeatable reads; allows phantoms.
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.
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
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.
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.
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.
Embedded SQL and interactive