Q23: What are Transaction Isolation Levels and Read Anomalies?
SQL defines four standard transaction isolation levels to balance concurrency against data consistency anomalies.
Concurrency Read Anomalies
- Dirty Read: Reading uncommitted changes made by another concurrent transaction.
- Non-Repeatable Read: Re-reading the same record yields modified values because another committed transaction updated it.
- Phantom Read: Re-running a range query returns newly inserted rows committed by another transaction.
Isolation Levels & Permitted Anomalies
Read Uncommitted:
Dirty Read | Non-Repeatable Read | Phantom ReadMaximum concurrency, no isolation guarantees.
Read Committed:
Non-Repeatable Read | Phantom ReadPrevents dirty reads. Common default in PostgreSQL and SQL Server.
Repeatable Read:
Phantom Read (in some engines)Ensures read locks remain held until transaction completion.
Serializable:
Strict Isolation (Zero Anomalies)Executes transactions sequentially via lock ranges or serializability checks.
Key Takeaways
- Higher isolation levels prevent more read anomalies, but reduce concurrent transaction throughput.
- Read Committed is the standard default isolation level in many enterprise relational database engines.