ACID (Atomicity, Consistency, Isolation, Durability)

Definition: A set of four properties, Atomicity, Consistency, Isolation, Durability, that a database transaction must satisfy to be processed reliably despite errors, power loss, or concurrent access.

How It Works

Atomicity: a transaction’s writes all happen or none do. If any step fails, the engine undoes every change made so far, using undo records or by simply never applying uncommitted writes to the main data files.
Consistency: a transaction can only move the database from one valid state to another. Constraints, foreign keys, and triggers are checked before commit; a violation forces a rollback. Note this is application-defined consistency, not the “C” in CAP theorem, the two are unrelated despite sharing a name.
Isolation: concurrent transactions behave as if they ran one after another, to a degree controlled by the isolation level. Lower levels allow more concurrency but expose more anomalies (dirty reads, non-repeatable reads, phantoms).
Durability: once a transaction reports success, its effects survive a crash immediately after. This is almost always implemented with Write-Ahead Logging (WAL): the change is fsync’d to a log before the client gets an acknowledgment.

Property-to-Mechanism Map

Property	What breaks without it	Primary mechanism
Atomicity	Partial writes leave the database in a half-finished state	Undo/redo logging, all-or-nothing apply at commit
Consistency	Constraints and business invariants can be silently violated	`CHECK`/`FOREIGN KEY`/`UNIQUE` evaluation at commit
Isolation	Concurrent transactions see each other’s uncommitted work	Locking, or MVCC snapshots, governed by isolation level
Durability	A crash right after “success” quietly loses the change	WAL record fsync’d before the client is acknowledged

Under the Hood

A transfer of $50 from account A to account B, committed successfully, then a second attempt that fails a constraint check:

Worked example 1: crash mid-commit

Given: account A has $200, account B has$ 100. A transaction debits A by 50 and credits B by 50.
Step: the engine writes both changes to the WAL and fsyncs the commit record. The power fails before the data pages themselves are updated.
Step: on restart, crash recovery scans the WAL, finds the committed transaction’s log records, and replays them against the data pages.
Answer: A ends at $150, B ends at$ 150. The transaction is durable even though the data pages were never touched before the crash.

Worked example 2: constraint violation

Given: accounts.balance has a CHECK (balance >= 0) constraint. Account A has $30.
Step: a transaction attempts to debit A by 50, then credit B by 50.
Step: at commit time (or immediately, depending on constraint deferrability) the engine evaluates the check and finds -20 violates it.
Answer: the whole transaction rolls back. B’s balance is untouched, A stays at $30. Nothing partial is ever visible to other sessions.

Worked example 3: isolation under concurrency

Given: account A has $150. Transaction T1 starts a transfer that debits A by 50 but has not committed yet.
Step: transaction T2 (a different session) runs SELECT balance FROM accounts WHERE id = A while T1 is still open.
Step: under Read Committed or stronger, T2 sees the pre-T1 value, $150, not the uncommitted$ 100, because uncommitted writes are never visible to other transactions.
Answer: T2 only sees $100 after T1 commits. If T1 rolls back instead, T2 never sees the intermediate value at all, that is what isolation is protecting against (a dirty read).

Atomicity and Durability are really two halves of the same log-based mechanism: the WAL record for a transaction is either entirely present and marked committed, or it is not, and recovery only ever replays complete, committed records. Isolation and Consistency are enforced at the transaction manager and constraint-checking layer instead, which is why databases can offer several isolation levels while atomicity and durability stay fixed.

Why It Matters

Without these four properties, ordinary failures, a crashed process, two sessions writing at once, a bad input row, silently corrupt data instead of cleanly failing. Financial ledgers, inventory counts, and any system where “half a transaction” is worse than no transaction depend on ACID guarantees, not just as a nice-to-have but as the reason the data can be trusted at all.

Debugging is tractable: if a bug corrupts data, ACID lets you rule out “the database silently did something impossible” and focus on application logic instead.
Multi-step business operations, placing an order, reserving stock, charging a card, can be expressed as one transaction instead of a pile of manual compensation logic for every possible partial failure.
It sets a shared vocabulary. When an engineer says “this needs Serializable isolation,” everyone on the team knows exactly which anomalies that rules out, without re-explaining the scenario from scratch.

Common Pitfalls

Assuming a database is ACID because it has “transactions”. Many document and key-value stores offer atomicity only within a single document or row, not across multiple.
Treating “Consistency” here as the same thing as consistency in the CAP theorem. They are different concepts that happen to share a word.
Running at a weak isolation level (e.g. Read Committed) and assuming Serializable-level guarantees, then getting bitten by lost updates or write skew under load.
Forgetting that AUTOCOMMIT mode wraps every single statement in its own implicit transaction, which defeats the point of atomicity across multi-statement business logic unless it’s explicitly disabled.
Believing durability means “safe the instant I call commit” regardless of configuration, when synchronous_commit = off (Postgres) or similar settings trade durability for throughput by not waiting for the fsync.
Wrapping a long-running batch job in one giant transaction for “atomicity”, then discovering it holds locks and bloats undo/WAL storage for the entire run instead of committing in smaller, still-correct units.
Assuming a ROLLBACK is instant and free. On a large transaction it has to undo every logged change, which can take nearly as long as the original writes did.

Comparison

Model	Atomicity	Consistency	Isolation	Durability	Typical use
ACID (PostgreSQL, MySQL InnoDB, Oracle)	All-or-nothing per transaction	Enforced constraints/foreign keys	Configurable, up to Serializable	WAL fsync’d before ack	Banking, orders, inventory
BASE (Cassandra, DynamoDB)	Per-row or per-partition only	Application-enforced	Eventual, tunable per request	Replicated, not always fsync’d	High-scale, availability-first systems
NewSQL (CockroachDB, Google Spanner)	Full ACID across nodes	Enforced via consensus	Serializable by default	Replicated log + WAL	Distributed OLTP needing SQL guarantees

The BASE row is not “worse,” it is a different trade-off. Systems like Cassandra give up cross-row atomicity and immediate consistency in exchange for staying available and low-latency during network partitions, which the CAP Theorem says you cannot have alongside strict consistency. NewSQL systems try to reclaim full ACID in a distributed setting by paying for it with consensus round-trips (Raft or Paxos) on every commit, trading raw latency for correctness instead of trading correctness for latency.

Example

PostgreSQL implements all four properties together rather than separately: Write-Ahead Logging (WAL) provides atomicity and durability by logging before applying changes, MVCC provides isolation by giving each transaction a consistent snapshot, and the constraint system (CHECK, FOREIGN KEY, UNIQUE) enforces consistency at commit time. Crash recovery replays the WAL on startup to restore any transactions that committed but hadn’t been fully flushed to data files.

MySQL’s InnoDB storage engine follows the same pattern with its own redo log and undo log tablespace, and exposes the same four isolation levels via SET TRANSACTION ISOLATION LEVEL. Oracle Database instead defaults to a form of Snapshot Isolation it calls “Read Committed” plus optional “Serializable,” both backed by undo segments rather than InnoDB-style multi-versioned pages, a reminder that ACID is a contract, not a single required implementation.

Where Isolation Fits

Isolation is the one property with a whole spectrum of valid answers rather than a single correct behavior, which is why it gets its own standard:

Level	Guards against	Cost
Read Uncommitted	Nothing extra	Cheapest, rarely used in practice
Read Committed	Dirty reads	Default in PostgreSQL, Oracle, SQL Server
Repeatable Read	Dirty + non-repeatable reads	Default in MySQL InnoDB
Serializable	Dirty, non-repeatable, and phantom reads	Most locking/retry overhead

See Transaction Isolation Levels for the anomalies each level allows or blocks, and MVCC for how modern engines implement Repeatable Read and Serializable without blocking every reader behind a writer.

Referenced by

Dig deeper

Spot an error in this note?