ACID Database Properties: Atomicity, Consistency, Isolation & Durability in Practice

Series Mindset: "A database transaction is not magic syntax — it is a physical contract between the storage engine's write-ahead log and your application's tolerance for concurrency anomalies, whereas BASE is the explicit acknowledgment that under distributed scale, availability and convergence supersede global locks."

The broken pattern: teams wrap complex domain operations in a BEGIN ... COMMIT block, check off "ACID compliant" on their architecture review, and assume their data layer is immune to corruption. In production, wrapping queries in a transaction provides zero safety against write skew under default isolation levels, does not prevent partial state leaks across microservices, and will happily discard committed records if the underlying disk controller's volatile write cache loses power before an fsync barrier. ACID is not a binary badge — it is a strict mechanical contract with four distinct properties, each enforced by a different storage subsystem.

Expand

1. Atomicity: The All-or-Nothing Primitive

Atomicity guarantees that a series of database operations either execute entirely or leave the database state completely unmodified. It is a crash-resilience property, not a concurrency property — two transactions can both be perfectly atomic while still corrupting shared state by interleaving their writes.

1.1 The Broken Pattern: Unprotected Multi-Step Mutations

-- ❌ Broken Pattern: Naive multi-statement execution without rollback semantics
UPDATE accounts SET balance = balance - 500 WHERE id = 101;
-- 💥 Power failure, process crash, or syntax error occurs right here!
UPDATE accounts SET balance = balance + 500 WHERE id = 202;
-- Result: 500 currency units vanished from the system. Invariant violated.

This pattern appears throughout codebase history as "two separate API calls that should both succeed." In microservices, the equivalent is calling Service A's REST endpoint, receiving HTTP 200, then calling Service B and receiving a network timeout. Without a distributed rollback mechanism, the data is now permanently inconsistent — Service A's state changed but Service B's did not.

1.2 The Mechanical Fix: Write-Ahead Logging (WAL) & Undo Buffers

Databases do not write directly to data table pages on disk during a transaction. Instead, every mutation appends a sequential change record to an append-only Write-Ahead Log (WAL). If the process crashes mid-transaction, recovery replays the WAL forward and uses undo records to roll back uncommitted changes.

The WAL's sequential write pattern is intentional: sequential disk I/O is 10–100× faster than random page writes. The data pages on disk can be updated lazily at checkpoint time because the WAL already contains the full audit trail needed for crash recovery.

For background on exactly how the WAL, buffer pool, B-Tree pages, and checkpoint flushes interact at the I/O layer, see Database Internals: What Happens Below the Query.

2. Consistency: Application Invariants vs CAP Linearizability

The letter "C" is the most misunderstood property in database theory because it means entirely different things in ACID and CAP:

2.1 Invariant Protection in SQL

-- ✅ Enforcing ACID Consistency via Declarative Schema Invariants
CREATE TABLE accounts (
    id BIGSERIAL PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    balance NUMERIC(12, 2) NOT NULL,
    currency VARCHAR(3) NOT NULL,
    CONSTRAINT chk_positive_balance CHECK (balance >= 0.00),
    CONSTRAINT uq_user_currency UNIQUE (user_id, currency)
);

Each constraint type protects a different class of invariant:

• REFERENCES ... ON DELETE RESTRICT — referential integrity (orphan prevention)
• CHECK (balance >= 0.00) — domain invariant (business rule enforcement)
• UNIQUE (user_id, currency) — uniqueness invariant (duplicate prevention at the storage layer, not just application code)

2.2 The Invariant the Schema Cannot Enforce: Cross-Row Checks

Schema-level constraints validate single-row conditions atomically. Multi-row business rules — like "a user's total balance across all currencies must not exceed their credit limit" — require either a trigger, a deferred constraint, or an application-level read-check inside the transaction:

-- ✅ Application-enforced cross-row invariant within a serializable transaction
BEGIN;
  -- Lock the user row to prevent concurrent credit modifications
  SELECT total_credit_limit FROM users WHERE id = $user_id FOR UPDATE;
  
  SELECT COALESCE(SUM(balance_usd_equivalent), 0)
    INTO current_total
    FROM accounts
    WHERE user_id = $user_id;

  -- Business logic check before allowing the mutation
  IF (current_total + $new_amount) > $credit_limit THEN
    RAISE EXCEPTION 'credit_limit_exceeded';
  END IF;
  
  INSERT INTO accounts (user_id, currency, balance) VALUES ($user_id, 'EUR', $new_amount);
COMMIT;

3. Isolation: The Spectrum of Concurrency Anomalies

Isolation dictates the degree to which concurrently executing transactions are invisible to one another. Full isolation (SERIALIZABLE) is equivalent to sequential, one-by-one serial execution — safe but expensive. The SQL standard defines a progression of weaker guarantees, each permitting specific classes of anomalies in exchange for higher throughput.

For an exhaustive breakdown of each anomaly class — dirty reads, non-repeatable reads, phantom reads, and write skew — including the exact MVCC snapshot timestamps at which each anomaly manifests, see Isolation & Concurrency Control: The Anomaly You Are Allowing Right Now.

3.1 The Hidden Trap: Write Skew Under Repeatable Read

REPEATABLE READ is the most dangerous isolation level because its safety feels intuitively complete. It sounds like "I can repeat my reads safely." What it actually means is "my individual row reads are stable, but concurrent transactions can perform conflicting writes based on the same stable snapshot without blocking each other."

-- ❌ Write Skew: Two concurrent transactions each read valid state,
--    independently decide to mutate, and together violate the invariant.

-- Transaction A: Doctor Alice requests on-call shift release
BEGIN;
SELECT COUNT(*) FROM shifts WHERE date = '2026-09-07' AND on_call = TRUE; -- returns 2 ✅ (safe to release)
UPDATE shifts SET on_call = FALSE WHERE doctor_id = 'alice' AND date = '2026-09-07';
COMMIT;

-- Transaction B (Concurrent): Doctor Bob requests on-call shift release
BEGIN;
SELECT COUNT(*) FROM shifts WHERE date = '2026-09-07' AND on_call = TRUE; -- also returns 2 ✅ (safe to release)
UPDATE shifts SET on_call = FALSE WHERE doctor_id = 'bob' AND date = '2026-09-07';
COMMIT;

-- Both transactions committed successfully under REPEATABLE READ!
-- 💥 Violation: Zero doctors are now on-call. Business rule: minimum 1 on-call doctor. BROKEN.

The fix requires either SERIALIZABLE isolation or an explicit SELECT ... FOR UPDATE on the aggregate guard row:

-- ✅ Fix Option 1: SERIALIZABLE isolation (PostgreSQL SSI detects the cycle and aborts one tx)
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM shifts WHERE date = '2026-09-07' AND on_call = TRUE;
-- PostgreSQL SSI will detect the rw-anti-dependency and abort the conflicting transaction.
UPDATE shifts SET on_call = FALSE WHERE doctor_id = 'alice' AND date = '2026-09-07';
COMMIT;

-- ✅ Fix Option 2: Explicit predicate locking via FOR SHARE
BEGIN;
SELECT COUNT(*) FROM shifts WHERE date = '2026-09-07' AND on_call = TRUE FOR SHARE;
-- This now blocks concurrent transactions from modifying the counted rows until this tx commits.
UPDATE shifts SET on_call = FALSE WHERE doctor_id = 'alice' AND date = '2026-09-07';
COMMIT;

3.2 MVCC: How PostgreSQL Isolates Without Blocking

PostgreSQL's Multi-Version Concurrency Control (MVCC) allows reads to never block writes and writes to never block reads by maintaining multiple physical versions of each row. Every row carries two hidden system columns:

-- Inspect MVCC row visibility metadata directly
SELECT xmin, xmax, id, balance FROM accounts WHERE id = 42;

-- xmin=5001, xmax=0  → Row created by txn 5001, still live (not yet deleted/updated)
-- xmin=5001, xmax=5003 → Row created by txn 5001, invalidated by txn 5003's UPDATE

When a SELECT executes at isolation level READ COMMITTED, it sees only rows where xmin is committed and xmax is either 0 or belongs to an aborted transaction. At REPEATABLE READ, the snapshot is fixed to a single transaction start timestamp, hiding all commits that occurred after BEGIN. For more detail on xmin/xmax mechanics and vacuum's role in cleaning dead tuples, see Relational Databases: PostgreSQL & MySQL.

Expand

4. Durability: The Physical Reality of Disk Flushing

Durability guarantees that once a transaction commits, its effects survive power outages, operating system panics, and process crashes. Achieving true durability requires understanding the I/O stack between application code and physical non-volatile media.

Each arrow in the chain represents a durability boundary where data can be permanently lost on power failure:

1. write() → OS page cache: Data is in volatile DRAM. A kernel panic destroys it.
2. OS page cache → disk controller buffer: Data is in the controller's volatile cache. A power failure without Battery-Backed Unit (BBU) destroys it.
3. Disk controller → NAND/platter: Data is physically persisted. Survives power failure.

4.1 The fsync = off Trade-off

PostgreSQL's fsync = off setting disables the fsync() call on WAL writes, which can increase write throughput by 10–100× on HDD-based systems. It is tempting to use in high-throughput environments:

-- ⚠️ NEVER in production: disables fsync, removing the Durability guarantee
-- postgresql.conf:
fsync = off
synchronous_commit = off   -- Even riskier: async commit ACK before WAL flush

[!CAUTION] fsync = off does not merely risk a single transaction's data — it risks database corruption. If the OS crashes with fsync = off active, the on-disk data page files and the WAL can diverge, resulting in unrecoverable corruption that pg_resetwal cannot fix. This setting is appropriate only for ephemeral test databases.

4.2 Group Commit: Durability at Scale

Executing fsync() on every individual transaction commit throttles write throughput to the storage device's physical seek-and-flush latency (~1–10ms per operation, or ~100–1,000 commits/second on HDDs). Modern ACID engines use Group Commit: bundling dozens of concurrently-committing transactions into a single batch fsync() call:

PostgreSQL's group commit is automatic and tunable via commit_delay and commit_siblings. It is what allows high-concurrency OLTP workloads (tens of thousands of commits per second) without sacrificing the fsync durability guarantee.

5. The Scaling Wall: Distributed ACID vs BASE

Single-node ACID is the gold standard for financial ledgers and relational correctness, but it hits a physical scaling ceiling:

1. Vertical Limit: A single primary database can only scale as far as the host machine's NVMe write bandwidth and CPU lock bus permit. Past roughly 1M writes/second, you either shard or choose a different paradigm.
2. Network Partitions: Spanning an ACID transaction across multiple geographic data centers via Two-Phase Commit (2PC) makes every transaction's latency equal to the slowest node's round-trip time. If any coordinator node becomes unreachable, all participant nodes hold locks indefinitely — a distributed deadlock.

For how distributed 2PC stalls and why the Transactional Outbox pattern is the production-safe alternative, see Distributed Transactions: Two-Phase Commit, Transactional Outbox & CDC.

For how modern distributed SQL databases like CockroachDB achieve true ACID transactions across geographic shards using Raft consensus and Hybrid Logical Clocks (HLC), see Cloud-Native & Distributed SQL: CockroachDB.

When business logic spans services and global ACID is prohibitive, SAGA patterns replace Isolation with explicit compensating transactions — trading the "I" in ACID for choreographed eventual consistency. This is examined in SAGA Pattern: Choreography & Orchestration.

This scaling threshold is where systems transition from ACID to BASE (Basically Available, Soft state, Eventual consistency), the distributed counterweight analyzed in Part 2.

Expand

Summary

Key Takeaways

• Atomicity is all-or-nothing execution enforced by Write-Ahead Logs (WAL) and undo buffers; it provides zero protection against concurrent interleaving without Isolation.
• The 'C' in ACID refers to application schema invariants (constraints, checks, foreign keys), which is fundamentally orthogonal to the 'C' (linearizability) in the CAP theorem.
• Isolation levels are a contractual menu of tolerable concurrency anomalies; picking the wrong level either invites silent write skew or incurs catastrophic lock serialization.
• Durability is physically dictated by the fsync barrier and disk controller write caches — without synchronous flushing, committed transactions remain vulnerable to power loss in the OS buffer cache.
• Single-node ACID guarantees do not scale across network partitions; attempting distributed 2-Phase Commit introduces coordinator blocking and extreme tail latency.

What's Next

In Part 2, we deconstruct the distributed counterweight to ACID: BASE Database Properties: Basically Available, Soft State & Eventual Consistency in Distributed Systems — detailing quorum mathematics, vector clocks, and CRDT convergence under network partitions.