System Design AI

ACID Transactions: Follow-Up Questions Interviewers Ask (And How to Answer)

When a candidate brings up ACID transactions, interviewers instantly shift from "what" to "how". They want to see whether you understand the cost of strong consistency, how it interacts with latency, and what you would do when the guarantees become a bottleneck. The follow-up questions also reveal whether you can articulate trade-offs and propose realistic mitigations.

A good answer shows you can quantify the impact, reference a concrete system, and suggest a pragmatic path forward. Below are the nine probes interviewers typically ask, ordered from the most straightforward to the hardest, each paired with a concise, example-rich response.

1. What does each letter in ACID stand for?

Answer

"ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity means a transaction either fully commits or fully rolls back – no partial state. Consistency guarantees that all defined invariants hold before and after the transaction. Isolation ensures concurrent transactions don’t interfere; the classic level is serializable, though many systems settle for snapshot isolation. Durability means once a transaction is committed, it survives crashes, typically via write-ahead logging to persistent storage."

2. Which isolation level does your system use by default?

Answer

"In the design I’m describing, we use snapshot isolation by default because it gives a good balance between performance and correctness. For example, Amazon Aurora implements snapshot isolation on top of a distributed log, allowing reads to see a consistent snapshot without locking writers. This reduces contention compared to strict serializable isolation, which would require locking or validation that adds latency on the order of tens of milliseconds per transaction."

3. How does the system guarantee durability?

Answer

"Durability is achieved with a write-ahead log (WAL) that is flushed to durable storage before the transaction is considered committed. In practice we replicate the log to three nodes in three availability zones and wait for a majority to acknowledge; this means a single zone outage won’t lose committed data. The replication latency is roughly 5 ms to another zone, and because the replicas are written in parallel rather than one after another, the commit latency is about 5 ms plus the local disk sync time, which is usually 2–3 ms on SSDs."

4. What is the performance impact of ACID on latency?

Answer

"Strong ACID guarantees add write latency because each commit must be durably recorded and often replicated. In a typical relational DB with synchronous replication to two replicas, a single write can take 8–12 ms versus 1–2 ms for a non-transactional write. The exact number depends on network distance; for a cross-region setup the latency can climb to 30 ms. This is why many high-throughput services relax isolation for read-heavy workloads."

5. How do you handle long-running transactions?

Answer

"Long-running transactions are split into smaller units of work using the saga pattern. Each saga step is its own ACID transaction, and a compensating action undoes the step if later steps fail. For instance, in an e-commerce checkout we might first reserve inventory, then charge payment, then create the order. If payment fails, the inventory reservation is rolled back via a compensating “release inventory” operation. This keeps lock hold times short and avoids blocking other users."

6. What happens if a node fails during a transaction?

Answer

"If a node crashes after it has written to its local WAL but before the replication quorum is reached, the transaction will be aborted and the client receives a retryable error. The coordinator detects the missing ack, rolls back the partial state, and retries on a healthy replica. This is similar to how Google Spanner’s Paxos-based commit protocol works – it only marks a transaction committed once a majority of replicas have persisted the commit record."

7. Can you scale ACID transactions horizontally?

Answer

"Horizontal scaling is possible by sharding data and limiting each transaction to a single shard. For example, a user-profile service might shard by user ID, ensuring that all updates for a given user land on the same shard, thus preserving ACID within that shard. Cross-shard transactions require a two-phase commit (2PC) and incur additional round-trips; in practice we keep them rare because 2PC adds at least one network RTT per participant, which can be 5–10 ms per shard."

8. How would you trade off consistency for availability in a geo-distributed system?

Answer

"We would adopt a tunable consistency model like Spanner’s true-time timestamps, allowing reads to be served from the nearest replica with a bounded staleness window, say 100 ms. Writes still go through a quorum to guarantee durability, but reads can be served locally, improving latency from 30 ms (cross-region) to under 5 ms. The trade-off is that a client might read slightly stale data, but the staleness is bounded and acceptable for many use-cases such as social feeds."

9. What distinguishes a senior-level answer from a staff-level answer on this topic?

Answer

"A senior-level answer explains the mechanics, cites concrete numbers, and suggests practical mitigations like sharding or saga decomposition. A staff-level answer goes further: it evaluates the impact on system architecture, proposes alternative consistency models, and sketches a migration path from strict ACID to a hybrid approach. It also anticipates operational concerns—monitoring commit latency, handling split-brain scenarios, and evolving the data model to reduce cross-shard transactions."

How to practice this

  1. Study the worked design of a relational database like PostgreSQL in the System Design AI library; click each component to read its notes.
  2. On your canvas, draw a simple service that uses transactions, then annotate the points where latency, replication, and sharding decisions occur.
  3. Role-play the interview: have a friend ask the nine questions above and answer them aloud, using concrete numbers from your design.

Questions people ask

Why do interviewers focus on isolation levels?

Isolation determines how concurrent writes interact. Different levels (read-committed, snapshot, serializable) have distinct performance and correctness implications, so interviewers gauge your awareness of those trade-offs.

Is it ever okay to drop durability?

Only for cache-only data or when the business can tolerate loss, such as analytics pipelines that replay from source logs. Dropping durability for primary data is rarely acceptable.

How many replicas are typical for durability?

A common setup replicates to three nodes in three availability zones; this provides a quorum of two for commits while tolerating the loss of a single node or zone.

Can ACID be achieved without a central coordinator?

Distributed ACID often relies on consensus protocols (Paxos, Raft) that act as coordinators. Fully decentralized ACID is impractical because the commit order must be agreed upon globally.