Transactions — ACID properties (each one in detail): Question bank

Before we start, a one-line refresher on the four words so every reveal makes sense:

Atomicity = all-or-nothing on a single transaction (survives crashes).
Consistency = the database stays inside its declared rules (constraints, invariants).
Isolation = concurrent transactions don't corrupt each other (survives other users).
Durability = once committed, effects survive future crashes.

The four properties at a glance

Before the traps, one picture of which enemy each letter defends against — Atomicity and Durability face the crash axis (time/failure), Isolation faces the concurrency axis (other users now), and Consistency is the rule-checker that sits across the top.

The isolation anomalies (define these before you use them)

Every "Isolation" trap below leans on five named anomalies. Read them here first — the figure is a mini timeline of two transactions T1 and T2 for each one, so you can see the exact interleaving that goes wrong.

Formula Which level stops which anomaly

Level	Dirty	Non-repeatable	Phantom	Lost update	Write skew
Read Uncommitted	✗	✗	✗	✗	✗
Read Committed	✓	✗	✗	✗	✗
Repeatable Read	✓	✓	✗	✓	✗
Serializable	✓	✓	✓	✓	✓

(Snapshot-based MVCC engines block lost update at Repeatable Read but famously still allow write skew until Serializable — see the last edge-case items.)

True or false — justify

The database moves from one valid state to another only if the transaction is atomic

False — atomicity guarantees completeness (all writes or none), but a fully-completed transaction can still try to leave a rule-breaking state; Consistency is the separate promise that rejects it.

Atomicity guarantees the transaction leaves no negative balance

False — atomicity only stops partial writes; a complete transaction can still produce a negative balance, which is the CHECK constraint / Consistency's job to reject.

Isolation and Atomicity both prevent you from seeing "half" a transaction

True, but for different reasons: Atomicity hides half-states caused by your own crash, Isolation hides half-states caused by another concurrent transaction.

Durability means the row is on disk the instant the UPDATE statement runs

False — durability is guaranteed only at COMMIT, and even then via the log first (see Write-Ahead Logging (WAL)); pre-commit writes are explicitly not durable.

ACID's "C" and CAP's "C" mean the same thing

False — ACID-C is single-node rule keeping; CAP-C is multi-node read agreement (all replicas see the same value). Same letter, unrelated ideas.

Serializable isolation is always the correct choice because it is safest

False — it is safest but costs the most locking/aborts; engineers pick the weakest level that still preserves their invariants (see Isolation Levels).

A ROLLBACK is a rare error path that well-written apps never hit

False — rollback is a normal, designed outcome (constraint violation, deadlock victim, app exception); atomicity relies on it working correctly every time.

The redo log and the undo log serve the same purpose

False — the undo log reverses uncommitted work (Atomicity), the redo log re-applies committed work lost from data pages (Durability). See Undo and Redo Logs.

If a transaction commits, its data pages are definitely on disk

False — only the redo log is guaranteed flushed at commit; the data page may still be in RAM and gets written lazily, then recovered by replaying the log if a crash intervenes.

Read Committed prevents a value from changing between two reads inside one transaction

False — that's a non-repeatable read, which Read Committed still allows; you need Repeatable Read to forbid it.

Under Serializable, transactions literally run one at a time

False — they run concurrently but the result is guaranteed to equal some serial order; actual execution can be highly parallel (e.g. via MVCC or Locking and 2PL).

Snapshot isolation (Repeatable Read on MVCC) is enough to prevent write skew

False — snapshot isolation stops dirty/non-repeatable reads and lost updates, but write skew survives because each transaction writes a different row and never sees the other's write; only Serializable closes it.

Spot the error

"We got atomicity, so we don't need constraints" — what's wrong?

Atomicity only ensures all-or-nothing execution; it never checks correctness of values. A fully-atomic transaction can still commit a rule-breaking state unless Consistency/constraints reject it.

"COMMIT returned OK, then the machine lost power, so the payment is gone" — where's the flaw?

If COMMIT truly returned success, the redo log was already fsynced to durable storage, so WAL recovery replays it — the payment survives. Durability is precisely this promise.

"We use Read Uncommitted for speed; occasional dirty reads are harmless" — when does this bite?

A dirty read can read a value the other transaction later rolls back — so you act on data that never existed, e.g. approving a withdrawal against a balance that was never really zero.

"Isolation guarantees my report sees a consistent snapshot even at Read Committed" — correct?

Only for single-statement reads. Across multiple statements Read Committed permits non-repeatable and phantom reads, so a multi-query report can still see a moving target.

"To make a transfer atomic, just wrap both UPDATEs in BEGIN…COMMIT and trust the app" — is that enough?

BEGIN…COMMIT gives atomicity, but the app must still call ROLLBACK on error; without that (or a crash) the engine's undo log is what actually erases partial work — the guarantee lives in the DB, not the app's happy path.

"We picked Serializable everywhere, so throughput problems are impossible" — critique it

Serializable maximizes correctness but also maximizes contention (more locks/aborts via 2PL or serialization failures), which typically lowers throughput — the opposite of a free win.

"Durability is delivered by writing data pages before returning from COMMIT" — fix it

WAL flushes the log before COMMIT returns, not the data page. Log-first is cheaper (sequential write) and sufficient, because recovery can rebuild pages from it.

"Two clerks both read stock = 5, both sell 1, both write stock = 4 — no anomaly since both wrote 4" — spot it

That is a lost update: two decrements should give 3, but the second write overwrote the first's result, silently losing one sale. Weaker isolation without row locking permits exactly this.

Why questions

Why write the undo record before changing the row, not after?

Because a crash between "change row" and "write undo" would leave modified data with no way to reverse it; logging first guarantees a reversal always exists.

Why is durability guaranteed only at commit and not for every write?

Because before commit the transaction may still roll back, so its writes must not be permanent; flushing on every write would also be prohibitively slow.

Why does the log get flushed to disk but data pages can wait?

The log is a compact, append-only sequential write (fast fsync), and it contains everything needed to rebuild pages, so pages can be written lazily without risking committed data.

Why do we distinguish Atomicity from Isolation if both hide partial states?

They defend against different enemies: Atomicity vs failure over time (crash), Isolation vs parallelism (other transactions running now) — the mechanisms and edge cases differ entirely.

Why can't the database enforce the invariant "total money is constant"?

That rule spans rows in a way no single declared constraint captures; the DB enforces only what you declare (CHECK, FK, PK), so such application invariants are the app's responsibility.

Why does stronger isolation reduce throughput?

Preventing more anomalies requires holding locks longer or aborting more conflicting transactions (via Concurrency Control), so transactions wait or retry more — less work per second.

Why does a dirty read specifically require the other transaction to be uncommitted?

Because "dirty" means the value might still be undone; once the writer commits, the value is real and reading it is a legitimate committed read, not a dirty one.

Why is Consistency partly the application's job and partly the database's?

The DB enforces declarable rules (constraints, keys, triggers); business invariants it cannot express must be maintained by the application logic inside the transaction.

Why does write skew slip past snapshot isolation even though nothing was overwritten?

Because each transaction reads an old snapshot and writes a different row, so there is no read-write conflict on the same item — yet the combined effect violates a cross-row invariant neither saw coming.

Edge cases

An empty transaction: BEGIN then immediately COMMIT with no operations — is that valid?

Yes — a valid but trivial transaction; it moves the database from a valid state to the same valid state, satisfying all four properties vacuously.

A transaction reads data but writes nothing, then commits — does durability apply?

There's nothing to make durable, so durability is trivially satisfied; the interesting guarantees for a read-only transaction are Isolation (which snapshot it saw) not Durability.

A crash happens exactly at COMMIT — how does recovery decide the outcome?

If the commit record made it to the durable log, recovery treats it as committed and replays redo; if not, it's treated as uncommitted and undone — the durable log record is the single deciding fact.

A single-statement UPDATE touching 1000 rows — is it atomic?

Yes — every statement runs inside a transaction (implicit if not explicit), so all 1000 row changes take effect together or none do.

Two transactions each wait for a lock the other holds (deadlock) — which ACID property is at stake and what happens?

Isolation via 2PL; the engine aborts one as the victim and rolls it back (Atomicity guarantees clean reversal), and the app is expected to retry.

A transaction commits, then a later transaction rolls back — can the rollback undo the first one?

No — rollback only reverses its own uncommitted work; a previously committed transaction is permanent (Durability) and untouchable by later rollbacks.

Phantom read at Repeatable Read — is it prevented?

No — Repeatable Read stops values from changing but new rows matching a range query can still appear; only Serializable forbids phantoms.

Classic write-skew case: two on-call doctors each check "at least one other is on duty" and both go off duty — which level stops it?

Each read the other as present (from their snapshot) and wrote only their own row, so snapshot isolation allows both — leaving zero doctors on duty. Only Serializable (true serialization) prevents this write skew.

Lost update: two edits to the same counter under Read Committed — is it prevented?

No — Read Committed allows lost updates; you need Repeatable Read (or explicit SELECT ... FOR UPDATE row locking) so the second writer detects the concurrent change.

Recall Two-line self-check before you leave

Which property fights crashes and which fights other users? ::: Atomicity (and Durability) fight crashes/failure; Isolation fights concurrent users; Consistency fights rule violations. Where does the "durability at commit" promise physically live? ::: In the fsynced WAL redo log flushed before COMMIT returns OK. Which anomaly hides from snapshot isolation and needs Serializable? ::: Write skew — different rows, no direct conflict, but a broken cross-row invariant.