Transactions, ACID & Isolation Levels

ACID: the promise a transaction makes

A transaction groups multiple statements so they succeed or fail as a single unit (BEGIN ... COMMIT/ROLLBACK). The four guarantees it's supposed to provide, at interview depth:

• Atomicity — all statements in the transaction happen, or none do. A transfer that debits one account and credits another either does both or neither; a crash mid-transaction cannot leave the money debited from one side and not credited to the other.
• Consistency — a transaction takes the database from one valid state to another, never violating a declared constraint (foreign keys, NOT NULL, CHECK) even transiently, as far as any other observer can tell.
• Isolation — concurrent transactions behave, from each transaction's point of view, as if they ran one at a time, even though the engine may genuinely be interleaving their execution for performance. This is the guarantee with a dial, not a fixed on/off switch — see isolation levels below — and it's the one this subtopic spends the most time on, since it's the one most directly relevant to reasoning about query correctness.
• Durability — once a transaction commits, it survives a crash immediately afterward. Typically implemented via a write-ahead log flushed to disk before the commit is acknowledged.

Isolation levels: a dial, not a binary switch

Full isolation (every transaction behaving as if it ran completely alone, called Serializable) is the strongest guarantee but the most expensive to enforce, since it can require blocking or aborting concurrent transactions that would otherwise interfere. Real systems expose weaker, cheaper isolation levels as an explicit trade-off, each one permitting a specific named anomaly that the level above it prevents:

Isolation level	Dirty read	Non-repeatable read	Phantom read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible
Repeatable Read	Prevented	Prevented	Possible (mostly — engine-dependent)
Serializable	Prevented	Prevented	Prevented

• Dirty read: reading another transaction's uncommitted changes — if that transaction later rolls back, you've read data that never officially existed.
• Non-repeatable read: reading the same row twice within one transaction and getting two different values, because another transaction committed a change to that row in between your two reads.
• Phantom read: re-running the same filtering query twice within one transaction and getting a different set of rows, because another transaction inserted or deleted a row matching the filter in between.

PostgreSQL's default is Read Committed; MySQL's (InnoDB) default is Repeatable Read, and InnoDB's specific implementation of Repeatable Read happens to prevent most phantom reads too via MVCC snapshotting — a good concrete example of why "what does isolation level X actually guarantee" is genuinely engine-specific in the details, even though the four named levels are a portable standard concept.

MVCC, briefly: how modern engines achieve this without just locking everything

Most modern engines (PostgreSQL, MySQL/InnoDB, Oracle) implement isolation primarily through Multi-Version Concurrency Control: instead of making a reader wait for a writer to finish, the engine keeps multiple versions of a row around and gives each transaction a consistent snapshot to read from, based on when that transaction started. This is why readers in these engines typically don't block writers (and vice versa) under normal isolation levels — a follow-up question worth being ready for if you mention isolation levels at all, since "so does a SELECT ever block on a concurrent UPDATE" is a natural next question.

Why interviewers care

This is one of the few purely conceptual questions that directly predicts real production competence: an engineer who doesn't know that "Read Committed" permits non-repeatable reads will eventually write application logic that re-reads a value mid-transaction and silently assumes it hasn't changed, which is a genuine, recurring class of production bug. It's also a natural, low-effort follow-up question after any query-writing problem that touches an UPDATE or a multi-statement workflow.

Pitfalls and interview gotchas

• Treating "Isolation" as a fixed guarantee rather than a configurable level with named trade-offs.
• Confusing a dirty read with a non-repeatable read — the distinction is specifically about whether the data being read was ever committed at all.
• Assuming isolation levels behave identically across engines — the same named level (e.g., Repeatable Read) can prevent different sets of anomalies depending on the specific engine's implementation, as the MySQL/InnoDB phantom-read example above shows.
• Not knowing MVCC exists as the usual mechanism, and assuming isolation is achieved purely through blocking/locking — most modern engines specifically avoid that for read-heavy workloads.

Where this goes next

Indexing & Query Performance Basics (next) covers the last conceptual area this roadmap touches — just enough to reason about whether a given query can use an index and why, without a database-internals-level dive into how indexes are actually implemented.