ACID Transactions

Question 1

A bank transfer debits Account A successfully but the server crashes before crediting Account B. $50 disappears. Which ACID property, if enforced, would have prevented this?

Accepted Answer

Atomicity — the two writes must succeed or fail together as a single unit — Atomicity means a transaction is all-or-nothing. If the debit succeeds but the credit does not, the database rolls back the debit too, leaving both accounts unchanged. Half-committed states never persist.

Question 2

What does "rollback" mean in the context of a database transaction?

Accepted Answer

The database undoes all writes from the current transaction as if it never started — A rollback reverses every write the transaction made — inserts, updates, deletes — restoring the database to the exact state it was in before the transaction began. It is the mechanism that makes Atomicity possible.

Question 3

The Consistency guarantee in ACID means that a transaction must:

Accepted Answer

Leave the database in a state that satisfies all defined rules and constraints — ACID Consistency means the database moves from one valid state to another — every constraint, foreign-key rule, and check is satisfied after the commit. This is different from the "C" in CAP theorem, which is about distributed replicas agreeing on a single value.

Question 4

Which ACID property specifically addresses the situation where two transactions run concurrently and one silently overwrites the other's change — a "lost update"?

Accepted Answer

Isolation — Isolation prevents transactions from seeing each other's intermediate states. Without isolation, two transactions can both read the same value, each compute an update, and each write back — the last write wins and the first update is lost. Serializable isolation is the strongest level and prevents this entirely.

Question 5

Durability guarantees that once a transaction is committed, its changes will survive even if:

Accepted Answer

The server loses power immediately after sending the "committed" reply — Durability means committed data is written to persistent storage (e.g., via a write-ahead log) before the success reply is sent to the client. Even a hard power cut right after the reply cannot erase a durable commit — the data is replayed from the log on restart.

Question 6

A write-ahead log (WAL) is a key implementation technique for which ACID property?

Accepted Answer

Durability — it ensures committed records survive crashes — The WAL records every intended change to durable storage before applying it to the main data files. If the server crashes after commit, the WAL entries are replayed on restart, recovering the committed state. It is the primary mechanism for Durability in most relational databases (PostgreSQL, MySQL InnoDB, SQLite).

Question 7

A transfer of $300 is attempted from Account A, which only holds $200. The database rejects the transaction. Which ACID property is being enforced?

Accepted Answer

Consistency — the "no negative balance" rule would be violated — Consistency ensures the database stays in a valid state according to its defined rules. A balance below zero violates the constraint, so the transaction is rejected before any write is committed — the database never enters the invalid state.

Question 8

What is a "lost update" anomaly?

Accepted Answer

Two transactions both read the same value, each compute an update, and the second write silently overwrites the first — A lost update happens when two concurrent transactions each read the same row, compute an updated value independently, and each write it back — the later write overwrites the earlier one. For example, if A=$200 and both transactions try to debit $50 at the same time, both read 200, both write 150, and one debit is lost. Isolation prevents this.

Question 9

Which statement best describes the difference between "ACID Consistency" and "CAP Consistency"?

Accepted Answer

ACID Consistency ensures all data constraints are satisfied after each commit on a single node; CAP Consistency means every replica returns the latest committed write — ACID Consistency means a single database node leaves the data in a valid state (all constraints satisfied) after each transaction. CAP Consistency (linearizability) means every read across a distributed system returns the most recently committed write regardless of which replica is queried. They are unrelated concepts that unfortunately share a word.

Question 10

Which isolation level provides the strongest guarantee against anomalies like dirty reads, non-repeatable reads, and phantom reads?

Accepted Answer

Serializable — Serializable is the highest SQL isolation level. Transactions execute as if they were run one after another in some serial order, eliminating all standard read anomalies. The trade-off is lower concurrency and throughput compared to weaker levels like Read Committed.

Which letter of ACID?

Test your knowledge

A bank transfer debits Account A successfully but the server crashes before crediting Account B. $50 disappears. Which ACID property, if enforced, would have prevented this?

The short version

Interview Questions

References & Further Reading

Related topics

Love this visualization?