How transaction isolation levels work
What are the five SQL transaction isolation levels?
There are four transaction isolation levels defined in the SQL specification, although a fifth transaction isolation levels appears in APIs like Java’s JDBC to identify databases that don’t support any transaction isolation levels at all.
The five transaction isolation levels are:
- 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 do. It identifies
Why are there different SQL transaction isolation levels?
Users want databases to be fast.
And 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 or a relational database.
Users also want their SQL queries to return consistent, correct, and uncorrupted data. And 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 snails pace.
In an enterprise environment, a balance must be struck between speed and consistency. You can think of transaction isolation levels as a knob you can twist to ideally 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 your database connection is set to TRANSACTION_SERIALIZABLE, records, rows and tables will be locked by the database to ensure that it is impossible to experience:
- 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
- and 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 when it comes to 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:
- dirty reads
- non-repeatable reads
However, since the database table as a whole is not locked, new records can be inserted while the transaction takes place, making it possible to experience a phantom read.
How does the read committed transaction isolation level work?
Sometimes an in-flight database transaction will update a database record, run into an exception, and then rollback 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 no locking, nor does it isolate in-flight database transaction. As a result, transactions that use this isolation level may experience:
- dirty reads
- nonrepeatable reads
- phantom reads
The benefit is that due to the lack of 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 to simply mark databases that do not support any types of transaction isolation levels at all.