Skip to content
All articles

ACID Internals: Locking, MVCC, and Two-Phase Commit

Dean Jain

Dean Jain

Senior Staff Software Engineer · Enterprise AI, Data & Cloud Architect

· 6 min read

DatabasesACIDConcurrency
---
config:
  theme: neutral
  fontSize: 16
---
flowchart LR
    A["Atomicity<br/>all or nothing"]:::server
    C["Consistency<br/>valid  valid"]:::obs
    I["Isolation<br/>concurrent = sequential"]:::gate
    D["Durability<br/>survives a crash"]:::good
    classDef server fill:#FFFFFF,stroke:#333333,stroke-width:1px,color:#111111
    classDef obs fill:#F0F0F0,stroke:#333333,stroke-width:1px,color:#111111
    classDef gate fill:#DAE8FC,stroke:#333333,stroke-width:1px,color:#111111
    classDef good fill:#DCDCDC,stroke:#333333,stroke-width:1px,color:#111111

Figure 1: The four ACID guarantees the promises a transactional database makes despite errors, crashes, and concurrency.

Everyone can recite ACID Atomicity, Consistency, Isolation, Durability. Far fewer can say how a database actually delivers those guarantees when ten transactions hit the same row at once and the power cuts out mid-write. That “how” is the interesting part, and it’s worth understanding because it explains the trade-offs behind every database choice you make why one is faster, why another scales out, why NoSQL gave some of this up (and is now walking it back). Here’s what’s happening under the hood.

Summary

  • ACID = four guarantees of transaction validity despite errors and crashes: atomic (all-or-nothing), consistent (valid→valid), isolated (concurrent behaves like sequential), durable (survives failure).
  • Durability & atomicity come from logging. Write-ahead logging (WAL) records changes before applying them, so a crash can be recovered; shadow paging is the alternative.
  • Isolation is the hard one. It’s enforced by locking (pessimistic or optimistic) or by MVCC multiple versions so readers never block writers.
  • MVCC is why modern databases feel fast readers see a prior consistent version instead of waiting for a lock.
  • Distributed atomicity needs 2PC. Two-Phase Commit coordinates a commit/abort decision across nodes. NoSQL’s BASE trades consistency for availability but the industry is drifting back toward ACID.

1. The four guarantees

ACID is a set of properties that keep data valid despite errors, power failures, and concurrent access:

  • Atomicity a transaction is a single unit: it succeeds completely or fails completely. If any statement fails, the whole thing rolls back and the database is untouched.
  • Consistency a transaction can only move the database from one valid state to another, honoring all constraints, cascades, and triggers. (Note: it guarantees validity, not correctness a transaction can be valid and still wrong.)
  • Isolation concurrent transactions leave the database in the same state as if they’d run sequentially. This is the main goal of concurrency control and the hardest property to deliver.
  • Durability once committed, a transaction stays committed even through a crash, because its effects are recorded in non-volatile storage.

The first and last (atomicity, durability) are about surviving failure; the middle two (consistency, isolation) are about surviving concurrency. Those are two different engineering problems, solved by two different families of mechanisms.

2. Surviving failure: write-ahead logging

How does a database guarantee atomicity and durability when a crash can strike mid-write? Two classic techniques, both built on a simple idea: write the intent before the change.

  • Write-ahead logging (WAL) copy the original (unchanged) data to a log before modifying the database. If a crash interrupts the transaction, the database replays or rolls back from the log to reach a consistent state. This is why “the WAL” shows up everywhere (it’s also what log-based CDC reads).
  • Shadow paging apply updates to a partial copy of the database, then atomically activate the new copy on commit.

In both cases, the log (or shadow) is the source of recovery truth: the commit isn’t real until it’s durably recorded, and a crash before that point leaves no trace. That’s atomicity and durability, delivered by ordering record what you’re about to do, then do it.

3. Surviving concurrency: locking vs MVCC

Isolation is the hard property, because it’s where transactions actually collide. Two strategies dominate:

---
config:
  theme: neutral
  fontSize: 16
---
flowchart TB
    subgraph LOCK["Locking"]
        direction TB
        PES["Pessimistic: take an exclusive lock<br/>(others wait)"]:::warn
        OPT["Optimistic: check at commit if<br/>someone else changed it"]:::warn
    end
    subgraph MV["MVCC"]
        direction TB
        M1["Readers get the prior version"]:::good
        M2["Writers don't block readers<br/>readers don't block writers"]:::good
    end
    classDef warn fill:#FFFFFF,stroke:#333333,stroke-width:1px,color:#111111
    classDef good fill:#F0F0F0,stroke:#333333,stroke-width:1px,color:#111111

Figure 2: Two ways to deliver isolation locking makes transactions wait; MVCC keeps multiple versions so reads and writes don't block each other.

Locking. A transaction marks the data it touches so the DBMS won’t let others modify it until the first finishes. Two flavors:

  • Pessimistic locking takes an exclusive lock up front no one else can modify the record until you’re done. Safe, but it blocks other transactions, and non-trivial transactions need many locks (real overhead).
  • Optimistic locking takes no lock; it checks at commit time whether someone else changed the record, and fails if so. Great for low-contention workloads the cost is that conflicts surface as commit-time failures, and the retry logic is yours to write (and to bound, so contention doesn’t turn into a retry storm).

A subtle but important rule: the lock must be acquired before processing data even data you only read, not just data you modify otherwise isolation breaks.

MVCC (Multi-Version Concurrency Control) sidesteps the blocking entirely. Instead of locking, the database keeps multiple versions of a row: each reading transaction is handed the prior, unmodified version of data that a writer is currently changing. The payoff is huge writers don’t block readers, and readers don’t block writers. This is why MVCC databases (PostgreSQL, and most modern engines) feel fast under mixed read/write load: your analytics query doesn’t grind to a halt waiting for someone’s update to commit.

4. Atomicity across machines: Two-Phase Commit

Everything so far assumes one database. The moment a transaction spans multiple nodes (a distributed atomic transaction), you need a protocol to make them all commit or all abort together. That’s Two-Phase Commit (2PC):

---
config:
  theme: neutral
  fontSize: 16
---
flowchart LR
    CO["Coordinator"]:::gov -->|"1. prepare?"|P1["Node A"]:::server
    CO -->|"1. prepare?"|P2["Node B"]:::server
    P1 -->|"vote: yes/no"|CO
    P2 -->|"vote: yes/no"|CO
    CO -->|"2. commit (all yes) /<br/>abort (any no)"|P1
    CO --> P2
    classDef gov fill:#FFFFFF,stroke:#333333,stroke-width:1px,color:#111111
    classDef server fill:#F0F0F0,stroke:#333333,stroke-width:1px,color:#111111

Figure 3: Two-Phase Commit a coordinator asks every node to prepare, then commits only if all vote yes; otherwise everyone aborts.

Phase 1 (prepare): the coordinator asks every participant “can you commit?” and each votes yes (and durably prepares) or no. Phase 2 (commit/abort): if all voted yes, the coordinator tells everyone to commit; if any voted no, everyone aborts. It’s how distributed systems preserve atomicity though it’s also a source of latency and blocking if the coordinator fails, which is why distributed transactions are used sparingly.

ACID vs BASE. Many NoSQL systems chose a different bargain BASE: Basically Available, Soft state, Eventual consistency trading strong consistency for availability and scale. The state can drift and only eventually converges. That was the right call for some web-scale workloads, but the pendulum has swung back: Google Spanner offers strong consistency at global scale, and MongoDB added multi-document ACID transactions. The lesson is that ACID vs BASE was never a permanent fork it’s a tunable trade-off, and the industry has been reclaiming ACID guarantees as the engineering matured.

Why it matters: “ACID” is easy to memorize and easy to take for granted until you’re debugging a deadlock, choosing an isolation level, or deciding whether you can afford a distributed transaction. Knowing that durability rides on a write-ahead log, that isolation is a choice between locking and MVCC, and that cross-node atomicity costs you 2PC turns those decisions from guesswork into reasoning. The guarantees aren’t magic; they’re mechanisms with costs and picking a database is really picking which costs you’ll pay.

References