Transactions & isolation
Core details
Transaction = atomic unit of work against the database. ACID maps to operations discipline:
| Property | Practical meaning |
|---|---|
| Atomicity | all statements commit or none—no half-applied business steps |
| Consistency | constraints + your app rules stay true—DB + domain invariants |
| Isolation | concurrent transactions do not produce forbidden interleavings—level chosen |
| Durability | after commit ack, data survives process crash—WAL / replication matter |
ACID vs BASE (when interviewers contrast them)
| ACID | BASE | |
|---|---|---|
| Stability | Strong per-transaction guarantees in one engine | Basically available, soft state, eventual consistency |
| Typical home | RDS OLTP cores you treat as system of record | Caches, globally replicated stores, async projections |
| Story | “Debit + credit must commit together in the ledger DB.” | “Social like-count can lag; repair or recompute later.” |
BASE is not “sloppy”—it’s explicit acceptance of temporary inconsistency with defined convergence.
Anomalies (name them crisply):
| Anomaly | What went wrong |
|---|---|
| Dirty read | read uncommitted data that may roll back |
| Non-repeatable read | same row reread in one txn returns different values |
| Phantom read | repeated range query sees new matching rows |
| Lost update | two writers overwrite each other’s changes |
Isolation levels (know the ladder + which anomaly each allows)
| Level | Dirty read | Non-repeatable | Phantom | Notes |
|---|---|---|---|---|
| Read uncommitted | ✓ possible | ✓ | ✓ | rare in production; debugging danger |
| Read committed | ✗ | ✓ | ✓ | default on many engines |
| Repeatable read | ✗ | ✗ | varies by engine | snapshot semantics often |
| Serializable | ✗ | ✗ | ✗ | strongest; retries, deadlocks |
Engine specifics differ (e.g. Postgres repeatable read vs MySQL history); in interviews: name anomalies you prevent per level, then say you’d verify vendor docs.
MVCC (multi-version concurrency control)
Idea: readers see a snapshot at txn start (or statement); writers create new row versions; old versions are garbage-collected later.
| Benefit | Cost / watch |
|---|---|
| Readers often don’t block writers | Vacuum / purge lag → bloat, long snapshots |
| Fewer reader–writer locks | xmin/xmax-style visibility rules in Postgres; undo logs in others |
| Good fit for read-heavy OLTP | Long txns hold snapshot and prevent reuse of old versions |
Interview line: “MVCC trades storage + cleanup for read isolation without locking the whole table—long transactions still hurt.”
Optimistic vs pessimistic (unchanged but quick)
Optimistic: version column—UPDATE … WHERE id = ? AND version = ?. Pessimistic: SELECT … FOR UPDATE for short critical sections.
Distributed transactions: 2PC vs saga
Two-phase commit (2PC)
Phases: Prepare (participants vote) → Commit/Abort (coordinator decides).
| Pro | Con |
|---|---|
| Atomic “all or nothing” across participants | Blocking if coordinator dies after prepare |
| Familiar in textbooks | Latency + availability hit; not the default microservice glue |
Staff: “2PC is fragile across many Node.js services over unreliable HTTP—I’d avoid it as the default integration pattern.”
Saga pattern (microservices default story)
Idea: sequence of local transactions each with a compensating action if later steps fail.
| Style | Mechanism | Trade |
|---|---|---|
| Choreography | services react to domain events | fewer moving parts; harder to trace |
| Orchestration | central orchestrator drives steps | clearer flow; orchestrator is SPoF unless hardened |
Pair with idempotency, outbox, and at-least-once messaging (idempotency).
Understanding
Long transactions hold locks or snapshots and hurt throughput; calling HTTP, queues, or heavy CPU inside a DB transaction is a staff-level trap. Pattern: short DB txn → commit → post-commit side effects (cache bust, outbox, async).
“Use SERIALIZABLE for everything” is usually wrong: prove which endpoints need strongest guarantees (inventory, balances) vs read-heavy paths on snapshot isolation.
The diagram below keeps the interview narrative concrete: define the short local transaction, choose isolation for the invariant, commit, and move external effects such as cache invalidation or events after durability is guaranteed.
Senior understanding
| Scenario | Narrative |
|---|---|
| Double-submit checkout | idempotency key + unique constraint + txn boundary |
| Phantom inventory | range locks or careful application-level reservation |
| Read replica “bugs” | stale read—not isolation—fix routing or versioning |
| Deadlock storm | lock ordering, smaller txns, timeout + retry with backoff |
| “Eventual is fine” | define SLA for convergence + user-visible staleness |
Diagram (local transaction boundary)
See also
Mark this page when you finish learning it.
Spotted something unclear or wrong on this page?