Transactions & ACID

Real operations are rarely a single write. “Transfer $100” is two writes — debit one account, credit another — and if the system crashes between them, money vanishes or appears from nowhere. A transaction is the database’s promise to treat a group of operations as one indivisible, correct unit: all of it happens, or none of it does. This is the bedrock of correctness in data systems.

What does a transaction buy us, and what does it cost? It buys you the right to not reason about every partial-failure and concurrent-access nightmare yourself — the database handles it. It costs performance (coordination isn’t free) and, once your data spans machines, it costs a great deal more.

ACID, defined

The guarantees go by the acronym ACID. Three of the four are about safety; one is about durability.

• Atomicity — all-or-nothing. If any part of the transaction fails, the whole thing rolls back as if it never happened. (The misleading “A” — it means abortability, not concurrency.)
• Consistency — the transaction moves the database from one valid state to another, respecting all declared rules (constraints, foreign keys). This C is really about your invariants, partly upheld by you; it’s the odd one out, arguably added to make the acronym pronounceable.
• Isolation — concurrent transactions don’t step on each other. Each runs as if it were the only one. This is the deep, expensive, interesting one.
• Durability — once committed, it survives crashes. The data is on stable storage (disk, often replicated) and won’t evaporate on a power loss.

Atomicity and durability are largely binary — you have them or you don’t. Isolation is a dial, and turning it costs performance, so databases offer levels.

Why isolation is a spectrum

Perfect isolation (serializability) means the result is as if transactions ran one-at-a-time in some order. It’s the strongest, simplest-to-reason-about guarantee — and the slowest, because it forces concurrent transactions to wait on each other. So databases offer weaker levels that allow more concurrency by permitting specific anomalies. To choose a level, you must know which anomalies it lets through.

The three classic anomalies

DIRTY READ          T1 writes X (uncommitted) → T2 reads that X → T1 rolls back.
                    T2 read a value that never officially existed.

NON-REPEATABLE READ T1 reads X = 5 → T2 commits X = 8 → T1 reads X = 8.
                    Same query, same transaction, two different answers.

PHANTOM READ        T1 reads "all orders > $100" → T2 inserts a new $200 order →
                    T1 re-runs the query and a NEW ROW appears that wasn't there.

The level/anomaly ladder

Isolation level	Dirty read	Non-repeatable	Phantom
Read Uncommitted	possible	possible	possible
Read Committed	prevented	possible	possible
Repeatable Read	prevented	prevented	possible*
Serializable	prevented	prevented	prevented

*Some engines (e.g. Postgres) prevent phantoms at Repeatable Read via snapshot isolation; the SQL standard does not require it. The standard is loose here, so behavior varies by database — always verify what your engine actually does.

Most databases default to Read Committed

Not Serializable. The default trades a weaker guarantee for much better throughput, on the bet that most applications don’t hit the anomalies it allows. That bet is usually fine — until it isn’t, and a race condition corrupts data under load. Knowing your default isolation level, and raising it for the transactions that need it, is a mark of an engineer who’s been burned once.

How isolation is enforced

Two broad strategies. Locking (pessimistic): grab a lock before touching a row; others wait. Safe but contention-prone. MVCC — Multi-Version Concurrency Control (optimistic): keep multiple versions so readers see a consistent snapshot without blocking writers, and detect conflicts at commit time. MVCC is why “readers don’t block writers” in Postgres and MySQL/InnoDB, and it’s the engine behind snapshot isolation.

Distributed transactions and two-phase commit

Everything above assumes one machine. Once a transaction must commit atomically across multiple nodes — two shards, two databases — you need them to agree: all commit or all abort, despite crashes and network gaps. The classic protocol is two-phase commit (2PC).

   PHASE 1 (prepare):  coordinator → all participants: "can you commit?"
                       each writes its changes durably, locks rows, replies YES/NO
   PHASE 2 (commit):   if ALL said YES → "commit!";  if any NO → "abort!"
                       participants finalize and release locks

It works, but the costs are steep and reveal why distributed transactions are avoided when possible:

• Blocking. If the coordinator crashes after participants vote YES but before sending the decision, participants are stuck — holding locks, unable to commit or abort, until it recovers. 2PC is not fault-tolerant against a coordinator failure.
• Latency & locks. Two network round-trips, with rows locked across the whole window. Throughput drops and contention rises.
• Coordinator is a single point of failure unless made highly available — which means real fault-tolerant agreement, i.e. Consensus (Raft/Paxos). Consensus is what 2PC should lean on to survive a coordinator crash, and it’s the deep machinery underneath robust distributed commits.

The pragmatic escape

Because distributed ACID is so costly, large systems often avoid it: keep data that must be transactional together on the same shard (so it’s a cheap single-node transaction), and for genuinely cross-service work, give up atomic commit in favor of sagas — a sequence of local transactions with compensating “undo” steps, accepting eventual consistency instead of paying the 2PC tax. Designing so you rarely need a distributed transaction is usually wiser than making distributed transactions fast.

Under the hood — write skew, the anomaly the ladder doesn’t name

The dirty / non-repeatable / phantom ladder isn’t the whole story. Snapshot isolation — what Postgres labels REPEATABLE READ, and what MVCC gives you cheaply — stops all three classic anomalies yet still permits a subtler one: write skew. Two transactions each read an overlapping set of rows, each decides its own write is fine based on what it read, and both commit — but together they violate an invariant neither would have broken alone.

   Rule: "at least one doctor must stay on call."  Two are on call: Alice, Bob.

   T1 (Alice): reads "2 on call" → safe to leave → writes Alice = off
   T2 (Bob):   reads "2 on call" → safe to leave → writes Bob   = off
   Both commit on their own snapshot.    Result: ZERO on call. Invariant broken.

Neither transaction saw the other’s write — they ran on separate snapshots — so no classic anomaly fired, yet the result is corrupt. This is exactly the gap that Serializable Snapshot Isolation (Postgres’s true SERIALIZABLE) closes, by tracking the read/write conflict and aborting one of the pair at commit. It’s the cleanest case for this page’s warning: know what your isolation level actually prevents, not what its name implies.

The thread

A transaction lets you treat many writes as one correct unit, so you don’t have to hand-code recovery from every partial failure or race. ACID names the guarantees: atomicity and durability you mostly have or don’t, while isolation is a dial you trade against throughput by choosing which anomalies you can tolerate. On one machine this is cheap and well-understood. Stretch it across machines and the cost explodes — 2PC buys atomic cross-node commit at the price of blocking, latency, and a fragile coordinator that ultimately needs consensus to be safe. The recurring wisdom: keep things that must be atomic together close together, and reach for distributed transactions only when you truly must.

The architect’s lens

A transaction is a guarantee you opt into (and pay for) — run it through the five questions, especially before stretching it across machines:

• Why does it exist? Because real operations are rarely a single write — “transfer $100” is a debit and a credit — and a crash between them makes money vanish or appear. A transaction promises a group of writes is one indivisible, correct unit: all of it, or none.
• What problem does it solve? It frees you from hand-coding recovery from every partial failure and every concurrent-access race. The database upholds atomicity, isolation, and durability so you don’t have to reason about every interleaving yourself.
• What are the trade-offs? Coordination costs performance, and isolation is a dial: Serializable is safest but slowest, most engines default to Read Committed (still permitting non-repeatable and phantom reads), and even snapshot isolation permits write skew. Across machines the cost explodes — 2PC buys atomic cross-node commit but blocks on a coordinator crash, holds locks across two round trips, and is a SPOF that ultimately needs consensus.
• When should I avoid it? Avoid distributed ACID wherever you can: keep data that must be atomic on the same shard (a cheap single-node transaction), or use sagas with compensating undo steps and accept eventual consistency. Lower the isolation level where the anomalies it permits are genuinely harmless.
• What breaks if I remove it? Partial failures and races corrupt the record of truth — half-applied transfers, invariants like “at least one doctor on call” silently broken under concurrency — and the burden of getting every interleaving right moves back onto your application code.

Check your understanding

1. Spell out ACID. Which letter is about concurrency, which is the “odd one out,” and why?
2. Define dirty read, non-repeatable read, and phantom read with a one-line example each.
3. Why is isolation a spectrum rather than on/off? What does Serializable guarantee that Read Committed doesn’t?
4. Contrast locking and MVCC as ways to enforce isolation. Why can MVCC let readers not block writers?
5. Walk through two-phase commit and name its three big costs. Why does a robust coordinator need consensus?

Show answers

1. Atomicity, Consistency, Isolation, Durability. Isolation is the one about concurrency (transactions not stepping on each other). Consistency is the “odd one out” — it’s really about your declared invariants, partly upheld by you, arguably added to make the acronym pronounceable.
2. Dirty read: T1 writes X uncommitted, T2 reads it, T1 rolls back — T2 read a value that never officially existed. Non-repeatable read: T1 reads X=5, T2 commits X=8, T1 reads X=8 — same query, two answers. Phantom read: T1 reads “all orders > $100,” T2 inserts a new $200 order, T1 re-runs and a new row appears.
3. Isolation is a spectrum because perfect isolation (serializability) forces concurrent transactions to wait on each other, so databases offer weaker levels that allow more concurrency by permitting specific anomalies. Serializable guarantees the result is as if transactions ran one-at-a-time; Read Committed still permits non-repeatable and phantom reads.
4. Locking (pessimistic) grabs a lock before touching a row so others wait — safe but contention-prone. MVCC (optimistic) keeps multiple versions so readers see a consistent snapshot and conflicts are detected at commit. MVCC lets readers not block writers because a reader sees an older committed version while a writer creates a new one — neither has to wait for the other.
5. 2PC: in prepare, the coordinator asks all participants “can you commit?”, each writes changes durably, locks rows, and votes YES/NO; in commit, if all said YES the coordinator says “commit,” otherwise “abort,” and participants finalize and release locks. Three big costs: blocking (a coordinator crash after YES votes leaves participants stuck holding locks), latency & locks (two round-trips with rows locked the whole window), and the coordinator is a single point of failure. A robust coordinator needs consensus (Raft/Paxos) to survive its own crash — real fault-tolerant agreement is what keeps the decision recoverable.