Databaseshard

Isolation levels: what’s the difference between Read Committed, Repeatable Read, and Serializable?

Answer

Read Committed prevents dirty reads but allows non‑repeatable reads. Repeatable Read guarantees the same rows won’t change during the transaction but can allow phantom rows. Serializable is the strictest; it behaves as if transactions run one‑by‑one, preventing phantoms but reducing concurrency.

Advanced answer

Deep dive

Expanding on the short answer — what usually matters in practice:

Context (tags): database, transactions, isolation, consistency
Data model and access patterns: dominant queries (read/write ratio, sorting, pagination).
Indexes: when they help vs hurt (write amplification, memory).
Consistency & transactions: what’s guaranteed and what can bite you.
Explain the "why", not just the "what" (intuition + consequences).
Trade-offs: what you gain/lose (time, memory, complexity, risk).
Edge cases: empty inputs, large inputs, invalid inputs, concurrency.

Examples

A tiny example (query shape):

-- Example: index + query shape
SELECT *
FROM users
WHERE email = '[email protected]'
LIMIT 1;

Common pitfalls

Too generic: no concrete trade-offs or examples.
Mixing average-case and worst-case (e.g., complexity).
Ignoring constraints: memory, concurrency, network/disk costs.

Interview follow-ups

When would you choose an alternative and why?
What production issues show up and how do you diagnose them?
How would you test edge cases?