Transactions & ACID

What each ACID property actually guarantees — and what it doesn't

Why

ACID is the contract a relational database makes with you. Without it, concurrent writes and crashes produce corrupted data — partial transfers, duplicate charges, lost updates. Every engineer who writes database-backed code is implicitly relying on ACID. Knowing what each letter actually guarantees — and doesn't — lets you write correct application code and spot designs that silently violate the contract.

Intuition

Atomicity: a transaction is all or nothing — partial commits don't exist. Consistency: constraints (foreign keys, CHECK) hold before and after every transaction. Isolation: concurrent transactions behave as if they ran serially. Durability: once committed, data survives crashes.

Of the four, Isolation is the most nuanced — databases offer different isolation levels that trade correctness for performance. Full serializability is correct but slow; weaker levels allow anomalies in exchange for higher concurrency.

Explanation

ACID properties

Atomicity

All statements in a transaction commit together or all roll back. Guaranteed by the WAL — on crash, uncommitted transactions are rolled back on recovery. "Transfer $100 from A to B" either completes fully or neither account changes.

Consistency

Transactions move the DB from one valid state to another. Constraints (NOT NULL, UNIQUE, FK, CHECK) are enforced at commit. Note: consistency is partly the application's responsibility — the DB enforces declared constraints, not application-level business rules.

Isolation

Concurrent transactions don't interfere with each other. The degree of isolation is configurable via isolation levels. Full serializable isolation is correct but limits concurrency. Weaker levels allow specific anomalies in exchange for performance.

Durability

A committed transaction persists through crashes. Guaranteed by the WAL — the commit record is flushed to durable storage before the transaction is acknowledged. fsync() ensures the OS doesn't buffer the write in volatile memory.

Isolation levels and anomalies

Anomaly | Description -------------------|-------------------------------------------------- Dirty read | Reading uncommitted changes from another transaction Non-repeatable read| Reading the same row twice gives different values Phantom read | A range query returns different rows on re-execution Lost update | Two transactions read then write the same row; | one overwrites the other's change Isolation level | Dirty | Non-rep | Phantom | Common in -------------------|-------|---------|---------|------------------ Read Uncommitted | YES | YES | YES | Rarely used Read Committed | no | YES | YES | Postgres default Repeatable Read | no | no | YES | MySQL InnoDB default Serializable | no | no | no | Strictest, slowest

Postgres's default Read Committed prevents dirty reads but allows non-repeatable reads — a transaction may see different values for the same row if another transaction commits between its reads. Use Repeatable Read or Serializable when your logic depends on stable reads within a transaction.

← Previous Next →