ACID Transactions

When you choose a relational database in a system design interview, you often cite ACID guarantees as the reason. That is usually the right instinct — but many candidates treat ACID as a magic shield that makes every consistency problem disappear. It does not. ACID describes what a single database engine promises for a transaction scoped to what that engine can see. Understanding each letter precisely — and where ACID stops — is what separates a credible database discussion from hand-waving.

This chapter covers atomicity, consistency, isolation, and durability; how engines implement them; what breaks when you span services; and the interview questions that follow.

What Is ACID?

ACID is an acronym for four properties that relational and many other databases guarantee for transactions — grouped units of work that either fully succeed or fully fail:

These properties apply inside one database session (or a coordinated distributed transaction across participants the engine controls). They do not automatically span two microservices, a database plus a message broker, or a cache and a primary store. That boundary matters constantly in interviews.

Atomicity

Atomicity means all statements in a transaction commit together, or none of them do. On failure, the database rolls back partial work so you never leave a half-done transaction visible as committed.

Consider transferring money between accounts:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If the debit succeeds but the credit fails — disk full, constraint violation, process crash — atomicity ensures neither change persists. The account holder does not lose $100 into the void. The interview phrase is all-or-nothing.

Atomicity is about grouping, not about business correctness across services. A saga that calls PaymentService then InventoryService is not atomic in the ACID sense unless you wrap both in a single coordinated transaction (rare and costly at scale).

Consistency (Database Sense)

Consistency in ACID means the database moves only between valid states according to rules the database can enforce — constraints, triggers, foreign keys, CHECK, NOT NULL, and similar declarations you define in your data model.

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL REFERENCES users(id),
  total DECIMAL(10,2) CHECK (total >= 0)
);

If a transaction tries to insert an order with a negative total or a nonexistent user_id, the database rejects it. The C in ACID is about integrity constraints inside the engine, not about every business rule in your domain.

ACID Consistency Is Not CAP Consistency

This is the most common vocabulary collision in system design interviews:

They share a word but describe different guarantees. A fully ACID PostgreSQL instance on one node can still participate in an AP-style replicated cluster where reads from followers are stale. See CAP & Consistency Tradeoffs for the distributed side.

Consistency in ACID also does not mean “the business is always correct.” Rules like “inventory count must match warehouse physical stock” or “payment captured implies order confirmed” often span tables, services, or external systems the database cannot see. Those invariants are your job in application logic and distributed patterns — sagas, outbox, idempotency keys — covered later in this chapter.

Isolation

Isolation means concurrent transactions do not see each other’s partial work in ways forbidden by the chosen isolation level. Without isolation, interleaved reads and writes from multiple sessions produce anomalies — results that could not occur if transactions ran one at a time.

The trade-off is fundamental: stronger isolation prevents more anomalies but typically increases blocking, deadlocks, serialization failures, and reduced throughput. There is no free lunch.

Read Phenomena

These are the classic anomalies isolation levels aim to prevent:

Not every isolation level prevents every phenomenon. Engine behavior also varies — always verify against your specific database documentation.

Isolation Levels (SQL Standard and Practice)

The SQL standard defines four levels. Real engines implement them differently; PostgreSQL’s “repeatable read” behaves more strictly than the standard minimum in some cases.

*PostgreSQL’s repeatable read also prevents phantoms for standard queries in many cases via snapshot isolation.

Read committed is the default for most production OLTP workloads. Each statement sees a snapshot of committed data as of the statement start. Good balance of concurrency and sanity.

Repeatable read holds a snapshot for the entire transaction. Good when you read a balance, do work, and read again expecting the same value — but watch for write skew on multi-row invariants.

Serializable is the gold standard when correctness dominates throughput. PostgreSQL uses Serializable Snapshot Isolation (SSI); you may get serialization failures (40001) that require retry. Design idempotent retries when using this level.

Example: Why Level Choice Matters

Imagine a flight with one seat left. Two users click “Book” at the same time:

• Under read committed, both transactions can read seats_available = 1, both proceed to insert a booking, and you double-book unless a unique constraint or explicit lock catches it.
• Under serializable (or with SELECT ... FOR UPDATE on the seat row), one transaction wins; the other blocks or fails with a serialization error.

The anomaly you are preventing drives the level — not prestige.

In interviews, naming the level is not enough. Tie it to the anomaly you are preventing: “For seat inventory, I need serializable or explicit row locks because read committed allows two transactions to both see a seat as available.”

Isolation and Indexing

Isolation interacts with performance. Range scans under serializable isolation may acquire predicate locks that block concurrent inserts. Heavy write contention on hot rows — popular product inventory, counter columns — causes lock waits regardless of level. Indexes that matter reduce scan scope and lock duration by letting the engine target exact rows instead of scanning entire tables.

Durability

Durability means after COMMIT, committed data survives crash or power loss according to the policies of the database engine — write-ahead logging, fsync behavior, and replication configuration.

When PostgreSQL returns success on COMMIT, the transaction’s changes are recorded in the WAL (write-ahead log) and flushed to disk per the configured durability settings (synchronous_commit, full_page_writes, etc.). A crash immediately after commit should not lose committed work on that node.

Durability vs Disaster Recovery

Durability on one node does not automatically mean surviving datacenter loss. That requires replication, backups, and disaster-recovery design with explicit RPO (how much data you can lose) and RTO (how long recovery takes). A single-node PostgreSQL with nightly backups has durability on commit but might lose up to 24 hours of data if the datacenter burns down.

Engine defaults matter. Some configurations trade durability for speed (synchronous_commit = off batches WAL flushes). Know what your database actually promises before claiming “ACID” in an interview.

How the Database Delivers ACID

High-level mechanisms vary by engine, but the pattern is consistent:

Write-Ahead Logging (WAL)

Before modifying data pages on disk, the engine appends the change to a sequential WAL file and fsyncs it. On crash, the engine replays the WAL to restore committed state. This is how durability and atomicity connect: uncommitted transactions have no commit record in the WAL, so replay ignores their partial changes.

Locks

Pessimistic locking (SELECT ... FOR UPDATE, table-level locks) blocks other transactions from accessing rows until the lock holder commits. Strong guarantees, higher contention.

Shared locks allow concurrent reads but block writes. Exclusive locks block everything else. Deadlocks occur when two transactions wait on each other’s locks; the engine detects the cycle and aborts one transaction.

MVCC (Multi-Version Concurrency Control)

Most modern OLTP engines (PostgreSQL, InnoDB, Oracle) use MVCC instead of read locks. Each transaction sees a snapshot of the database as of a point in time. Readers do not block writers; writers do not block readers. Old row versions are kept until no transaction needs them, then vacuumed.

MVCC delivers read committed and repeatable read efficiently but does not alone prevent write skew — that is why serializable isolation or explicit locking is needed for certain invariants.

Undo and Redo

On failure mid-transaction, undo logs roll back in-memory and on-disk changes. On commit, redo logs (often part of WAL) ensure committed changes survive crash. Together with the transaction manager, this implements atomicity across multiple statements touching multiple pages.

Understanding these mechanisms helps you answer “what happens if the process crashes between these two UPDATEs?” and “why did we get a deadlock?” without guessing.

ACID vs What Your Application Assumes

ACID is powerful but scoped. Common interview mistakes come from over-extending it.

Single Database, Single Service

When one service owns one database and keeps all related writes in one transaction, ACID does the heavy lifting:

BEGIN;
  INSERT INTO orders (user_id, total) VALUES (1, 49.99);
  UPDATE inventory SET qty = qty - 1 WHERE sku = 'ABC';
COMMIT;

Foreign keys and checks enforce what the schema declares. This is the sweet spot for ACID.

Cross-Service “ACID”

ACID does not mean:

• “If I return HTTP 200, the payment is consistent with the warehouse.” That spans two databases or a database plus a message broker. You need distributed transactions (2PC — slow, fragile), outbox pattern, sagas, idempotency keys, or eventual consistency with compensation.

• “My microservice stack is ACID.” Usually only one database session per request is ACID end-to-end. Service A commits; Service B fails — A does not roll back automatically.

• “The cache and the database are always in sync.” Cache invalidation is not part of any database’s ACID guarantee.

In a microservices architecture, each service typically owns its datastore. Business invariants that span services — payment plus inventory, order plus notification — require explicit distributed design. Mention this when an interviewer asks about e-commerce checkout or ticket booking at scale.

Example: Checkout Across Services

A naive checkout flow:

1. Order service — INSERT INTO orders ... COMMIT (ACID ✓)
2. Payment service — charge card via external API (ACID ✗ across boundary)
3. Inventory service — UPDATE stock ... COMMIT (ACID ✓ locally)

If step 2 succeeds and step 3 fails, the customer is charged but stock is not decremented. No single database transaction spans all three. Options:

• Saga: on inventory failure, call payment refund (compensating transaction)
• Outbox: order service writes order + outbox event in one DB transaction; a worker publishes to inventory asynchronously
• Reserve-then-capture: hold inventory in step 1 inside one ACID transaction before charging

Each pattern trades latency, complexity, and consistency scope differently. The interview win is naming where ACID applies and what replaces it across the boundary.

Patterns When ACID Is Not Enough

These are not failures of ACID — they are acknowledgments that ACID’s scope ends at the database boundary. CAP trade-offs apply at the cluster layer; application patterns apply across services.

Common Misconceptions

1. Consistency in ACID means “business always correct.” It means integrity constraints the DB enforces. Cross-service rules remain application logic.

2. Stronger isolation is always better. It prevents more anomalies but costs locks, retries, and throughput. Match the level to the invariant, not the maximum.

3. Durability on one node means survive datacenter loss. That is replication and DR, not a single-engine default.

4. ACID and CAP consistency are the same. They are not — see the comparison table above and the CAP chapter.

5. NoSQL means no ACID. Many document and key-value stores offer transactional guarantees on single documents or limited scopes. The question is scope and isolation level, not the SQL label.

Interview Questions

Q1: What is atomicity?

Answer: All operations in a transaction succeed and persist on commit, or none of them do — the database rolls back partial work on failure. All-or-nothing.

Q2: What is the difference between consistency in ACID and “business consistency”?

Answer: ACID consistency means integrity constraints and rules enforced inside the database — foreign keys, checks, not-null. Business consistency (e.g. inventory plus payment agree across two services) requires application logic and possibly distributed patterns like sagas or outbox.

Q3: What trade-off exists around isolation?

Answer: Stronger isolation prevents more anomalies (dirty reads, phantoms, write skew) but typically increases blocking, deadlocks, serialization failures, and reduces concurrency. Choose the weakest level that protects the invariant you care about.

Q4: How does the database guarantee durability?

Answer: After commit, changes are written to a write-ahead log and flushed to disk per engine policy. On crash, the engine replays the WAL to restore committed transactions. Replication and backups address datacenter-level failure separately.

Q5: Does using PostgreSQL make my entire system ACID?

Answer: It makes transactions within that database ACID. Cross-service calls, caches, and async messaging are outside that scope unless you add explicit distributed patterns.

Summary

Single-database ACID is the right default for strong local invariants — financial ledger rows, inventory decrements, seat holds — especially when paired with thoughtful data modeling and indexing. When your design spans services or regions, say clearly where ACID ends and what pattern picks up the slack.