Database transaction isolation levels tutorial
What are the five SQL transaction isolation levels?
The SQL specification defines four transaction isolation levels, although a fifth transaction isolation levels appears in APIs including Java’s JDBC. That one is used to identify databases that don’t support any transaction isolation levels at all.
The five transaction isolation levels are as follows:
- TRANSACTION_READ_UNCOMMITTED
- TRANSACTION_READ_COMMITTED
- TRANSACTION_REPEATABLE_READ
- TRANSACTION_SERIALIZABLE
- TRANSACTION_NONE
The TRANSACTION_NONE isolation level is not part of the SQL specification, but many APIs include it.
Why are there different SQL transaction isolation levels?
Users want databases to be fast. One way to make a database fast is to remove any and all record locking. Removing database locks is the easiest way to increase the performance of a relational database.
Users also want their SQL queries to return consistent, correct and uncorrupted data. The easiest way to achieve that is to lock records whenever a query is performed.
As you can see, there is a tradeoff between database performance and consistency. Drop all the locks and you get fast access to potentially inconsistent data. Lock up everything, and clients get consistent data delivered at a snail’s pace.
In an enterprise environment, we must strike a balance between speed and consistency. Think of transaction isolation levels as a knob you can twist to tune your database to the ideal balance between these opposing forces.
How does the serializable isolation level work?
If consistent data is the highest priority and speed is a secondary consideration, TRANSACTION_SERIALIZABLE is the ideal SQL transaction isolation level to use.
When a database connection is set to TRANSACTION_SERIALIZABLE, the database locks records, rows and tables to ensure that it is impossible to experience problems such as the following:
- Dirty reads, where your receive data that get rolled back by an in-flight transaction.
- Phantom reads, where a second query within a transaction returns extra records the not present in the first query.
- Non-repeatable reads, where a second query within a transaction returns fewer records than the first.
This is the slowest of all of the transaction isolation levels SQL provides, but it is also the safest for returning consistent data.
How does the repeatable read isolation level work?
The TRANSACTION_REPEATABLE_READ isolation level locks every record returned from a query. Since locked records cannot be edited or deleted, a repeatable read isolation level eliminates both dirty reads and non-repeatable reads.
However, this level does not lock the database table as a whole. New records can be inserted while the transaction takes place, which makes it possible to experience a phantom read.
How does the read committed transaction isolation level work?
Sometimes an in-flight database transaction updates a database record, runs into an exception, and then rolls back the update.
If your query reads the updated data after the update but before the transaction is rolled back, you end up with a dirty read.
The TRANSACTION_READ_COMMITTED isolation level makes it impossible to see any updates from other transactions until those transactions have either fully committed or have successfully been rolled back.
The read committed transaction isolation level makes dirty reads impossible, but phantom and nonrepeatable reads are still a possibility.
What does the read uncommitted transaction isolation level work?
The TRANSACTION_READ_UNCOMMITTED isolation level does not lock, nor does it isolate in-flight database transaction. As a result, transactions that use this isolation level may experience all three aforementioned problems:
- dirty reads
- nonrepeatable reads
- phantom reads
The benefit is that because there is no locking or transaction monitoring, queries that execute under this transaction isolation level are the fastest.
What does TRANSACTION_NONE mean?
The SQL specification does not include the TRANSACTION_NONE isolation level, but it is used by various APIs like JDBC. This level simply marks databases that do not support any types of transaction isolation levels at all.