1. Atomicity: All-or-Nothing Execution via Undo Logs

Formal Definition

Atomicity guarantees that a database transaction is treated as a single, indivisible unit of work. Either every SQL statement inside the transaction boundary (BEGIN ... COMMIT) executes to completion, or none of its mutations are applied. Partial execution is strictly impossible.

How Atomicity Preserves Data Consistency

• Consider a double-entry financial transfer that debits Account A and credits Account B across two separate SQL UPDATE statements.
• Storage engines implement atomicity using an Undo Log (or transaction status log in PostgreSQL). Before modifying a data page in the buffer pool, the engine records the prior tuple version.
• If the second operation fails due to a constraint violation, deadlock, or process crash before COMMIT, the storage engine replays the Undo Log in reverse—rolling back the debit on Account A so account balances never drift out of equilibrium.

BEGIN TRANSACTION;

-- Step 1: Debit source account
UPDATE ledger_accounts
SET balance_cents = balance_cents - 25000
WHERE account_id = 'acct_source_01' AND balance_cents >= 25000;

-- Step 2: Credit destination account
UPDATE ledger_accounts
SET balance_cents = balance_cents + 25000
WHERE account_id = 'acct_dest_02';

-- Both mutations become visible atomically, or neither takes effect on ROLLBACK
COMMIT;

2. Consistency: Enforcing Schema & Domain Invariants

Formal Definition

Consistency ensures that any transaction transitions the database from one valid state to another valid state. It enforces all declarative rules, relational integrity checks, and domain constraints defined in the schema—including PRIMARY KEY uniqueness, FOREIGN KEY referential integrity, NOT NULL requirements, data type bounds, and CHECK constraints.

How Consistency Preserves Data Integrity

• Before allowing a transaction to commit, the RDBMS validates every modified tuple against active constraints and triggers.
• If an statement attempts to insert an orphaned foreign key or drive a column with CHECK (balance_cents >= 0) into a negative balance, the engine aborts the transaction and restores the pre-transaction state.
• By enforcing invariants inside the storage engine rather than relying solely on application-tier validation, consistency prevents race conditions or buggy service deployments from persisting corrupted records.

3. Isolation: Concurrency Control via MVCC & Locking

Formal Definition

Isolation ensures that concurrently executing transactions do not corrupt or leak intermediate state to one another. Modern relational engines combine Multi-Version Concurrency Control (MVCC)—where readers inspect immutable point-in-time tuple snapshots (xmin/xmax transaction IDs) without blocking writers—with granular row/predicate locks.

Concurrency Anomalies Prevented by Isolation

• Dirty Reads: Reading uncommitted modifications from a concurrent transaction that subsequently rolls back.
• Non-Repeatable Reads: Re-reading the same row within a single transaction and observing different values because another transaction committed an UPDATE in between.
• Phantom Reads: Re-executing a range query and observing newly inserted or deleted rows that match the WHERE predicate.

4. Durability: Crash Recovery via Write-Ahead Logging (WAL)

Formal Definition

Durability guarantees that once the database acknowledges a COMMIT to the client, its state changes are permanent and will survive power loss, kernel panics, or process crashes.

How Write-Ahead Logging Guarantees Durability

• Writing dirty 8KB or 16KB B-Tree pages across random disk offsets on every commit would bottleneck I/O throughput. Instead, relational engines use Write-Ahead Logging (WAL / Redo Log).
• Before acknowledging COMMIT, the engine appends a sequential redo record describing the byte-level page delta to the WAL file and issues an OS fsync() flush to non-volatile NVMe/SSD storage.
• If a power outage occurs before background checkpoint workers flush the dirty buffer pool pages to the main table files, the database replays the WAL log during startup (ARIES recovery protocol) to reconstruct every committed transaction losslessly.

How ACID Properties Work Together in Production

The four ACID guarantees form an interdependent reliability contract for transactional systems:

• Atomicity prevents partial multi-statement mutations via Undo Logs.
• Consistency validates schema constraints and relational invariants on every state transition.
• Isolation arbitrates concurrent reader and writer access via MVCC snapshots and lock managers.
• Durability persists committed deltas sequentially to non-volatile Write-Ahead Logs before returning success to the caller.