Transactions & Isolation Levels

Database Transactions and Isolation Levels

Overview

In database systems, a transaction is a sequence of operations performed as a single logical unit of work. Isolation levels define the degree to which a transaction must be isolated from the data modifications made by other concurrent transactions.

🧠 Mental model: Imagine multiple people editing the same shared document. An isolation level dictates whether someone can see your drafted sentences before you hit "save" (commit), or if they must wait until you are completely done.

Key Concepts

Transactions guarantee the ACID properties (Atomicity, Consistency, Isolation, Durability). However, strict isolation (Serializable) has a massive performance cost because it forces transactions to execute essentially sequentially. To improve performance, SQL standards define weaker isolation levels that allow certain concurrency anomalies.

Concurrency Anomalies

Dirty Read: Reading uncommitted changes from another transaction. If that transaction rolls back, you read data that never really existed.
Non-repeatable Read: Reading the same row twice in one transaction and getting different values because another transaction updated it in between.
Phantom Read: Executing the same range query twice in one transaction and getting a different set of rows because another transaction inserted or deleted rows matching the condition.

Standard Isolation Levels

Isolation Level	Dirty Read	Non-repeatable Read	Phantom Read	Performance
Read Uncommitted	Possible	Possible	Possible	Highest
Read Committed	Prevented	Possible	Possible	High (Default in Postgres/SQL Server)
Repeatable Read	Prevented	Prevented	Possible	Medium (Default in MySQL/InnoDB)
Serializable	Prevented	Prevented	Prevented	Lowest

Trade-offs

The core tradeoff is data correctness versus system throughput. Serializable guarantees perfect correctness by preventing all concurrency anomalies, but it uses heavy locking or complex validation (like SSI) that causes high lock contention and transaction aborts, crippling throughput under heavy load. Weaker isolation levels like Read Committed offer high throughput but force application developers to handle race conditions and edge-case anomalies in code.

Interview Tips

Know the default isolation level of popular databases (e.g., MySQL is Repeatable Read, PostgreSQL is Read Committed). This shows practical engineering experience.
If you need strict consistency for financial transactions (e.g., transferring money), explicitly state you would use Serializable isolation or explicit row-level locking (SELECT ... FOR UPDATE).
Mention MVCC (Multi-Version Concurrency Control), the mechanism most modern databases use to implement isolation levels without readers blocking writers.

Summary

Transactions group operations into a single logical unit of work.
Isolation levels trade perfect data correctness for higher concurrency and throughput.
The four standard levels are Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
Each level prevents specific concurrency anomalies: dirty reads, non-repeatable reads, and phantom reads.
Most databases default to Read Committed or Repeatable Read, using MVCC to avoid locking.