Transaction Isolation Levels Explained
Fast recall
Quick revision
Core answer
Whether you understand the trade-off between consistency and performance in concurrent database access and can explain what anomalies each level prevents.
Memorizing one anomaly table as universal even though PostgreSQL and InnoDB implement some levels more strongly than the SQL minimum.
Explain PostgreSQL's READ COMMITTED default and stronger REPEATABLE READ snapshot, InnoDB's REPEATABLE READ default, and the need to retry serialization failures.
Interview-ready guide
Interview answer
The four SQL isolation levels are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. A dirty read sees uncommitted work, a non-repeatable read sees a row change between reads, and a phantom changes the rows matching a repeated predicate. The standard defines minimum protections, but products differ. PostgreSQL maps READ UNCOMMITTED to READ COMMITTED and its REPEATABLE READ snapshot prevents the standard phantom example. InnoDB defaults to REPEATABLE READ and uses both MVCC and locking rules. SERIALIZABLE requires a result equivalent to some serial order and can abort work, so safe retry logic is essential. Choose from the business invariant and the selected database's documentation, not a memorized universal table.
Transaction Isolation Levels Explained
Direct Answer
SQL names four isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. They set minimum rules for concurrent visibility, but databases can implement stronger behavior. SERIALIZABLE produces an outcome equivalent to some serial order and may abort work that the application must retry.
Code Example
-- PostgreSQL: set the level when the transaction starts.
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- Later plain SELECT statements use the same transaction snapshot.
SELECT balance FROM accounts WHERE id = 1;
COMMIT;
This example describes PostgreSQL. Other engines may use different snapshots or locks at the same named level.
Common Mistakes & Pitfalls
- Using one universal anomaly table: Same-named levels can be stronger in a specific database.
- Ignoring retries: Serializable implementations may abort a transaction with a serialization failure; the application must retry the whole unit safely.
- Assuming stronger means more locks: MVCC engines can provide snapshots and detect conflicts without locking every read. Measure the actual workload.
Check your understanding
Answer these without looking back at the article.
- What is a dirty read?
- What is a phantom read?
Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.