The short answer
Quick answer: ACID is a set of four guarantees a database makes about transactions, which are groups of operations treated as one unit. Atomicity: all the operations happen, or none do. Consistency: the database moves from one valid state to another, never breaking its rules. Isolation: transactions running at the same time do not interfere with each other. Durability: once a transaction is committed, it survives crashes and power loss. Together they let you write code as if nothing else were happening and nothing could fail halfway.
The classic example
Moving £100 from Alice to Bob takes two updates:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;
Things that could go wrong without ACID:
- The server crashes after the first update. Alice is £100 poorer and Bob got nothing.
- Another process reads the balances between the two updates and sees £100 missing.
- Alice had only £50, and the balance goes negative.
- The transfer "succeeded", then the power failed and it vanished.
Each letter of ACID rules out one of these. The PostgreSQL tutorial on transactions uses the same example.
Atomicity: all or nothing
A transaction either completes entirely or has no effect. If anything fails, such as an error, a constraint violation or a crash, every change is undone (rolled back).
How it is implemented: the database records changes in a log before applying them. On failure, it uses the log to undo incomplete transactions. Some databases keep old row versions instead, so "undo" simply means never making the new versions visible.
Atomicity is about failure, not about concurrency. The name is a little misleading: it does not mean "happens instantly".
Consistency: the rules always hold
The database starts in a valid state and ends in a valid state. "Valid" is defined by the rules you declare:
NOT NULLand data types.UNIQUEand primary keys.- Foreign keys: an order must refer to a customer that exists.
CHECKconstraints:balance >= 0.
If a transaction would break a rule, the database rejects it and rolls back.
Consistency is the odd one out. The other three are properties the database provides; this one is partly your responsibility. The database can only enforce the rules you tell it about. If "the sum of all balances must stay constant" is not expressed as a constraint, your application has to get it right.
Note also that this "C" is unrelated to the "C" in the CAP theorem, which is about replicas agreeing with each other. See the CAP theorem explained.
Isolation: transactions do not step on each other
Many transactions run at once. Isolation means each behaves as if it were running alone. The strictest form, serializable, guarantees the result is the same as if the transactions had run one after another in some order.
Full isolation is expensive, so databases offer weaker isolation levels that allow certain anomalies in exchange for speed:
| Level | Prevents |
|---|---|
| Read uncommitted | Almost nothing |
| Read committed | Reading uncommitted ("dirty") data |
| Repeatable read | The above, plus values changing mid-transaction |
| Serializable | All concurrency anomalies |
Most databases default to read committed, which means a transaction can see different data in two successive reads. This surprises many developers, and it is where most real-world transaction bugs come from.
How it is implemented: with locks, or more commonly with multi-version concurrency control (MVCC), where each transaction sees a consistent snapshot. This is a large topic of its own; see how database transactions handle concurrent users.
Durability: committed means saved
Once the database confirms a commit, the data is safe even if the machine loses power a millisecond later.
How it is implemented: before acknowledging, the database appends the transaction to a write-ahead log and forces it to stable storage with fsync. The actual tables can be updated later; after a crash, the log is replayed. See how write-ahead logs prevent data loss.
Durability has limits worth knowing:
- It protects against a crash, not against losing the disk. That takes replication and backups.
- Some systems let you relax it for speed, for example flushing the log once a second. A crash can then lose the most recent second of commits.
- It depends on hardware and operating systems honestly reporting that data reached the disk.
ACID vs BASE
Guaranteeing ACID across many machines is hard and slow. Many early NoSQL systems chose a different trade-off, nicknamed BASE:
- Basically Available: the system answers, even during failures.
- Soft state: data may change over time without new input, as replicas catch up.
- Eventually consistent: replicas converge if updates stop.
| ACID | BASE | |
|---|---|---|
| Priority | Correctness | Availability and scale |
| After a write | Everyone sees it | Replicas may briefly disagree |
| Typical systems | PostgreSQL, MySQL, Oracle, SQL Server | Cassandra, DynamoDB (by default), Riak |
| Suited to | Money, inventory, bookings | Feeds, counters, carts, analytics |
The divide has narrowed. Many NoSQL databases now offer transactions, and distributed SQL databases such as Spanner and CockroachDB provide ACID across regions. See SQL vs NoSQL and eventual consistency explained.
What "ACID compliant" does not tell you
The label is a starting point, not a guarantee of safety:
- Check the default isolation level. It is rarely serializable.
- Check durability settings. Defaults differ, and managed services sometimes relax them.
- Transactions end at the database's edge. ACID cannot cover a payment API call or an email sent inside your transaction. If the database rolls back, the email is still sent. Patterns such as idempotency keys and outbox tables bridge that gap.
- Independent testing matters. The Jepsen project has found many databases falling short of their documented guarantees under failure.
Frequently asked questions
What is a database transaction?
A group of one or more operations that the database treats as a single unit: either all of them take effect or none do.
Is ACID only for SQL databases?
No. It is a set of guarantees, not a data model. Several document and key-value databases now support ACID transactions.
Does ACID make a database slow?
It adds overhead, mainly from flushing the log on commit and managing concurrency. For most applications that cost is small compared with the cost of corrupted data.
What happens to a transaction if the database crashes?
On restart, committed transactions are replayed from the log and incomplete ones are rolled back, so the data returns to a consistent state.
Conclusion
ACID is a promise that a database will behave sanely when things go wrong and when many things happen at once. Atomicity handles failure, isolation handles concurrency, durability handles crashes, and consistency ties them to your business rules. Use transactions for anything that must be correct, and know which isolation level you are really running at.
Related articles
- How Database Transactions Handle Two Users Editing at Once
- How Write-Ahead Logs Prevent Data Loss During Crashes
- SQL vs NoSQL: When to Use Which (Beyond the Hype)
- How Payment Systems Avoid Charging You Twice (Idempotency)
Sources and further reading
