What are transaction isolation levels in SQL and what problems do they prevent?
Quick answer
Isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) control how much concurrent transactions can see of each other's changes, trading consistency against concurrency to prevent dirty reads, non-repeatable reads and phantom reads.
A dirty read means reading data another transaction has not committed. A non-repeatable read means reading the same row twice in one transaction and getting different values because someone committed in between. A phantom read means running the same query twice and getting a different set of rows because rows were inserted or deleted.
Read Uncommitted allows all three. Read Committed prevents dirty reads (the default in PostgreSQL, SQL Server and Oracle). Repeatable Read also prevents non-repeatable reads (the default in MySQL InnoDB, and it also prevents most phantoms there). Serializable prevents all three by making transactions behave as if they ran one after another, at the highest cost in locking or retries.
Choose the lowest level that is correct for the business rule, and use explicit locking (SELECT ... FOR UPDATE) or optimistic concurrency with a version column for specific hot spots such as inventory or balances.
Key points
- Higher isolation = fewer anomalies but less concurrency
- Defaults differ by database
- Use row locks or version columns for critical updates
Questions interviewers ask next
- What is a deadlock and how do you prevent it?
- What is MVCC?