1. System Prerequisites
Transactions & Isolation Levels
In simple words
When many people use a database at the same moment, their changes can trip over each other. One person might read a value another hasn't finished saving, or two people might both read a stock count of 10 and both write back 9, so one sale is lost. Isolation levels are settings that decide how much the database protects you from this. Stronger protection is safer but makes people wait for each other, so it is slower. This note covers what can go wrong, and how to choose the right setting.
What is a Transaction — Precise Definition
A transaction is a logical unit of work that groups one or more database operations into an atomic sequence. The database guarantees that either all operations in the group succeed, or none of them take effect.
-- The full transaction lifecycle
START TRANSACTION; -- or BEGIN
SELECT balance FROM accounts WHERE id = 1; -- read
UPDATE accounts SET balance = balance - 500 WHERE id = 1; -- write
UPDATE accounts SET balance = balance + 500 WHERE id = 2; -- write
INSERT INTO transaction_log VALUES (...); -- audit
COMMIT; -- permanently save all changes
-- OR
ROLLBACK; -- undo all changes since START TRANSACTION
Transaction States — The Lifecycle
┌─────────┐
│ ACTIVE │ ← transaction executing operations
└────┬────┘
│ all operations done
┌────▼────┐
│PARTIALLY│ ← ready to commit, not yet written to disk
│COMMITTED│
└────┬────┘
┌─────────┴─────────┐
success failure
│ │
┌─────▼─────┐ ┌─────▼─────┐
│ COMMITTED │ │ FAILED │
└─────┬─────┘ └─────┬─────┘
│ │
▼ ▼
permanent ABORTED / ROLLED BACK
changes ← all changes undone,
DB returns to state
before transaction began
The Four Concurrency Problems — In Depth
When multiple transactions run simultaneously without proper isolation, these problems occur. Interviewers expect you to explain each with examples.
Problem 1 — Dirty Read
Reading uncommitted data from another transaction.
T1 reads data that T2 modified but has not yet committed.
If T2 rolls back, T1 has used data that never officially existed.
Timeline:
Time│ Transaction 1 (T1) Transaction 2 (T2)
────┼───────────────────────────────────────────────────────────
1 │ UPDATE accounts
│ SET balance = 9000 ← not committed
│ WHERE id = 1
2 │ SELECT balance FROM accounts
│ WHERE id = 1 (still not committed)
│ → sees 9000 ← DIRTY READ
3 │ (makes decision based on 9000)
4 │ ROLLBACK
│ ← balance is back to 5000
5 │ T1 made a decision based on 9000
│ which never existed → wrong result
Real-world impact:
A lending system reads a "pending" deposit to decide credit limit
The deposit rolls back → credit extended based on money that doesn't exist
Prevented by: READ COMMITTED and above.
Problem 2 — Non-Repeatable Read
Same row returns different values when read twice in the same transaction.
T1 reads a row. T2 modifies and commits that row. T1 reads it again.
Same query, same transaction, different results.
Timeline:
Time│ Transaction 1 (T1) Transaction 2 (T2)
────┼───────────────────────────────────────────────────────────
1 │ SELECT price FROM products
│ WHERE id = 'P01' → 500
2 │ UPDATE products
│ SET price = 600
│ WHERE id = 'P01'
│ COMMIT ✅
3 │ SELECT price FROM products
│ WHERE id = 'P01' → 600 ← different!
4 │ T1 is generating a sales report
│ Line 1: price was 500
│ Line 2: price is 600
│ Report is internally inconsistent → incorrect analysis
Real-world impact:
Financial report calculates totals across multiple queries
Prices change between queries → totals are based on mixed price points
Audit report is wrong
Prevented by: REPEATABLE READ and above.
Problem 3 — Phantom Read
New rows appear (or disappear) in a repeated range query within same transaction.
Unlike non-repeatable read (existing row changes),
phantom read is about NEW rows being inserted by another transaction.
Timeline:
Time│ Transaction 1 (T1) Transaction 2 (T2)
────┼───────────────────────────────────────────────────────────
1 │ SELECT COUNT(*) FROM orders
│ WHERE status = 'PENDING'
│ → returns 50
2 │ INSERT INTO orders
│ (status='PENDING', ...)
│ COMMIT ✅
3 │ SELECT COUNT(*) FROM orders
│ WHERE status = 'PENDING'
│ → returns 51 ← phantom row appeared!
4 │ T1 reserved capacity for 50 orders
│ now there are 51 → capacity exceeded → logic is wrong
Real-world impact:
Batch job processes "all pending orders from this morning"
New orders inserted during processing appear in second scan
Same order may be processed twice
Prevented by: SERIALIZABLE (and MySQL REPEATABLE READ via gap locks).
Problem 4 — Lost Update
Two transactions read the same value, both update it, one update is lost.
Timeline:
Time│ Transaction 1 (T1) Transaction 2 (T2)
────┼───────────────────────────────────────────────────────────
1 │ SELECT stock FROM products
│ WHERE id = 'P01' → 10
2 │ SELECT stock FROM products
│ WHERE id = 'P01' → 10
3 │ UPDATE products
│ SET stock = 10 - 1 = 9
│ WHERE id = 'P01'
│ COMMIT ✅
4 │ UPDATE products
│ SET stock = 10 - 1 = 9
│ WHERE id = 'P01'
│ COMMIT ✅
5 │ stock = 9 (T2 overwrote T1's change)
│ TWO items sold, but stock went from 10 to 9 (not 8)
│ → OVERSOLD by 1 unit → LOST UPDATE
Real-world impact:
Flash sale: 10 units available, 2 concurrent buyers
Both succeed → stock shows 9 → 1 item given away for free
Flipkart-scale: thousands of concurrent buyers → massive oversell
Prevented by: Explicit locking (SELECT ... FOR UPDATE) or SERIALIZABLE.
The Four Isolation Levels — Deep Dive
Isolation Level │ Dirty │ Non-Repeatable │ Phantom │ Lost Update
───────────────────┼───────┼────────────────┼─────────┼────────────
READ UNCOMMITTED │ ✗ │ ✗ │ ✗ │ ✗
READ COMMITTED │ ✓ │ ✗ │ ✗ │ ✗
REPEATABLE READ │ ✓ │ ✓ │ ✗ (*) │ ✓
SERIALIZABLE │ ✓ │ ✓ │ ✓ │ ✓
✓ = problem prevented ✗ = problem can occur
(*) MySQL InnoDB prevents phantoms in REPEATABLE READ via gap locks
1. READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Behaviour: Reads see ALL changes — even uncommitted ones from other transactions.
No read locks acquired at all.
Performance: Highest throughput — no blocking whatsoever
Problems: All four concurrency problems can occur
When to use: Almost never in production.
Possibly for rough real-time counters where approximate values
are acceptable (page view counter, rough inventory estimate).
Example acceptable use:
Real-time dashboard showing "approximately how many users are online"
Exact number doesn't matter → dirty reads acceptable
2. READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Behaviour: Each statement within a transaction reads the latest committed snapshot.
If you run the same SELECT twice, you may get different results
(because other transactions may have committed between your two reads).
Mechanism: Statement-level snapshot (new snapshot per statement, not per transaction)
Default in: PostgreSQL, Oracle, SQL Server, DB2
Problems prevented: Dirty reads ✓
Problems remaining: Non-repeatable reads ✗, Phantom reads ✗, Lost updates ✗
Performance: Good — reads don't block writes, writes don't block reads
(MVCC: readers and writers don't block each other)
When to use: Most general-purpose OLTP applications.
When you need fresh data on every read and slight inconsistency
between multiple reads in a transaction is tolerable.
Example:
User profile update: read profile, show to user, update on save
If someone else updated the profile between read and display → acceptable
→ READ COMMITTED is fine
3. REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Behaviour: A snapshot is taken at the START of the transaction.
All reads within the transaction see data as of that snapshot.
Even if other transactions commit changes, your reads stay the same.
Mechanism: Transaction-level snapshot (MVCC — snapshot taken at BEGIN)
Default in: MySQL InnoDB
Problems prevented: Dirty reads ✓, Non-repeatable reads ✓
Problems remaining: Phantom reads ✗ (theoretically; MySQL InnoDB prevents via gap locks)
Performance: Good — reads still don't block writes (MVCC)
Writes may block other writes on the same rows
When to use:
Financial reports that span multiple queries (consistent view needed)
Batch jobs that read multiple tables and need a consistent snapshot
Any transaction where you need to read the same data multiple times
and get the same result
Example:
Month-end revenue report:
SELECT SUM(amount) FROM orders WHERE month = '2024-01'; → ₹10M
SELECT COUNT(*) FROM orders WHERE month = '2024-01'; → 5000
→ Both queries should be based on the SAME snapshot
→ If READ COMMITTED: new orders committed between queries could affect result
→ REPEATABLE READ: both queries see the same snapshot → consistent report
MySQL-specific note:
InnoDB uses gap locks in REPEATABLE READ to prevent phantom reads
A SELECT ... WHERE range query acquires gap locks on the scanned range
→ Other transactions cannot INSERT rows into that range until lock released
4. SERIALIZABLE
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Behaviour: Transactions are fully isolated — execute as if one at a time.
All reads acquire shared locks (or predicate locks).
No concurrent transaction can modify data being read.
Mechanism: Range locks / predicate locks prevent any insert/update/delete
to rows that match the WHERE clause of any active read
Problems prevented: ALL — dirty reads, non-repeatable reads, phantom reads, lost updates
Performance: Lowest throughput — high blocking
Transactions frequently wait for each other
Deadlocks more common
When to use:
Core financial transactions (debit + credit)
Inventory deduction during flash sale (prevent oversell)
Seat reservation systems (no double booking)
Any case where any concurrency anomaly is unacceptable
Example:
Airline seat booking:
T1: SELECT * FROM seats WHERE flight='AI101' AND status='AVAILABLE' → 1 seat left
T2: SELECT * FROM seats WHERE flight='AI101' AND status='AVAILABLE' → 1 seat left
T1: UPDATE seats SET status='BOOKED' WHERE seat_id='12A' COMMIT
T2: UPDATE seats SET status='BOOKED' WHERE seat_id='12A'
→ BLOCKED (T1 holds lock on 12A)
→ After T1 commits: T2 sees seat is BOOKED → can react accordingly
→ No double booking possible
Choosing the Right Isolation Level
Decision framework:
1. Are dirty reads acceptable?
NO → use READ COMMITTED or higher (almost always NO for production)
YES → READ UNCOMMITTED (very rare)
2. Do you need consistent reads across multiple queries in a transaction?
YES → REPEATABLE READ or higher
(reports, batch processing, multi-step calculations)
NO → READ COMMITTED is enough
3. Do you need to prevent phantom rows?
YES → SERIALIZABLE (or MySQL REPEATABLE READ for MySQL)
NO → REPEATABLE READ is enough
4. Is this a critical write path where any anomaly means data corruption?
YES → SERIALIZABLE + explicit locking for critical sections
NO → REPEATABLE READ or READ COMMITTED with careful query design
Real-world defaults:
MySQL InnoDB: REPEATABLE READ ← good default for most apps
PostgreSQL: READ COMMITTED ← fine for most OLTP
Payment systems: SERIALIZABLE or explicit row-level locks
Analytics: READ COMMITTED or READ UNCOMMITTED (data freshness > consistency)
Explicit Locking — Taking Control
Sometimes you need stronger guarantees than isolation level alone provides.
SELECT FOR UPDATE — Pessimistic Locking
-- Prevent lost update in inventory deduction
BEGIN;
SELECT stock FROM products
WHERE product_id = 'P01'
FOR UPDATE; -- ← acquires exclusive lock on this row
-- other transactions trying to SELECT FOR UPDATE or UPDATE
-- this row will WAIT until this transaction commits/rolls back
-- Now safe to update — we hold the lock
UPDATE products
SET stock = stock - 1
WHERE product_id = 'P01';
COMMIT; -- lock released here
Timeline with FOR UPDATE:
Time│ T1 (buys item) T2 (buys same item)
────┼────────────────────────────────────────────────────────
1 │ SELECT stock FOR UPDATE → 10
│ (acquires exclusive lock on P01)
2 │ SELECT stock FOR UPDATE
│ → BLOCKED (T1 holds lock)
3 │ UPDATE stock = 9
│ COMMIT → lock released
4 │ → UNBLOCKED
│ SELECT stock → NOW sees 9
│ UPDATE stock = 8
│ COMMIT ✅
5 │ stock = 8 ✅ correct!
SELECT FOR SHARE — Shared Locking
-- Multiple transactions can read simultaneously
-- but NO transaction can write while any reader holds share lock
SELECT * FROM orders
WHERE order_id = 1001
FOR SHARE; -- shared lock, multiple readers OK, writers blocked
Optimistic Locking — Without DB Locks
-- Add version column to table
ALTER TABLE products ADD COLUMN version INT DEFAULT 0;
-- Read with version
SELECT stock, version FROM products WHERE product_id = 'P01';
-- Gets: stock=10, version=5
-- Update ONLY if version hasn't changed
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE product_id = 'P01' AND version = 5;
-- If another transaction already updated it, version is now 6
-- This UPDATE affects 0 rows → application detects conflict → retry
-- Check rows affected
-- 0 rows affected → conflict detected → retry the transaction
-- 1 row affected → success ✅
Pessimistic locking: assume conflicts will happen → lock upfront
Best for: high conflict rate, short transactions
Optimistic locking: assume conflicts are rare → detect at commit time
Best for: low conflict rate, long read periods
Used by: Hibernate (@Version), JPA optimistic locking
Savepoints — Partial Rollback
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1, 500);
SAVEPOINT sp_order_created; -- mark a point
INSERT INTO payments (order_id, amount) VALUES (LAST_INSERT_ID(), 500);
-- payment fails due to card decline
ROLLBACK TO SAVEPOINT sp_order_created; -- undo only the payment
-- order is still there (not rolled back)
INSERT INTO payments (order_id, status) VALUES (LAST_INSERT_ID(), 'PENDING');
-- record payment as pending for retry
COMMIT; -- order created, payment marked pending
Deadlocks — When Transactions Block Each Other
Deadlock: T1 waits for T2 to release a lock
T2 waits for T1 to release a lock
Neither can proceed → circular wait
Timeline:
Time│ T1 (transfer A→B) T2 (transfer B→A)
────┼──────────────────────────────────────────────
1 │ LOCK account A (id=1)
2 │ LOCK account B (id=2)
3 │ Waiting for LOCK on B
4 │ Waiting for LOCK on A
5 │ ← DEADLOCK ← T1 waits for T2, T2 waits for T1
DB detects deadlock:
→ picks a victim (usually the transaction that has done less work)
→ rolls back the victim
→ other transaction proceeds
MySQL: detects deadlocks automatically, rolls back smaller transaction
Error: "Deadlock found when trying to get lock; try restarting transaction"
Application must handle: catch deadlock error → retry transaction
Preventing Deadlocks
1. Always acquire locks in the SAME ORDER
Both T1 and T2 should lock account with LOWER id first
T1: lock id=1 then id=2
T2: lock id=1 then id=2 (not 2 then 1)
→ one will wait for the other → no circular wait
2. Keep transactions SHORT
Shorter transactions hold locks for less time → less chance of conflict
3. Use LOWER isolation levels where safe
READ COMMITTED with careful locking avoids many deadlock scenarios
4. Index your WHERE clauses properly
Unindexed updates take table-level locks instead of row-level locks
→ much higher deadlock chance
Distributed Transactions
When operations span multiple databases or services, standard ACID transactions don't work — each database is independent.
Two-Phase Commit (2PC)
Coordinator asks all participants: "Are you ready to commit?"
Phase 1 — Prepare:
Coordinator → "Prepare" → DB1, DB2, DB3
Each DB: locks resources, writes to WAL, replies "YES" or "NO"
Phase 2 — Commit or Abort:
If ALL said YES → Coordinator → "Commit" → all DBs commit
If ANY said NO → Coordinator → "Abort" → all DBs rollback
Problems:
- If coordinator crashes after phase 1 but before phase 2
→ all participants are stuck holding locks indefinitely → blocking
- Network partitions → participants don't hear from coordinator → stuck
- Not used at large scale (too slow, too fragile)
- Supported by: XA transactions in Java/MySQL/PostgreSQL
Saga Pattern (Preferred at Scale)
Long-running business transaction split into a sequence of local transactions.
Each local transaction commits immediately.
If a later step fails → compensating transactions undo earlier steps.
Example: Place order (3 services, 3 databases)
Step 1: OrderService → create order → COMMIT (local)
Step 2: InventoryService → reserve item → COMMIT (local)
Step 3: PaymentService → charge card → FAILS (card declined)
Compensating transactions (run in reverse):
Step 2 undo: InventoryService → release reservation → COMMIT
Step 1 undo: OrderService → cancel order → COMMIT
Implementations:
Choreography: each service publishes events, others react
(loose coupling, harder to track)
Orchestration: a central saga orchestrator sends commands to services
(easier to track, single point of coordination)
Used by: Uber, Airbnb, Netflix, Flipkart for distributed order flows
System Design Context
Choosing Isolation Level Per Use Case
User registration (INSERT new user):
→ READ COMMITTED is fine, no concurrent reads needed
User login (check credentials):
→ READ COMMITTED — always read latest committed password hash
Bank transfer (debit + credit):
→ SERIALIZABLE or REPEATABLE READ + SELECT FOR UPDATE
→ Never use READ COMMITTED — risk of lost update
Inventory deduction (flash sale):
→ SELECT FOR UPDATE in REPEATABLE READ
→ Pessimistic locking because conflict rate is extremely high
Product price display:
→ READ COMMITTED — slight staleness is acceptable
→ Even READ UNCOMMITTED is fine (rough price display)
Monthly financial report:
→ REPEATABLE READ — need consistent snapshot across all queries
Order status update (user cancels):
→ READ COMMITTED + optimistic locking (low conflict rate)
Real-World Isolation at Scale
Instagram (Meta):
Mostly READ COMMITTED on MySQL
For critical writes (follower counts, post creation): explicit FOR UPDATE
Flipkart:
Order placement: REPEATABLE READ + inventory FOR UPDATE
Catalog reads: READ COMMITTED
Reports: REPEATABLE READ (snapshot consistency)
Razorpay/PayU:
Every payment transaction: SERIALIZABLE or REPEATABLE READ + explicit locks
Zero tolerance for lost updates or dirty reads on money movement
How This Connects
- ACID Properties — this note is the "I" of ACID expanded; the transaction lifecycle and atomicity/durability parts belong to that note.
- Concurrency Control - Locks & MVCC — how the levels are implemented: shared/exclusive locks and gap locks for the pessimistic side, multi-version snapshots for the reader side.
SELECT ... FOR UPDATElives there too. - Relational Model - Tables, Keys, Relationships — an unindexed
WHEREin anUPDATEescalates to a table lock, which is where most surprise deadlocks come from. - Module 11 — Async & Event-Driven — Saga (choreography vs orchestration), 2PC/XA, and the Outbox pattern are the cross-service answers when one transaction can't span the databases.
- Redis as a Caching Engine — a distributed lock (
SET key val NX EX) is the cross-process version ofFOR UPDATEwhen the contended resource isn't a single DB row.
In the Interview
When to say it. Any "two users do X at the same time" scenario — booking the last seat, buying the last unit, double-clicking "pay", incrementing a counter — and any "which isolation level?" question.
The ~20-second version:
"Isolation levels decide which concurrency anomalies a transaction can see. READ UNCOMMITTED allows dirty reads — basically no isolation. READ COMMITTED, Postgres's default, stops dirty reads but the same query can return different rows within one transaction. REPEATABLE READ, MySQL InnoDB's default, gives you a snapshot fixed at transaction start, so repeated reads are stable. SERIALIZABLE makes transactions behave as if they ran one at a time, stopping phantoms too, at the cost of throughput. For a bank transfer or a flash-sale decrement I don't rely on the level alone — I take an explicit
SELECT ... FOR UPDATErow lock, or use an optimistic version column and retry. Across services I use a Saga with compensating actions, not 2PC."
Follow-ups to expect:
- "Non-repeatable read vs phantom read?" — Non-repeatable = an existing row's value changed between two reads. Phantom = the set of rows matching a
WHEREchanged (a row was inserted/deleted). - "Pessimistic vs optimistic locking?" — Pessimistic (
FOR UPDATE) locks upfront; best when conflicts are frequent and transactions short. Optimistic (version column, 0 rows updated → retry) assumes conflicts are rare; best for long think-time and low contention. - "How do you prevent deadlocks?" — Acquire locks in a consistent order (e.g. lower id first), keep transactions short, index the
WHEREclauses, and catch the deadlock error and retry. - "REPEATABLE READ still allows lost updates — how do you handle that?" —
FOR UPDATE, an atomicSET x = x - 1, or an optimisticWHERE version = :v. - "Why not just always SERIALIZABLE?" — Lock contention and serialization-failure retries collapse throughput under load; you scope it to the critical section instead.
Mistake to avoid: saying "I'd use SERIALIZABLE" and stopping. Show you know the cheaper, more common answer — READ COMMITTED for the bulk of traffic plus a targeted FOR UPDATE (or optimistic retry) on the exact rows that matter.
Quick Revision — 7 Lines
- Dirty read = reading uncommitted data. Non-repeatable = same row differs between reads. Phantom = new rows appear in range query.
- READ UNCOMMITTED → no protection. READ COMMITTED → no dirty reads. REPEATABLE READ → consistent snapshot. SERIALIZABLE → full isolation.
- MySQL default: REPEATABLE READ. PostgreSQL default: READ COMMITTED.
- SELECT FOR UPDATE = exclusive row lock (pessimistic). Version column = optimistic locking.
- Deadlock = circular wait. DB auto-detects and rolls back one victim. Prevent by locking in consistent order.
- 2PC = distributed atomicity protocol, fragile. Saga = preferred at scale with compensating transactions.
- Choose isolation by risk: money/inventory → SERIALIZABLE or explicit locks. Catalog/display → READ COMMITTED.
Quick-revision quiz
Question 1 of 18
Fill the anomaly table: at READ COMMITTED, which of dirty read / non-repeatable read / phantom read can still occur?