ACID Properties Cheat Sheet
The four ACID guarantees, SQL transaction isolation levels, and the concurrency anomalies each isolation level prevents or allows.
1 PageIntermediateMar 18, 2026
The Four Properties
What ACID actually guarantees.
- Atomicity- a transaction's operations either all succeed or all roll back together
- Consistency- a transaction moves the database from one valid state to another
- Isolation- concurrent transactions don't observe each other's uncommitted changes
- Durability- once committed, changes survive crashes, power loss, and restarts
Atomicity in Practice
A transfer that must succeed or fail as a unit.
sql
BEGIN TRANSACTION;UPDATE accounts SET balance = balance - 100 WHERE id = 1;UPDATE accounts SET balance = balance + 100 WHERE id = 2;-- if either statement fails, the whole transaction rolls backCOMMIT;-- or: ROLLBACK;
Isolation Levels (weakest to strongest)
Standard SQL isolation levels.
- READ UNCOMMITTED- can see other transactions' uncommitted changes (dirty reads)
- READ COMMITTED- only sees committed data; default in Postgres, Oracle, SQL Server
- REPEATABLE READ- a query returns the same rows all transaction long; MySQL InnoDB default
- SERIALIZABLE- transactions behave as if run one at a time; strongest, most contention
Anomalies Isolation Prevents
Concurrency problems isolation levels guard against.
- Dirty read- reading another transaction's uncommitted data
- Non-repeatable read- re-reading a row returns different data after another commit
- Phantom read- re-running a query returns new or missing rows after another commit
- Lost update- two transactions overwrite each other's changes because neither saw the other's write
MVCC Snapshot Reads
How Postgres serves REPEATABLE READ without blocking writers, via row versions.
sql
-- Session ABEGIN ISOLATION LEVEL REPEATABLE READ;SELECT balance FROM accounts WHERE id = 1; -- sees balance = 500, takes a snapshot (xmin/xmax)-- Session B (concurrently)BEGIN;UPDATE accounts SET balance = 400 WHERE id = 1;COMMIT; -- creates a new row version, old version kept for A's snapshot-- Session A (same transaction)SELECT balance FROM accounts WHERE id = 1; -- still 500, reads its original snapshotCOMMIT;-- Postgres never blocks readers on writers or vice versa: each transaction-- reads the row version visible as of its snapshot (xmin <= snapshot < xmax).
How Databases Actually Implement Isolation
REPEATABLE READ and SERIALIZABLE mean different things depending on the engine.
- Postgres REPEATABLE READ- implemented as snapshot isolation via MVCC; prevents dirty/non-repeatable/phantom reads but still allows write skew
- Postgres SERIALIZABLE- Serializable Snapshot Isolation (SSI); detects dangerous read-write dependency cycles at commit time and aborts one transaction
- MySQL InnoDB REPEATABLE READ- uses next-key locking (record + gap locks) in addition to MVCC, so it actually blocks some phantom inserts that pure snapshot isolation would allow
- Oracle read consistency- every statement (not just transaction) gets a consistent read snapshot built from undo segments; Oracle has no true dirty-read level
- SQL Server SNAPSHOT- opt-in isolation level using row versioning in tempdb; distinct from the lock-based SERIALIZABLE level
- CockroachDB / Spanner SERIALIZABLE- the only isolation level offered; achieved via distributed timestamp ordering (e.g., TrueTime) rather than single-node locking
Write Skew Under Snapshot Isolation
An anomaly REPEATABLE READ/SNAPSHOT does NOT prevent, unlike true SERIALIZABLE.
sql
-- Rule: at least one doctor must be on call at all times.-- Two on-call doctors, both try to go off-call concurrently.-- Session ABEGIN ISOLATION LEVEL REPEATABLE READ;SELECT count(*) FROM doctors WHERE on_call = true; -- sees 2UPDATE doctors SET on_call = false WHERE id = 1;COMMIT;-- Session B (started before A committed, same snapshot)BEGIN ISOLATION LEVEL REPEATABLE READ;SELECT count(*) FROM doctors WHERE on_call = true; -- also sees 2UPDATE doctors SET on_call = false WHERE id = 2;COMMIT;-- Result: zero doctors on call, even though each transaction checked the-- invariant. Neither transaction wrote a row the other read, so snapshot-- isolation sees no conflict. Only true SERIALIZABLE (SSI) catches this.
Durability Knobs: WAL & fsync
Trading durability guarantees for throughput at the Postgres config level.
sql
-- synchronous_commit controls how durable a COMMIT actually is:SHOW synchronous_commit; -- default 'on': fsync WAL before COMMIT returns-- 'off' returns immediately after writing to the WAL buffer; a crash within-- ~wal_writer_delay can lose the last few commits, but corruption never happensSET synchronous_commit = off;-- Per-transaction override for a batch job that can tolerate replay on crashBEGIN;SET LOCAL synchronous_commit = off;INSERT INTO event_log (payload) VALUES ('...');COMMIT;-- Group commit: Postgres batches concurrent fsyncs into one disk flush,-- so durability under load is closer to O(1) fsyncs, not O(n) transactions.
ACID vs. BASE
The trade-off distributed NoSQL systems make instead of strict ACID.
- Basically Available- the system guarantees availability over strict consistency, typically via replication
- Soft state- replica state may not be consistent at every point in time without input
- Eventually consistent- given no new writes, all replicas converge to the same value, but with no bound on when
- Tunable consistency- systems like Cassandra let you choose per-query (QUORUM, ONE, ALL) rather than picking BASE or ACID globally
- Compensating transactions (sagas)- replace atomicity across services with a sequence of local transactions plus explicit undo steps on failure
Pro Tip
Durability doesn't mean the data files are written instantly — it typically means the change is recorded in a write-ahead log that's fsync'd before COMMIT returns, so crash recovery can replay it even if the data files lag behind.
Was this cheat sheet helpful?
Explore Topics
#ACIDProperties#ACIDPropertiesCheatSheet#Database#Intermediate#TheFourProperties#AtomicityInPractice#Isolation#Levels#Databases#Concurrency#CheatSheet#SkillVeris
