System Design Data

ACID Properties

Picture a checkout where the debit clears on the ledger, then the app dies before the order row is written. The customer has paid for nothing. Money movement only counts when both sides finish together: never a charge without an order, or an order without a charge, even if the process crashes mid-flight.

A is that bundle of related reads and writes that must succeed or fail as one. (Atomicity, Consistency, Isolation, Durability) is the checklist databases follow so the bundle never leaves data in an impossible state. This page walks the checkout through each letter, shows the machinery underneath (a write-ahead log and row versions), and ends with what changes when the data no longer fits in one database.

Checkout decision path

Open one unit of work so every payment step succeeds or fails together.

Debit and credit either both stick or both undo; no half transfer survives.

Concurrent checkouts must not sell the same last unit of inventory twice.

After COMMIT returns, a crash or power loss must not erase the payment.

What actually happens

The application begins a transaction and executes related reads and writes under the chosen isolation level. The engine checks constraints as required and uses locks or version checks to handle conflicts. A write-ahead log records changes before affected data pages must be persisted; with durable commit settings, the required log record is flushed before success is acknowledged. Exact snapshot, constraint-checking, and error-abort behavior varies by engine, so use the transaction API to commit or roll back the intended unit explicitly.

That single path is how , , , and show up in practice: not as four flashcards but as one story. No half writes, only valid states, concurrent checkouts that do not corrupt each other, and committed work that survives a crash.

At scale, engines lean on (keeping several versions of a row so readers and writers rarely block each other), optimistic concurrency, and replication while still aiming for the same logical guarantees. Across services, teams keep the money-changing write small and ACID, then use a (a chain of local transactions with compensating undo steps) for emails, webhooks, and other side effects.

What must never become false. After COMMIT returns there is no durable half-transaction: either every constrained effect remains or none does. Crash recovery from the log must restore every acknowledged commit, and business invariants (balances, uniqueness, capacity) must still hold. Everything below is machinery for keeping that sentence true.

How to choose ACID strength

Knowing what promises is not the same as knowing how much of it a given write needs. Three questions settle that for almost any feature.

1. Does a partial write lose money or trust? Payments, inventory, seats, and uniqueness usually need a real database with a strong enough (how sealed one transaction is from others running at the same time).

2. Is the work inside one database? If so, prefer native ACID there; it is the cheapest correctness you will ever buy. If ownership spans services, no single engine can guarantee the whole write, and the cross-service section near the end of this page compares the two options.

3. Can side effects leave the transaction? Emails, analytics, and cache invalidation often belong in an outbox (a table in the same database that records events to publish after the commit) rather than inside the same lock hold.

Use full ACID for

Money movement, credits, refunds, and ledgers, because a half-applied transfer is a lost or invented balance.
Inventory, seat booking, quota allocation, and uniqueness, where two winners for one slot is a visible failure.
Admin and permission changes, because a partial write can open a security or compliance gap.

Relax or move outside the transaction for

Email sends, webhooks, cache invalidation, and analytics events, which can retry later without corrupting truth.
Long-running business workflows that cross service ownership, since no single database can lock them.
Derived views that can be rebuilt from a durable source of truth whenever they drift.

Simple rule of thumb

Keep transactions short and choose isolation from the business invariant before deployment. Read Committed is appropriate for many operations, but it does not automatically protect multi-row decisions or read-modify-write logic. Use constraints, conditional updates, targeted locks, or Serializable where the known concurrency pattern requires them, and test concurrent executions. Publish slower side effects through an outbox so they do not unnecessarily extend lock lifetimes.

Transaction lifecycle

The three questions above assume you know what a does step by step. Here is the path every committed transaction walks, from BEGIN to the moment other transactions can see its result.

BEGIN transaction

Read snapshot

Apply writes in memory

Check constraints and app invariants

Constraints pass?

Yes

Append and flush WAL

COMMIT ack

New version visible to later txns

Fails

ROLLBACK all changes

Notice where invariants get checked on that path. Database-enforced invariants (foreign keys, uniqueness, CHECK constraints, row locks, and isolation levels) run inside the engine on every commit, which is far stronger than hoping every service writes perfect application code.

Application-enforced invariants cover what the database cannot see: cross-service workflows, business approvals, and external side effects. Those usually need idempotency keys (a unique request ID so a retried call is recognised and not applied twice), outbox events, sagas, and periodic reconciliation. Each of those returns later on this page.

The four properties

ACID properties answer different questions. Atomicity asks whether partial transaction effects can become committed; consistency asks whether declared invariants remain valid; isolation constrains interference between concurrent work; durability defines what survives after success. A database cannot infer an unstated business rule. Represent the rule with constraints, conditional updates, locks, or a transaction design that the selected isolation level can enforce.

The lifecycle showed the path; the four letters name what that path guarantees. For each one, ask "what breaks if I lose this?", because that is how interview follow-ups usually go.

A: Atomicity

All operations in a succeed, or none are applied. That is : no half-finished transfers, ever.

sql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- If ANY fails → ROLLBACK both
COMMIT;

The usual implementation is the : after a crash the database replays finished transactions and rolls back unfinished ones. The durability section below walks that path step by step.

C: Consistency

means the database only moves between valid states: constraints (foreign keys, CHECK, UNIQUE, NOT NULL) hold after every commit.

Not the same word as CAP (Consistency, Availability, Partition tolerance) consistency, which means linearizability: every replica showing the latest write.
Enforced by schema rules plus the application invariants you encode in SQL (Structured Query Language) or app code.
Example: a balance can never go negative once the column carries CHECK (balance >= 0).

I: Isolation

means concurrent transactions should not corrupt each other's view of the world. How strictly that holds is controlled by the you choose (the dedicated transactions page covers each anomaly in depth).

Weak isolation allows dirty reads (seeing uncommitted data), lost updates, and phantom rows appearing mid-query.
Strongest common target is Serializable, which behaves as if transactions ran one after another.
The cheat sheet later on this page compares each level and its throughput cost so you choose deliberately.

D: Durability

means that after COMMIT the database promises the change survives crashes and power loss; replication then adds machine-level durability on top.

The is flushed to stable storage before COMMIT returns, so the promise is on disk first.
fsync pushes operating-system buffers to the physical disk (or the cloud-disk equivalent).
Standby replicas extend durability across hosts, so one dead machine does not lose committed work.

WAL: how durability usually works

Write-ahead logging records enough information to recover changes before affected data pages need to reach their final disk locations. A durable commit record lets recovery distinguish completed work from work that did not commit. Group commit can share one flush across several transactions, improving throughput without requiring each transaction to update all its data pages synchronously. The actual acknowledgment guarantee depends on the engine’s flush and replication settings.

Both and leaned on the above; this is the mechanism in detail. The idea is simple: write down what you intend to do, in order, in one sequential file, before touching the real data pages.

Typical commit path

The transaction appends its records to the WAL, a sequential write that disks and SSDs handle quickly.
The WAL is flushed to durable storage with fsync, so the record survives a power loss.
COMMIT is acknowledged to the client only after that flush has returned.
Data pages in memory are updated; a later checkpoint (a periodic bulk write) pushes the dirty pages to the data files.

Crash recovery replays the WAL from the last checkpoint. That is why commits survive power loss without rewriting entire tables on every transaction.

A crash at each step of that path

Steps two and three are the ones that matter. Whether the log reached disk, and whether the client heard back, decides what the restart looks like.

Crash point	Outcome
Before WAL flush	Transaction lost on restart
After WAL flush, before client ack	Committed; client may retry
After client ack, before page flush	Durability from log replay

Why log first instead of writing the table directly? Because of the cost gap between sequential and random IO.

Sequential

WAL writes

Random

Data pages

Large gap

Sequential vs random IO cost

~16MB

Example WAL segment

Running many transactions at once: MVCC, locks, and hot rows

The log protects one transaction against a crash; protects many transactions against each other. Engines reach for three tools: row versions so readers rarely wait, locks or version checks so writers do not clobber each other, and a redesign of the data when one row becomes the bottleneck.

MVCC: isolation without blocking every reader

keeps older row versions around so readers use a snapshot while writers add new versions. That is the classic throughput win for OLTP (online transaction processing: many small, concurrent reads and writes). Two engine families implement it differently.

Append-only MVCC (tuple versions + vacuum)

Each row records which transaction created it (xmin) and which one retired it (xmax).
An UPDATE inserts a brand-new row version and marks the old version as superseded.
A transaction sees only the versions that were already committed when its snapshot was taken.
Background cleanup (vacuum) reclaims old versions once no active transaction can still need them.

Undo-log MVCC

The current row is updated in place, and prior values move into an undo log chain.
Readers rebuild an older version from the undo log whenever their snapshot requires it.
Undo entries are purged once no active transaction can still read them.
Very long transactions swell the undo history, so watch monitoring on history length.

Pessimistic vs optimistic concurrency

Pessimistic locking reserves conflicting access before a decision is made, which is useful when collisions are common but can create waiting and deadlocks. Optimistic concurrency reads a version and applies the update only if that version is unchanged, which avoids waiting when collisions are rare but requires retrying failed attempts. Both approaches must put the check and state change inside the same authoritative boundary.

Versions let readers avoid writers, but two writers on the same row still need a referee. You can either lock the row up front (pessimistic) or write freely and check that nobody else changed it first (optimistic).

Pessimistic (locking)

sql

SELECT * FROM inventory
WHERE product_id = 42
FOR UPDATE;  -- lock row
UPDATE inventory SET qty = qty - 1;
COMMIT;

When: hot rows such as inventory or seats. FOR UPDATE locks the row so access is serialised up front: fewer surprise conflicts, more waiting.

Optimistic (compare-and-set)

sql

SELECT qty, version FROM inventory
WHERE product_id = 42;
UPDATE inventory
SET qty = qty - 1, version = version + 1
WHERE product_id = 42 AND version = 5;
-- 0 rows updated → someone else won → retry

When: conflicts are rare. The version column is a compare-and-set check: no lock is held while the user thinks, and a retry handles the occasional overlap.

Hot-row contention math

Either choice hits the same wall when one row is popular. If a single inventory row takes 5 ms to lock, update, and commit, serialized throughput is roughly 1 / 0.005 = 200 writes/sec before queueing starts. Read replicas do not raise this write limit, because the primary still owns the row lock.

Row lockAlways correct, but writers queue once write QPS (queries per second) passes the lock ceiling.

Version retryCheap when conflicts are rare; on a hot row the retries pile into a retry storm.

Inventory bucketsSplit one product's capacity across many rows, then reconcile the totals afterwards.

Reservation ledgerAppend each reservation as its own row and compute remaining capacity from the sum.

Isolation levels: the anomaly cheat sheet and what each costs

The concurrency tools above are how an engine delivers an ; the level is what you actually choose in code. Full anomaly stories live on Transactions & Isolation Levels. Here is the cheat sheet most teams memorise first. A dirty read means seeing another transaction's uncommitted write; non-repeatable means the same row reads differently twice inside one transaction; a phantom means new rows appear when a range query is repeated.

Level	Dirty read	Non-repeatable	Phantom	Performance
Read Uncommitted	Yes	Yes	Yes	Fastest
Read Committed	No	Yes	Yes	Good
Repeatable Read	No	No	Yes*	Moderate
Serializable	No	No	No	Slowest

* Some engines use gap or range locks (locks on the empty space between index keys) so phantoms are often avoided in practice at Repeatable Read.

What engines and teams pick by default

Common SQL defaultRead Committed: enough for most OLTP once you understand that a row can read differently twice.

Locking SQL defaultRepeatable Read with gap-locking semantics, so phantoms are mostly blocked as well.

Payments-focusedOften Serializable (or stricter) because correctness beats throughput on the money path.

Consensus SQLSerializable only: the product is correctness-first and offers no weaker level.

Rough performance shape (not a benchmark)

The cheat sheet says "slower"; this is roughly how much slower, so the choice is a trade rather than a guess.

Metric	Read Committed	Repeatable Read	Serializable
Relative throughput	Baseline (~100%)	Often ~90–95%	Often ~70–85%
Conflict handling	Fewer aborts	Some retries under write races	Higher abort / retry rate possible
Correctness ceiling	Good if app closes gaps	Stronger snapshot guarantees	Highest (idealized serial execution)

SSI (Serializable Snapshot Isolation) can deliver Serializable with lower overhead than old-school two-phase locking (hold every lock until commit) in some engines; still profile your own workload.

Failure timeline: a crash around COMMIT

A lost commit response creates an uncertain outcome for the client. The database may have committed durably before the connection broke, so blindly rerunning a transfer can duplicate it. Give the business operation a stable identifier and record it with the transaction, allowing a retry to discover the prior result. Transaction atomicity prevents half a transfer inside the database; idempotency prevents two complete transfers for one client intent.

Everything on this page can be checked with one question: what happens if the database crashes at this exact step? The timeline runs the checkout transfer through each crash point, and the matrix underneath generalises it, including the replication case.

t=0      BEGIN; debit A; credit B; constraints OK
t=1      Append WAL records; fsync pending
t=2      Crash BEFORE WAL flush → restart: txn gone (atomicity preserved)
t=3      Alternate: WAL flushed, client not yet acked → restart: committed; client may retry
t=4      Client retries without idempotency → duplicate side effects outside the DB
Outcome  Durability is WAL-before-ack; safe retries need idempotency keys

Crash and recovery matrix

Crash point	Recovery outcome	ACID property
Before log flush	Transaction is rolled back; client should retry safely	Atomicity
After log flush, before client ack	Recovery may commit it; client needs idempotency key to avoid double action	Durability
After client ack, before data page flush	WAL (Write-Ahead Log) replays committed changes	Durability
During replica shipping	Primary is correct; async replica may lag and must catch up before promotion	Durability plus replication policy

The one row the database cannot fix for you is t=4. Once the client hears nothing and retries, only an idempotency key (a unique request ID the server remembers) keeps the second attempt from charging twice or sending two emails.

Beyond one database: BASE stores and cross-service transactions

Every section so far assumed the data lives in one engine. Two things change when it does not: some stores deliberately relax , and some writes span services that each own their own database.

ACID vs BASE

BASE (Basically Available, Soft state, Eventually consistent) names the distributed-store mindset: favour availability and convergence instead of single-node ACID semantics everywhere.

Topic	ACID	BASE
Primary goal	Strong per-transaction guarantees	Availability + eventual convergence
Under contention	May block or abort	Keeps serving; resolves conflicts later
State shape	Consistent after each commit	Soft: replicas catch up over time
Common fits	Payments, inventory, bookings	Feeds, analytics caches, counters
Examples	Relational SQL engines	Wide-column, key-value, and cache stores

Both in one product: ACID on the money path, BASE-style stores for everything users tolerate being slightly stale.

When one write spans machines

A transaction in the order database cannot roll back an email already sent or a payment accepted by an external provider. An outbox keeps the order change and delivery intent atomic locally, then a worker performs the external action with retry and deduplication. A saga coordinates several local commits with explicit compensating actions, such as releasing a reservation. Compensation is another business operation and can fail; it is not an invisible database rollback.

Even with ACID stores on both sides, a write that touches two databases cannot use one COMMIT. There are two ways to fake it: coordinate the commits, or accept a sequence of local commits and undo on failure.

Two-phase commit ()

Prepare: the coordinator asks each participant whether it can commit its part.
If all vote yes, the coordinator sends commit to everyone and all parts become durable together.
If any vote no, the coordinator sends abort and every participant rolls back its part.

Catch: if the coordinator dies mid-flight, participants may block, holding locks until it recovers. That is why microservices often avoid classic 2PC.

(microservices)

Each service runs a local ACID transaction against the database it owns.
Downstream steps chain via messages or an orchestrator, so no cross-service lock is ever held.
A failure triggers compensating actions (refunds, cancellations) that undo earlier steps.

This trades atomic cross-service commits for clarity and resilience. Pair it with idempotent handlers, because messages can arrive twice.

Try it: walk a transfer through the WAL

Press each stage to move one $10 transfer. Crash at different points to see why durability on the log—not on buffer pages—defines what survives restart and what the client should retry.

Account A

$500

Write-ahead log

Empty for this transfer.

Account B

$200

1. Debit in memory
2. Append WAL record
3. Flush WAL to disk
4. Credit in memory
5. Commit marker + ack client

Step through a $10 transfer to see where atomicity and durability attach.

Atomicity is a commit decision. Until commit, observers should see neither half of the transfer.

Durability follows the flushed log. Acknowledging the client before flush trades latency for data loss if the machine dies seconds later.

Wrap-up

You have seen the promise, the log that keeps it, the concurrency tools, and what changes across machines. Use these exercises to check the ideas stuck, then follow the references for the primary sources.

Exercises

Map crash-before-WAL-flush vs crash-after-WAL-flush-before-client-ack for a payment transfer. What must the client send on retry?
Hot inventory row: lock hold time 5ms. Roughly how many exclusive updates/sec can one row sustain? Propose a redesign that keeps ACID without that ceiling.
Split "charge card + send email + update cache" into an ACID core plus saga/outbox. Which steps stay inside the transaction and why?

References

Gray & Reuter, Transaction Processing: Concepts and Techniques: the foundational text on ACID and recovery.
PostgreSQL: Write-Ahead Logging describes the relationship between log records, data pages, and crash recovery.
Hector Garcia-Molina et al. and the ARIES recovery literature: the classic papers behind log-based recovery.

What to remember

Pick isolation with the rule of thumb above rather than by habit, and profile before escalating. Prefer SELECT … FOR UPDATE on contested rows and optimistic versioning everywhere else. Understand so long transactions do not accidentally pin old versions. Across services, prefer plus idempotency over fragile distributed .

Practice and further reading

Use these exercises to connect the lesson to a design decision. State your assumptions, trace what happens to one request, and explain the rule that must remain true even during a failure. That rule is the design's correctness invariant.

Focus for this lesson: Connect atomicity, consistency, isolation, and durability to WAL, constraints, locks/MVCC, commit acknowledgement, and crash recovery.

Explain the decision

How does WAL support atomicity and durability?

Read the reasoning

Log records reach durable storage before data pages; recovery redoes committed effects and discards or undoes incomplete transactions.

Apply it to a production scenario

The database crashes after COMMIT reaches WAL but before dirty pages flush. Is the transaction lost?

Read one approach

No if commit acknowledgement followed durable WAL policy; crash recovery replays the committed log records.

Work through the numbers

Group commit flushes every 5 ms. What is the theoretical maximum flush groups per second?

Read the worked calculation

About 200 groups/s, while each group may contain many transactions; latency and storage throughput still bound results.

Reason through a failure

What must remain true after any crash point in a transfer transaction?

Read the recovery reasoning

Both debit and credit are visible or neither is, and the ledger constraints still hold after recovery.

Sources and further study

Continue with Types of Databases.