What do the ACID properties mean, and how does each one protect your data?
Atomicity ensures a transaction either commits fully or rolls back entirely — no partial updates. Consistency ensures every committed transaction leaves the database in a valid state that satisfies all defined constraints. Isolation ensures concurrent transactions do not see each other's intermediate state. Durability ensures a committed transaction survives crashes because its changes are flushed to non-volatile storage.
How to think about it
The interviewer wants to know two things: can you define each letter, and can you explain what actually breaks when a property is violated? Reciting the acronym isn’t enough — walk through a concrete scenario for each one.
Atomicity — all or nothing
Picture a bank transfer: debit one account, credit another. If the database crashes between those two operations, you’ve destroyed money. Atomicity prevents that by treating both writes as a single unit — either both land, or neither does.
The mechanism is the write-ahead log (WAL): changes are recorded in the log before they touch data pages. On crash, the database replays committed transactions and rolls back any that never committed.
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;
-- If the second UPDATE fails, ROLLBACK undoes the first automatically.
-- You will never see $500 vanish from account 1 without arriving at account 2.Consistency — constraints always hold
Consistency means the database enforces its own rules on every commit. NOT NULL, UNIQUE, CHECK constraints, and foreign keys are the enforcers. A transaction that would break any of them is rolled back before it ever commits — the database never lands in a structurally invalid state.
-- This entire transaction is rolled back if the CHECK constraint fires
BEGIN;
INSERT INTO orders (id, amount) VALUES (99, -50);
-- amount CHECK (amount > 0) fires → rollback
COMMIT;Isolation — concurrent transactions do not interfere
This is the most nuanced property. At the default READ COMMITTED level, a transaction sees only data that was already committed when it reads — it never sees another session’s in-progress writes.
-- Session A
BEGIN;
UPDATE inventory SET qty = qty - 1 WHERE sku = 'A001';
-- Not yet committed
-- Session B — sees the original qty, not A's uncommitted change
SELECT qty FROM inventory WHERE sku = 'A001';There are four isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) that trade consistency for concurrency. Interviewers often follow up asking which anomaly each level prevents.
Durability — committed data survives crashes
Once COMMIT returns, the data is on disk. The database syncs the WAL via fsync before acknowledging the commit. If the server loses power the next millisecond, a restart replays the WAL and the committed transaction is fully present.
Setting synchronous_commit = off in PostgreSQL trades this guarantee for lower write latency — you can lose the last few committed transactions on a hard crash.
What breaks each property in practice
| Property | Common violation |
|---|---|
| Atomicity | Application crash mid-transaction without proper error handling |
| Consistency | Bypassing constraints via bulk load tools (COPY ... WITH (DISABLE TRIGGERS)) |
| Isolation | Using READ UNCOMMITTED where dirty reads occur |
| Durability | synchronous_commit = off, or a filesystem that does not honor fsync |