A transaction is a group of database operations that should succeed or fail as a unit. Transfer $100 from Account A to Account B? That’s two operations (debit A, credit B) — and we never want only one of them to happen.
ACID is the set of guarantees that make transactions reliable. Let’s break each one down.
The ACID Properties
Atomicity — All or Nothing
Think of it like a light switch: it’s either ON or OFF. There’s no half-ON. If any operation in a transaction fails, all operations are rolled back. The database looks like the transaction never started.
Real-world analogy: We’re transferring money. Debit Account A by $100, credit Account B by $100. If the credit fails, the debit is undone. We never lose $100 into thin air.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;
-- If anything fails between BEGIN and COMMIT, everything rolls backConsistency — Rules Are Always Respected
After every transaction, the database must be in a valid state. All constraints, foreign keys, and rules we defined must hold.
Real-world analogy: If we have a rule that account balance can never go negative, the database will reject any transaction that violates it. We can’t transfer $500 from an account with $300.
The only difference from the other properties: consistency is partly the application’s responsibility. The database enforces constraints we define, but our application logic also needs to make sense.
Isolation — Transactions Don’t Peek at Each Other
Even when multiple transactions run at the same time, each one behaves as if it’s the only one running. One transaction’s uncommitted changes are invisible to other transactions.
Real-world analogy: Two people at different ATMs both checking the same account balance. Neither should see the other’s half-finished transaction.
This is the trickiest property. Full isolation is expensive, so databases offer different isolation levels (more on this below).
Durability — Committed Means Permanent
Once the database says “committed,” the data survives power outages, crashes, and panics. It’s written to non-volatile storage (disk), not just sitting in memory.
Real-world analogy: Once we get the receipt, the transaction is done. Even if the bank’s computers crash right after, our money is safe.
Databases achieve this by writing to a write-ahead log (WAL) before making changes. Even if the system crashes mid-write, the WAL can replay and recover.
Transaction Isolation Levels
Full isolation (Serializable) is safe but slow. So databases give us four levels to choose from, each trading safety for speed.
Read Uncommitted (Weakest)
A transaction can see another transaction’s uncommitted changes. This is almost never used because it allows dirty reads — we might read data that gets rolled back.
Read Committed
We can only see data that has been committed. No dirty reads. This is the default in PostgreSQL and most databases.
But we can still have a non-repeatable read: we read a row, another transaction modifies and commits it, and when we read it again within the same transaction, we get a different value.
Repeatable Read
Once we read a row, we’ll always see the same value for the rest of our transaction, even if another transaction changes it. No dirty reads, no non-repeatable reads.
But we can still get phantom reads: we run a query and get 10 rows. Another transaction inserts a new row that matches our query. If we run the same query again, we get 11 rows. The new row is a “phantom.”
Serializable (Strongest)
Transactions behave as if they ran one after another, in sequence. No dirty reads, no non-repeatable reads, no phantom reads. Complete safety.
The trade-off? It’s the slowest level. The database may need to lock rows, detect conflicts, or abort and retry transactions.
Read Anomalies Cheat Sheet
| Anomaly | What happens | Prevented by |
|---|---|---|
| Dirty read | Read uncommitted data that gets rolled back | Read Committed+ |
| Non-repeatable read | Same row returns different values in same transaction | Repeatable Read+ |
| Phantom read | New rows appear in a repeated query | Serializable |
When ACID Matters vs When We Can Relax
ACID is critical for:
- Financial systems (bank transfers, payments)
- Inventory management (don’t oversell)
- Booking systems (don’t double-book a seat)
- Anything where wrong data = real money lost
We can relax for:
- Social media feeds (seeing a post 2 seconds late is fine)
- Analytics and reporting (slightly stale data is acceptable)
- Caching layers (eventual consistency is OK)
- Logging and metrics (losing one log line isn’t the end of the world)
This is the ACID vs BASE trade-off we mentioned in the SQL vs NoSQL topic. Many NoSQL databases choose availability and partition tolerance (BASE) over strict ACID, because for their use cases, it’s the right call.
In simple language, ACID is a promise from the database: “Your transaction will either happen completely and correctly, or not at all. And once it’s done, it’s done forever.” The isolation levels let us tune how strict that promise is, depending on how much speed we need.