What you'll learn
Quick Answer
Atomicity means all or nothing. Consistency means constraints always hold. Isolation means concurrent transactions do not corrupt each other. Durability means committed data survives a crash.
A — Atomicity: all or nothing
The classic case is a bank transfer: debit one account, credit another. If the system fails between the two, money has vanished.
Atomicity means the transaction happens completely or not at all:
BEGIN;
UPDATE acct SET bal = bal - 200 WHERE name = 'A';
-- crash here
UPDATE acct SET bal = bal + 200 WHERE name = 'B';
COMMIT;Running that with a failure before the commit, then rolling back:
-- after ROLLBACK
SELECT name, bal FROM acct;
-- [('A', 1000), ('B', 500)] -- unchangedThe partial debit was discarded entirely. No manual repair, no reconciliation script. Without atomicity you would need application code to detect and undo half-finished work — which is exactly what people write when they use a system that lacks transactions.
C — Consistency: the rules always hold
A transaction moves the database from one valid state to another. Every constraint that held before must hold after.
If a foreign key requires every enrolment to reference a real student, no transaction can leave an enrolment pointing at a deleted one. If a CHECK requires marks between 0 and 100, no transaction can commit 150.
The nuance worth knowing: consistency is largely your responsibility, expressed through constraints. The database enforces the rules you declare. If you never declare that a balance cannot go negative, no ACID property will stop it.
This is why it is the least discussed of the four in interviews — it depends on schema design rather than on database machinery. It is also why declaring constraints rather than checking in application code matters: a constraint cannot be bypassed by a script someone runs manually.
I — Isolation: concurrent transactions do not corrupt each other
The hardest of the four, and where most real bugs live.
Two transactions running simultaneously should not see each other's half-finished work. Without isolation you get named failure modes:
- Dirty read — reading data another transaction has written but not committed, which may be rolled back.
- Non-repeatable read — reading the same row twice in one transaction and getting different values, because another transaction committed in between.
- Phantom read — running the same query twice and getting different rows, because another transaction inserted some.
- Lost update — two transactions read a value, both modify it, and one overwrites the other. The classic "seat booked twice" bug.
Databases offer isolation levels trading safety against concurrency: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. Higher levels prevent more anomalies and allow less parallelism.
Most databases default to READ COMMITTED or REPEATABLE READ, not full serialisability. So the default does not prevent every anomaly, which is why a booking system needs explicit handling — see final year project ideas, where double-booking is the standard example.
D — Durability: committed means committed
Once a transaction commits, the data survives a crash, a power failure or a process being killed. The database does not respond "committed" until the change is recorded somewhere that survives restart.
The mechanism is usually a write-ahead log: changes are appended to a log on disk and flushed before the commit is acknowledged. The actual data pages may be written later — if the system crashes in between, recovery replays the log.
Durability has a real cost, which is why it is sometimes weakened deliberately. Flushing to disk on every commit is slow, so some systems allow a small window where commits are acknowledged before being fully flushed, trading a possible loss of the last few transactions for substantially higher throughput. Knowing that this setting exists is worth more than knowing the definition.
Why NoSQL databases talk about BASE instead
ACID is straightforward on one machine. Across many machines it becomes expensive, because guaranteeing every node agrees before acknowledging a write requires coordination — and coordination costs latency and availability.
Many distributed systems therefore offer BASE — Basically Available, Soft state, Eventually consistent. Writes are accepted quickly and propagate, so different nodes can briefly disagree. That is acceptable for a social feed and unacceptable for a bank balance.
This connects directly to the CAP theorem, and it is the reason "which database should I use" is really a question about what your data can tolerate.
A common misconception worth correcting: several modern NoSQL databases, including MongoDB, do support multi-document ACID transactions now. The old "SQL is ACID, NoSQL is not" framing is out of date.