ACID
The four guarantees a relational transaction makes about what happens when things go wrong partway through.
Overview
ACID is the set of four guarantees — Atomicity, Consistency, Isolation, Durability — that a relational transaction makes about what happens to your data when a multi-step operation runs, especially when something goes wrong partway through it.
Why It Exists
Real operations often need several separate writes to happen together as one indivisible unit — debit one account, credit another. Without a formal guarantee, a crash or a conflicting concurrent operation partway through could leave the data in a state that violates an invariant the application assumed could never happen — money leaving one account without arriving in the other. ACID exists to name exactly what 'together, safely' means, so an engine can guarantee it instead of every application reinventing its own partial version of the same guarantee.
Real World Example
Map all four letters onto one `transfer_funds(1, 2, 500)` call: Atomicity means the debit from account 1 and the credit to account 2 either both happen or neither does. Consistency means the total money in the system still balances afterward, and no constraint (like a balance never going negative) is violated. Isolation means a concurrent read of either account mid-transfer never sees a half-completed state — only fully-before or fully-after. Durability means once the transfer commits, it survives a server crash the very next instant.
Example Data
accounts — before and after transfer_funds(1, 2, 500), atomically
| id | owner | balance |
|---|---|---|
| 1 | Sarah Johnson | 1000 → 500 |
| 2 | Michael Carter | 200 → 700 |
The Four Letters, One at a Time
Atomicity
All statements in a transaction succeed together, or none of them take effect at all. A crash or an error partway through triggers a full rollback — never a partial one where only some of the intended changes stuck.
Consistency
A transaction can only move the database from one valid state to another valid state — every constraint (foreign keys, CHECK, uniqueness) must hold both before and after. This is the least mechanically-enforced of the four letters on its own — it's really the outcome you get from Atomicity, Isolation, and Durability working together, combined with the constraints you've actually declared on your schema.
Isolation
Concurrent transactions behave as if they ran one after another, not interleaved — even though the engine actually runs them concurrently for performance. Exactly how strictly this is enforced is configurable, covered in full in the Isolation Levels chapter later in this path.
Durability
Once a transaction commits, its effects survive a crash immediately afterward. This is typically guaranteed via Write-Ahead Logging — the change is flushed to a durable log on disk before the commit is ever acknowledged back to the caller.
Diagram
transfer_funds(1, 2, 500) — all four guarantees checked on the same transaction
BEGIN transaction
Atomicity
both writes, or neither
Consistency
constraints still hold
Isolation
no half-done state visible
Durability
survives a crash after commit
COMMIT — or full ROLLBACK on any failure
Common Mistakes
Treating ACID's 'Consistency' as the same idea as CAP theorem's 'Consistency'
Why: They're genuinely different concepts that happen to share a name. ACID's C is about a single transaction respecting your own schema's constraints. CAP's C (covered later in this path) is about every node in a distributed system seeing the same data at the same time. Conflating them muddles architecture discussions.
Fix: Mentally rename ACID's C to 'constraint validity' whenever you're comparing it against CAP's C.
Assuming 'ACID' alone tells you how strictly isolated your transactions are
Why: Only the strictest isolation level (Serializable) prevents every possible anomaly. Most engines default to something weaker, which permits specific, named anomalies while still being fully 'ACID-compliant.'
Fix: Know your engine's actual default isolation level — 'ACID' guarantees the four properties exist, not which isolation level you're getting by default.
Believing ACID is an exclusively relational, SQL-only property
Why: Many modern NoSQL and distributed engines now offer ACID transactions too, at least within a single document or across a limited scope — ACID is a set of guarantees any engine can choose to provide, not a label tied only to SQL databases.
Fix: Check what specific ACID guarantees a given engine actually provides, rather than assuming based on its NoSQL/relational label.
Interview Questions
What does each letter in ACID stand for?
Atomicity (all-or-nothing), Consistency (constraints always hold), Isolation (concurrent transactions don't see each other's half-finished work), and Durability (committed changes survive a crash).
How do Atomicity and Durability work together to survive a crash in the middle of a transaction?
Before any change is applied to the actual data, it's first written to a Write-Ahead Log on disk. If the system crashes mid-transaction, recovery replays the log: any transaction that never reached a logged COMMIT is rolled back entirely (Atomicity), and any transaction that did commit before the crash is guaranteed to be re-applied from the log (Durability) — the two guarantees rely on the same underlying logging mechanism.
You inherit a system where a 'transaction' only wraps some of a multi-step operation, and a partial failure once corrupted data. Which ACID guarantee was actually violated?
Atomicity — the transaction boundary didn't cover every statement that needed to succeed or fail together, so a failure partway through left some of the operation's effects applied and others not. The fix is widening the transaction boundary to include every statement whose success is genuinely tied together, not adding more error-handling around the existing narrow boundary.
Production Best Practices
Do
✓Draw the transaction boundary around every statement that must succeed or fail together — not just some of them.
✓Know your engine's default isolation level explicitly, rather than assuming full isolation from the word 'ACID' alone.
✓Rely on real schema constraints (foreign keys, CHECK) to help the engine enforce Consistency, don't assume application code alone will catch every case.
Don't
✗Don't confuse ACID's Consistency with CAP's Consistency — they describe different things.
✗Don't assume every engine offering 'ACID transactions' provides them at the same scope (single-row vs multi-document vs cross-shard).
✗Don't leave a multi-step operation as separate, unwrapped statements just because each one individually succeeds most of the time.
Comparison
| What It Guarantees | What Breaks Without It | |
|---|---|---|
| Atomicity | All-or-nothing execution | Partial writes left behind by a crash mid-operation |
| Consistency | Constraints always hold | Invalid data slips through (e.g. a negative balance) |
| Isolation | No half-finished state visible | A concurrent read sees a transaction's in-progress work |
| Durability | Commits survive a crash | A committed change vanishes after a crash |