Transactions & Isolation Levels

In a Nutshell

A transaction groups multiple database operations into a single unit that either fully succeeds or fully rolls back. But the hard question isn't what a transaction is — it's how much concurrent transactions can see of each other's work. This is controlled by the isolation level, and it's a trade-off: stronger isolation means more correctness guarantees but lower concurrency and higher latency. Most databases offer four standard levels, each permitting or preventing specific anomalies. Choosing the right level — not just the strongest — is a critical design decision.

How It Actually Works

The Four Standard Isolation Levels

Level	Speed	Safety	What It Prevents	What It Allows
Read Uncommitted	Fastest	Weakest	Nothing	Dirty reads, non-repeatable reads, phantoms
Read Committed	Fast	Moderate	Dirty reads	Non-repeatable reads, phantoms
Repeatable Read	Moderate	Strong	Dirty reads, non-repeatable reads	Phantoms (in some implementations)
Serializable	Slowest	Strongest	Everything	Nothing — behaves as if transactions run one at a time

The Anomalies Explained

Dirty Read

Reading data that another transaction wrote but hasn't committed yet.

T1: UPDATE accounts SET balance = 0 WHERE user = 'Alice';
T2: SELECT balance FROM accounts WHERE user = 'Alice';  → sees 0
T1: ROLLBACK;  -- Alice's balance is actually still $100
T2: Already acted on the $0 value — incorrect!

Prevented by: Read Committed and above.

Non-Repeatable Read

Reading the same row twice within a transaction and getting different values.

T1: SELECT balance FROM accounts WHERE user = 'Alice';  → $100
T2: UPDATE accounts SET balance = 50 WHERE user = 'Alice'; COMMIT;
T1: SELECT balance FROM accounts WHERE user = 'Alice';  → $50 (changed!)

Prevented by: Repeatable Read and above.

Phantom Read

A query returns different rows when executed twice — new rows appear (or disappear).

T1: SELECT COUNT(*) FROM orders WHERE status = 'pending';  → 5
T2: INSERT INTO orders (status) VALUES ('pending'); COMMIT;
T1: SELECT COUNT(*) FROM orders WHERE status = 'pending';  → 6 (phantom!)

Prevented by: Serializable only (in standard SQL). PostgreSQL's Repeatable Read also prevents phantoms via MVCC.

Lost Update

Two transactions read the same value, modify it, and write it back — one update overwrites the other.

T1: Read balance = $100
T2: Read balance = $100
T1: Write balance = $100 - $30 = $70
T2: Write balance = $100 - $50 = $50  ← T1's update is lost!

Prevented by: Repeatable Read (with FOR UPDATE locking) or Serializable.

How Databases Implement Isolation

Mechanism	How It Works	Used By
Locks	Transactions acquire locks on rows; others wait	MySQL (some cases), SQL Server
MVCC (Multi-Version Concurrency Control)	Each transaction sees a snapshot of the database; writes create new versions	PostgreSQL, MySQL InnoDB, Oracle
SSI (Serializable Snapshot Isolation)	MVCC + dependency tracking to detect conflicts	PostgreSQL (Serializable), CockroachDB

MVCC is the dominant approach because it allows readers and writers to not block each other — a huge concurrency advantage over pure locking.

Default Isolation Levels

Database	Default Level	Notes
PostgreSQL	Read Committed	Offers Serializable via SSI
MySQL (InnoDB)	Repeatable Read	Close to snapshot isolation
SQL Server	Read Committed	Also offers Snapshot Isolation
Oracle	Read Committed	Serializable available but rarely used
CockroachDB	Serializable	Default — strongest guarantee
SQLite	Serializable	Single-writer model

Seeing It in Action

Scenario: Preventing double-booking in a reservation system

-- Problem: Two users try to book the same time slot simultaneously

-- WRONG (Read Committed — non-repeatable read possible):
BEGIN;
  SELECT * FROM slots WHERE room = 'A' AND time = '10:00' AND booked = false;
  -- Both T1 and T2 see the slot as available
  UPDATE slots SET booked = true, booked_by = 'user1' WHERE room = 'A' AND time = '10:00';
COMMIT;
-- Both transactions succeed → double-booked!

-- CORRECT (Option 1: SELECT FOR UPDATE — pessimistic locking):
BEGIN;
  SELECT * FROM slots WHERE room = 'A' AND time = '10:00' AND booked = false FOR UPDATE;
  -- T2 blocks here until T1 commits/rollbacks
  UPDATE slots SET booked = true, booked_by = 'user1' WHERE room = 'A' AND time = '10:00';
COMMIT;
-- T2 now sees booked = true → booking rejected

-- CORRECT (Option 2: Serializable isolation):
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
  SELECT * FROM slots WHERE room = 'A' AND time = '10:00' AND booked = false;
  UPDATE slots SET booked = true, booked_by = 'user1' WHERE room = 'A' AND time = '10:00';
COMMIT;
-- Database detects the conflict and aborts one transaction
-- Application retries the aborted transaction

Interview Questions

1. Q: What isolation level would you use for a financial transaction system? Why? Hint: Serializable or Repeatable Read with explicit locking (SELECT FOR UPDATE). Financial transactions cannot tolerate lost updates or phantom reads. The performance cost of stronger isolation is justified by the correctness requirement. CockroachDB defaults to Serializable for this reason.

2. Q: Explain the difference between optimistic and pessimistic concurrency control. Hint: Pessimistic: lock the resource before modifying (SELECT FOR UPDATE) — prevents conflicts but reduces concurrency. Optimistic: proceed without locks, check for conflicts at commit time (version numbers, SSI) — higher concurrency but requires retry logic for conflicts. Use pessimistic for high-contention resources; optimistic for low-contention.

3. Q: What is MVCC, and why is it important? Hint: Multi-Version Concurrency Control keeps multiple versions of each row. Readers see a consistent snapshot from their transaction's start time; writers create new versions. Key benefit: readers never block writers and vice versa — unlike locking where a write lock blocks all reads. Used by PostgreSQL, MySQL InnoDB, Oracle.

4. Q: Your application is experiencing deadlocks. What do you do? Hint: 1) Identify the queries involved (database logs). 2) Ensure consistent lock ordering (always lock table A before table B). 3) Keep transactions short (less time holding locks). 4) Use lower isolation levels where safe. 5) Add retry logic — deadlocks are expected in concurrent systems; the database aborts one transaction, and the application should retry.

5. Q: PostgreSQL defaults to Read Committed. When would you switch to Repeatable Read or Serializable? Hint: Repeatable Read: when a transaction reads the same data twice and needs consistent results (reports, aggregations within a transaction). Serializable: when concurrent transactions could produce incorrect results that no weaker level prevents (booking systems, inventory management). Always benchmark — stronger isolation reduces throughput.

References

• Designing Data-Intensive Applications by Martin Kleppmann — Chapter 7: Transactions (weak isolation levels section)
• PostgreSQL Transaction Isolation — official isolation level documentation
• MySQL InnoDB Transaction Isolation — MySQL isolation specifics

Dive Deeper

• A Critique of ANSI SQL Isolation Levels — the seminal paper revealing gaps in the SQL standard's definitions
• Hermitage — Testing Transaction Isolation — Martin Kleppmann's test suite for real-world isolation behavior
• CockroachDB — Transaction Contention — practical advice on handling contention in distributed SQL