Core Code
Core Code Modern Dev Handbook
DATABASE-ADVANCED Contents
Storage & Index Internals
DB Page Layouts B-Tree Internals Hash, GIN, and GiST Indexes Selectivity & Cardinality Index Maintenance & Bloat Covering & Partial Indexes WAL & Write Amplification MVCC and HOT Updates
Query Planner & Execution
Query Lifecycle Cost-Based Optimizer Basics Reading EXPLAIN ANALYZE Join Strategies Index Scan vs Seq Scan Statistics & Misestimation Plan Regressions Query Optimization Checklist
Transactions & Isolation
ACID Realities Isolation Levels Deep Dive Phantoms, Lost Updates, and Anomalies Deadlocks: Detection and Retries Locking Mechanisms Long-Running Transactions Optimistic vs Pessimistic Concurrency Transaction Safety Checklist
Replication & High Availability
Replication Types Postgres Replication Model MySQL Replication Model Replica Lag and Read-After-Write Failover Strategies Backup Strategies Point-in-Time Recovery HA Architecture Patterns
Sharding & Horizontal Scale
Vertical vs Horizontal Scaling Sharding Patterns Shard Key Design Cross-Shard Queries Rebalancing and Resharding Multi-Region Data Architectures Global Consistency Tradeoffs Sharding Operational Checklist
Postgres vs MySQL Tradeoffs
Storage Engine Differences JSON Support Comparison Indexing Capability Differences Replication Differences Ecosystem and Extensions Interpreting Benchmarks Choosing Between Postgres and MySQL Migration Surface Area
Caching & Redis Strategies
Caching Patterns Redis Data Structures in Production Cache Invalidation Cache Stampede and Thundering Herd TTL Design Strategies Read-Heavy Optimization Cache Consistency Models Cache Observability Checklist
Data Modeling at Scale
Normalization vs Denormalization Soft Delete Patterns Event-Driven Modeling Time-Series Modeling Multi-Tenant Schema Design Audit Trail Design Schema Migration Strategies Data Modeling Checklist

ACID Realities

What ACID guarantees, what it does not, and how apps accidentally defeat correctness.
On this page

ACID Is a Contract, Not a Guarantee of Performance

ACID is often misunderstood as “the database is safe.” In reality, ACID defines guarantees about correctness under failure and concurrency. It says nothing about performance, scalability, or latency.

Understanding what ACID really guarantees — and what it does not — is essential for production systems.

Atomicity: All or Nothing

Atomicity means a transaction either fully commits or fully rolls back. Partial updates are not allowed.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If the system crashes before COMMIT, both updates must disappear. If after COMMIT, both must persist.

Atomicity is implemented using write-ahead logging and crash recovery mechanisms.

Consistency: Application Rules Enforced

Consistency means that after a committed transaction, all defined constraints are satisfied.

  • Foreign keys
  • Unique constraints
  • Check constraints

Important: consistency does not mean “business logic correct.” The database enforces declared constraints only.

Isolation: Concurrent Transactions Behave Predictably

Isolation defines how transactions interact when running at the same time. Different isolation levels allow different anomalies.

Isolation is not binary; it is configurable. Stronger isolation often means higher contention or lower throughput.

Durability: Survives Crash

Durability means once a transaction commits, it will survive a crash.

Durability relies on:

  • WAL / redo logs
  • fsync guarantees
  • Replication (optionally)

Changing durability settings trades safety for performance. This must align with business RPO requirements.

ACID Myths in Production

  • Myth: ACID means no data loss.
    Reality: durability depends on configuration and hardware reliability.
  • Myth: Serializable isolation is always safest.
    Reality: it can drastically reduce throughput.
  • Myth: ACID prevents logical bugs.
    Reality: ACID enforces declared constraints, not business correctness.
  • Myth: More isolation is always better.
    Reality: higher isolation increases contention and latency.

ACID vs Distributed Systems Reality

In distributed systems, ACID may apply only within a single database node. Cross-service workflows often require compensation patterns (sagas) instead of single ACID transactions.

Assuming global ACID across services is a design error.

Durability and Replication Interaction

Commit acknowledgment does not always mean replica persistence. Depending on configuration:

  • Primary may acknowledge before replica applies change.
  • Crash before replica sync may cause divergence.

Understand your replication acknowledgment semantics.

Performance Cost of ACID

ACID properties impose cost:

  • Logging overhead (WAL/binlog)
  • Lock management
  • Memory usage for transaction tracking
  • Cleanup (MVCC vacuum)

High-concurrency workloads magnify these costs.

Failure Modes in Production

  • Durability weakened for speed: data loss after crash.
  • High isolation level: lock contention and timeouts.
  • Long transactions: block cleanup and inflate bloat.
  • Cross-service assumption: inconsistent state across systems.
  • Replication misunderstanding: stale reads or lost writes under failover.

Operational Checklist

  • Document required isolation level per workload.
  • Align durability settings with RPO/RTO requirements.
  • Avoid using strongest isolation by default without load testing.
  • Keep transactions short to reduce lock time.
  • Monitor replication acknowledgment semantics.
  • Educate teams on difference between ACID and distributed consistency.
  • Test crash recovery scenarios periodically.
  • Validate constraint coverage matches business invariants.
  • Monitor lock waits and transaction age.
  • Do not weaken durability silently for performance.

Summary

ACID defines correctness boundaries, not performance guarantees. Each letter has implementation cost and operational implications. Production database engineering requires understanding these tradeoffs explicitly, not assuming safety by default.

Isolation Levels Deep Dive →