Skip to main content
CodeSparring

The Relational Model & ACID

Level 2: Level 2: Data Storage & Modelingmedium30 minacidtransactionsrelational

Name what each ACID guarantee protects against: atomic debit+credit, fsync'd WAL durability, and consistency as your constraint-enforced invariant.

Four guarantees, not one buzzword

ACID is four separate guarantees that people blur into one buzzword. The way to sound senior is to say what each one protects against with a concrete failure in mind.

Atomicity means a transaction is all-or-nothing. A money transfer is a debit on one row and a credit on another. If the process crashes, or a constraint fails, or the network drops between the two writes, atomicity guarantees the database rolls back to the state before the transaction started. Without it you get the classic bug: 100 dollars leaves account A and never arrives at account B because the second write failed. The invariant "total money is conserved" only holds if both writes commit together or neither does.

Check yourself
Your bank schema declares no CHECK constraints and no foreign keys. A transfer transaction runs on a fully ACID database. Does the C in ACID still guarantee that money is never created or destroyed?

Consistency in ACID is not a free property the database grants you. It means the database moves from one valid state to another valid state as defined by your constraints and your application logic. The database enforces the part you declared: CHECK (balance >= 0), NOT NULL, foreign keys, unique constraints. Everything else (a transfer must not create money) is an invariant your transaction plus isolation must uphold. Consistency is the outcome; atomicity, isolation, and your constraints are the mechanism.

Isolation means concurrent transactions do not corrupt each other. If two transfers touch the same account at once, isolation determines whether one sees the other's half-finished work. This is the hardest and most-probed guarantee, and the next lesson is entirely about its levels.

Durability means once the database returns "committed," that data survives a crash, a power loss, or a kill -9. The concrete mechanism: the change is written and fsync'd to the write-ahead log (WAL) on durable storage before the commit acknowledgment is sent. A commit that is only in a memory buffer is not durable; if the box loses power, it is gone. This is why a synchronous commit costs a disk fsync (often a few ms), and why "group commit" batches many transactions into one fsync to amortize it.

Interview nuance: When asked "is your write durable?" the strong answer names the WAL and fsync, and flags the tradeoff: synchronous_commit = off in Postgres returns faster but risks losing the last few hundred ms of commits on a crash. Money says on; a like counter can say off.

When is strict ACID worth its cost?

When a violated invariant means lost money, double-charged users, or corrupted balances, pay for it: a single-primary relational database (Postgres, MySQL InnoDB) with real transactions. When the invariant is soft (a view count, a feed ordering) you can relax to BASE (basically available, soft state, eventual consistency) and buy horizontal scale and availability instead.

BEGIN
  UPDATE accounts SET balance = balance - 100 WHERE id = A;  -- debit
  UPDATE accounts SET balance = balance + 100 WHERE id = B;  -- credit
COMMIT   -- atomic: both or neither; durable: fsync'd WAL before ack

Recap: ACID is four concrete guarantees; atomicity makes debit+credit all-or-nothing, durability means fsync'd to the WAL not just memory, and consistency is your invariant enforced by constraints plus isolation, not a free lunch.

Check yourself

You are about to design a money-transfer feature. Match each failure to the ACID guarantee that prevents it.

A crash between the debit and the credit leaves 100 dollars missing
Power loss one second after the client saw 'committed' erases the transfer
Two concurrent transfers read the same 100 dollar balance and both withdraw it
A constraint failure on the credit leaves the debit already applied

Apply

Your turn

The task this lesson builds to.

Design the schema and transaction boundaries for a bank money-transfer feature that must never lose or double-count funds under concurrent transfers.

Think about

  1. Why must debit + credit be a single atomic transaction?
  2. What does durability actually mean at commit time?
  3. When is strict ACID worth the cost versus relaxing to BASE?

Practice

Make it stick

A second problem on the same idea, so it survives past today.

Design the transaction and idempotency strategy for Stripe-style payment intents where the client retries aggressively on timeouts and network blips, at roughly 5,000 charge attempts per second, so no customer is ever charged twice for one intent.

Think about

  1. Why can the external card charge not live inside your database transaction?
  2. What database mechanism (not app-side logic) makes two concurrent retries converge on one charge?
  3. How does recovery reconcile a crash that happened after charging but before recording success?