System Design · Core

Transactions

ACID in one database, isolation levels you can explain, and how distributed work uses 2PC or sagas instead of pretending one big TX exists.

1. ACID

Letter	Meaning	Interview example
Atomicity	All or nothing	Transfer: debit A and credit B commit together or neither
Consistency	Invariants hold after commit	Balances never negative if constraint enforced
Isolation	Concurrent TXs do not step on each other wrongly	Two bookings cannot take the last seat
Durability	After commit, survives crash	WAL fsynced before ACK to client

“Consistency” here is DB invariant consistency, not CAP “C”. Say that out loud so you do not confuse the interviewer.

2. Isolation levels

Isolation is a spectrum. Stronger levels mean fewer anomalies and more locking / abort / MVCC cost.

Level	Dirty read	Non-repeatable	Phantom	Notes
Read Uncommitted	Possible	Possible	Possible	Rarely used
Read Committed	No	Possible	Possible	Postgres default
Repeatable Read	No	No	DB-dependent	InnoDB RR prevents many phantoms via next-key locks
Serializable	No	No	No	Strictest; SSI or heavy locking

Dirty read: T2 sees T1's uncommitted write Non-repeatable: T2 re-reads row; value changed by committed T1 Phantom: T2 re-runs range query; new rows appeared Write skew: two TXs each OK alone, together break invariant (classic SSI example)

Practical advice: use the default (often RC), bump isolation or use SELECT ... FOR UPDATE on the critical rows (inventory, wallet).

3. Single-node patterns

Pessimistic locking: lock rows early; simple, can reduce concurrency.
Optimistic: version column; update WHERE version=old; retry on conflict.
Idempotency keys: client retries must not double-charge.

4. Distributed transactions — Two-Phase Commit (2PC)

Coordinator Participants (DB A, DB B) |-- PREPARE ------------>| each votes YES (locked) or NO |<-- YES / NO -----------| |-- COMMIT or ABORT ---->| if all YES → commit; else abort

Pros: strong atomicity across resources when it works.
Cons: blocking if coordinator fails after PREPARE; high latency; poor for long / cross-service UX flows.
Reality: rare across microservices; more common inside a single XA-aware data plane.

5. Sagas

A saga is a sequence of local transactions. Each step has a compensating action if a later step fails. No global lock.

Happy: ReserveSeat → ChargeCard → ConfirmBooking Failure: ChargeCard fails → ReleaseSeat (compensate) Confirm fails → Refund + ReleaseSeat

Style	How	Trade-off
Choreography	Services emit events; others react	Loose coupling; harder to see full flow
Orchestration	Central orchestrator calls steps	Clear control; orchestrator is critical

Compensations must be idempotent and often cannot perfectly undo (email already sent).
Users may observe intermediate states — design UX for “pending.”
Prefer sagas for long-running business flows across services.

6. 2PC vs saga — pick one

	2PC	Saga
Atomicity	Strong	Eventual; compensations
Latency / availability	Worse under failure	Better; local commits
Best for	Short, tightly coupled resources	Cross-service business processes

7. Outbox pattern (related)

In one local TX: 1) update business tables 2) insert event into outbox table Then publisher drains outbox → message bus

Avoids “DB committed but Kafka publish lost.” Pair with idempotent consumers.

Quick revision

ACID: atomic, invariants, isolation, durable after commit.
Know dirty / non-repeatable / phantom; map to isolation levels.
RC is common default; lock or raise isolation for critical rows.
2PC: prepare then commit; blocking and latency-heavy.
Saga: local TX + compensations; choreography or orchestration.
Outbox keeps DB and messaging in sync for one service.
Distributed “exactly one global TX” is usually the wrong goal.