Behind ACID: How Databases Actually Implement the 4 Guarantees

Every software engineer learns the ACID acronym during introductory database coursework. Yet in production engineering interviews and high-concurrency systems design, theoretical definitions are insufficient.

To design resilient architectures, you must understand the concrete low-level engines responsible for enforcing each guarantee.

---

1. Atomicity: The Undo Log

Atomicity ensures that a transaction executes as a single, indivisible unit of work: either all operations succeed, or none do.

Implementation:

The database storage engine maintains an append-only Undo Log in memory and disk:

1. Before mutating any data page in the buffer pool, the engine writes an undo record containing the pre-image (original values).
2. If an unhandled exception occurs or the client issues ROLLBACK, the recovery manager traverses the undo log backward, writing the original values back into data pages.
3. If the server loses power mid-transaction, startup recovery parses the undo log to clean up incomplete transactions.

---

2. Consistency: Declarative Constraints

Consistency ensures the database transitions exclusively between legally valid states that satisfy all integrity rules.

Implementation:

Enforced by relational constraints:

• Primary Key / Unique Indexes: Prevents duplicate identity.
• Foreign Key Constraints: Prevents orphaned relational records.
• CHECK Constraints: Enforces domain boundaries (e.g., balance >= 0).

---

3. Isolation: Concurrency Control (2PL vs MVCC)

Isolation guarantees that concurrent execution yields the exact same state as if transactions ran sequentially.

Implementation:

• Two-Phase Locking (2PL): Locks data items during execution (Growing phase) and releases them only at termination (Shrinking phase).
• Multi-Version Concurrency Control (MVCC): Storage engines (PostgreSQL, InnoDB) tag tuples with transaction IDs (xmin/xmax). Readers read snapshot versions without blocking concurrent writers.

---

4. Durability: Write-Ahead Logging (WAL)

Durability guarantees that once a transaction receives a successful COMMIT response, its updates will survive subsequent hardware crashes or power cuts.

Implementation:

Writing 16KB dirty data pages randomly across a disk array is slow. Instead, modern engines enforce the WAL Protocol:

• Changes are written sequentially to a fast, append-only Redo Log.
• When COMMIT is executed, the engine forces an fsync() of the log buffer to non-volatile storage.
• On reboot after a crash, the ARIES Redo Phase replays logged changes to rebuild physical state.

Explore interactive database transaction blueprints on Binary Dose.