Demystifying ACID: Transactions as an Isolation & Recovery Abstraction

Suppose you are building a banking application handling money transfers.

User A wants to transfer $100 to User B. This single business action requires executing two separate SQL updates inside your database:

UPDATE accounts SET balance = balance - 100 WHERE user_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE user_id = 'B';

Now consider what happens if a hardware failure, power loss, or database crash occurs after the first SQL statement finishes executing, but before the second statement starts.

User A loses $100, but User B never receives it. $100 vanishes into thin air.

To prevent partial execution failures, data corruption, and concurrent access bugs, database engines provide the Transaction abstraction.

What a Transaction Is Under the Hood

A transaction is a bounded group of database operations executed as a single, atomic unit of work.

BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE user_id = 'A';
  UPDATE accounts SET balance = balance + 100 WHERE user_id = 'B';
COMMIT;

A transaction MUST conclude in one of two states:

Committed: All operations succeed, and changes become permanently visible.
Aborted (Rolled Back): If any operation fails or power cuts out, all partial modifications are completely undone, leaving the database state as if the transaction never executed.

This guarantee is formalized by the acronym ACID.

Deconstructing ACID: Mechanism by Mechanism

=====================================================================
                      THE ACID GUARANTEES
=====================================================================
A - ATOMICITY     ===> All-or-nothing execution via Undo Logs
C - CONSISTENCY   ===> Schema constraint preservation (PK/FK/CHECK)
I - ISOLATION     ===> Concurrency control via MVCC and Locks
D - DURABILITY    ===> Crash recovery via Write-Ahead Logging (WAL)
=====================================================================

1. Atomicity (All-or-Nothing Execution)

Atomicity guarantees that all SQL statements inside a transaction execute to completion, or none of them take effect.

How Database Engines Enforce Atomicity:

Database engines maintain an Undo Log (or rollback segment).

Before modifying any 16KB page in memory, the engine writes the original, un-modified row bytes to the Undo Log.

TRANSACTION EXECUTION:
1. Write original state (User A = $500, User B = $200) to Undo Log.
2. Modify in-memory pages: (User A = $400, User B = $300).
3. If error occurs during step 2 -> Read Undo Log & restore original bytes!

If a statement fails or the user issues a ROLLBACK command, the engine reads the Undo Log backward and overwrites modified pages with original data.

2. Consistency (Preserving Domain Constraints)

Consistency in ACID is often confused with Consistency in the CAP Theorem.

C in CAP Theorem: Refers to Linearizability in distributed systems (all replicas returning the exact same data value at any given instant).
C in ACID: Refers to Schema & Business Constraint Preservation (Primary Key uniqueness, Foreign Key references, CHECK constraints, NOT NULL rules).

Mechanism:

Database engines validate invariants during transaction execution. If a transaction attempts to insert a record that violates a FOREIGN KEY reference or CHECK (balance >= 0) rule, the engine aborts the transaction and triggers atomicity rollback.

3. Isolation (Managing Concurrent Transactions)

Isolation ensures that concurrently executing transactions do not interfere with each other’s intermediate state.

If Transaction 1 updates User A’s balance to $400 but has not committed yet, Transaction 2 should not be able to read that uncommitted $400 value.

CONCURRENT TRANSACTION ISOLATION

Tx 1: BEGIN -> Update User A ($400) --------------------------> COMMIT
                                     \ (Must be isolated!)
Tx 2: BEGIN -------------------------> Reads User A ($500) ---> COMMIT

Isolation is enforced through Multi-Version Concurrency Control (MVCC) and Locking Protocols (2PL), which we will build in Modules 3 and 5.

4. Durability (Surviving System Crashes)

Durability guarantees that once a transaction receives a successful COMMIT acknowledgment, its changes are permanently recorded and will survive power outages, OS crashes, or hardware failure.

The naive approach:

Call fsync() on every 16KB table page when a transaction commits. But writing 50 random 16KB pages to disk on every commit destroys throughput.

The engine mechanism:

Database engines write transaction operations sequentially to an append-only Write-Ahead Log (WAL) file on disk before modifying physical table pages.

During crash recovery, the engine reads the WAL log to reconstruct committed transactions.

Transaction State Lifecycle

A database transaction moves through five formal states during execution:

              +----------+
              | Active   | <--- Executing SQL statements
              +----------+
             /            \
            v              v
  +---------------+   +--------------+
  | Partially     |   | Failed       | <--- Exception / Constraint error
  | Committed     |   +--------------+
  +---------------+          |
          |                  v
          v           +--------------+
  +---------------+   | Aborted      | <--- Undo Log applied
  | Committed     |   +--------------+
  +---------------+

Active: Initial state while executing statements.
Partially Committed: Final statement executed; WAL logs written, awaiting final fsync().
Committed: WAL flushed to disk; transaction successfully finished.
Failed: Error detected during execution.
Aborted: Partial changes undone using Undo Log records.

Quick Summary

Transactions provide a boundary for grouping multiple database reads and writes.
Atomicity uses Undo Logs to revert partial modifications if a transaction fails.
Consistency ensures database schema constraints (PK/FK/CHECK) remain valid before and after transactions.
Isolation hides uncommitted intermediate modifications from concurrent transactions.
Durability uses Write-Ahead Logging (WAL) to guarantee committed writes survive system crashes.

References & Further Reading

Comer, D. (1979). The Ubiquitous B-Tree. ACM Computing Surveys, 11(2), 121–137.
Bayer, R., & McCreight, E. M. (1972). Organization and Maintenance of Large Ordered Indexes. Acta Informatica, 1(3), 173–189.
Graefe, G. (2011). Modern B-Tree Techniques. Foundations and Trends in Databases.