ACID Transactions
Updated June 3, 2026Imagine you're transferring $500 from your savings account to your checking account. The bank's system debits the savings account. Before it can credit the checking account, the server crashes.
What happens to your $500?
In a system without ACID transactions, it could vanish into the void. With ACID, the entire transfer either completes or it never happened. That's what ACID guarantees: your data stays sane even when things go wrong.
The Four Properties
ACID stands for Atomicity, Consistency, Isolation, and Durability. Let's go through each one with the bank transfer as our running example.
Atomicity: All or Nothing
A transaction is a single unit of work. Either every operation in it succeeds, or none of them do. There's no in-between.
In our bank transfer, the transaction contains two operations:
- Debit $500 from savings
- Credit $500 to checking
If the server crashes after step 1, atomicity ensures the debit is rolled back. You don't lose money. The database acts as if the transaction never started.
Think of it like cutting a piece of paper. You either cut it or you don't. There's no "half cut" that persists.
A bank transfer debits a savings account and then the server crashes before crediting the checking account. Which ACID property ensures the debit is rolled back?
Consistency: Valid State to Valid State
Consistency means the database only ever transitions from one valid state to another. Any constraints, rules, or invariants you've defined must hold before and after every transaction.
For example: the rule "no account balance can go negative." If your savings only has $300 and you try to transfer $500, the consistency constraint rejects the transaction before it runs.
Or consider a more subtle constraint: the total money in the system is constant. Before the transfer: savings has $1000, checking has $200 — total $1200. After the transfer: savings has $500, checking has $700 — total still $1200. Consistency enforces this invariant.
Consistency in ACID is slightly different from the C in CAP theorem. CAP's consistency means all nodes see the same data. ACID's consistency means data invariants are preserved.
Consistency in ACID means the database enforces rules like "no negative balance." This is the same concept as Consistency in the CAP theorem.
Isolation: Transactions Don't Step on Each Other
Isolation is about concurrency. When multiple transactions run at the same time, they shouldn't interfere with each other. Each transaction should run as if it's the only one executing.
Here's the problem without isolation: two people both try to withdraw $500 from an account that only has $600. Without isolation, both reads might see $600, both decide the withdrawal is valid, and both succeed — leaving the account at -$400.
With proper isolation, the second transaction either sees the balance after the first transaction committed (and fails because $100 < $500), or it waits for the first transaction to finish.
Isolation is actually a spectrum, not a binary. More on that shortly.
Two users simultaneously withdraw $500 from an account with a $600 balance. Without proper isolation, both withdrawals may succeed, leaving the account at -$400. Which read anomaly does this scenario most directly relate to?
Durability: Committed Means Committed
Once a transaction is committed, it stays committed. Even if the server crashes immediately after, even if the power goes out, even if the hard drive partially fails — the data is there.
Databases achieve this through write-ahead logging (WAL): before applying any change to the data files, the database writes the change to an append-only log. If the server crashes mid-write, it replays the log on restart and finishes the job.
In our bank example: the moment the transfer commits and you get a "Transaction Successful" confirmation, that data is on disk. Not in memory. Not "almost saved." On disk.
How do databases guarantee Durability — that a committed transaction survives a server crash immediately after the commit?
Isolation Levels — The Spectrum
Here's where it gets nuanced. Full isolation is expensive — it requires locking rows or entire tables to prevent concurrent access. So databases let you dial down the isolation level in exchange for more concurrency. The weaker the isolation, the more anomalies you're exposed to.
The Anomalies
There are three classic read anomalies you need to understand:
Dirty Read: Reading data that hasn't been committed yet. Transaction A updates a balance to $0 but hasn't committed. Transaction B reads $0. Transaction A rolls back. B made a decision based on data that never officially existed.
Non-Repeatable Read: You read a value, another transaction modifies and commits it, and when you read again in the same transaction, the value has changed. You asked "what's the balance?" twice and got two different answers.
Phantom Read: You run a query and get a set of rows. Another transaction inserts new rows that match your query's condition. You run the same query again and get more rows. Rows appeared that weren't there before.
The Four Isolation Levels
| Isolation Level | Dirty Reads | Non-Repeatable Reads | Phantom Reads |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible |
| Serializable | Prevented | Prevented | Prevented |
Read Uncommitted is basically no isolation at all. You can read data from in-progress transactions. Almost never used in practice.
Read Committed is the default in PostgreSQL and most SQL databases. You only see data that has been committed. Dirty reads are gone, but if you read the same row twice in a transaction, you might get different values.
Repeatable Read guarantees that if you read a row once in a transaction, subsequent reads return the same value — even if another transaction commits a change. MySQL's default for InnoDB. Note: you can still get phantom reads (new rows appearing).
Serializable is the strictest level. Transactions execute as if they ran one after another, in serial order. No anomalies possible. But it's the most expensive — databases typically use either strict locking or snapshot-based conflict detection (SSI) to achieve it.
Which isolation level prevents dirty reads and non-repeatable reads, but still allows phantom reads?
Real-World Usage
Banking, payments, e-commerce checkouts → Always use Serializable or at minimum Repeatable Read. You cannot afford dirty reads or phantom rows when money is moving.
Social media feeds, analytics dashboards → Read Committed is fine. Slight inconsistency is acceptable; nobody cares if a like count is off by one.
Inventory management → Repeatable Read or Serializable. Overselling products (phantom reads allowing two users to buy the last item) is a real business problem.
Example: PostgreSQL Transaction
BEGIN;
-- Debit savings account
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 'savings-001' AND balance >= 500;
-- If no rows were updated, the balance was insufficient
-- Application checks affected rows and rolls back if needed
-- Credit checking account
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 'checking-001';
COMMIT;The AND balance >= 500 check in the debit ensures we don't go negative. The whole thing is wrapped in a transaction — if anything fails between BEGIN and COMMIT, Postgres rolls it all back automatically.
Serializable isolation is the safest choice for every application, so there is no reason to use a weaker isolation level.
The Cost of ACID
Nothing is free. ACID guarantees require:
- Locking: Rows or tables may be locked during transactions, blocking concurrent writes.
- Logging: The WAL adds write overhead to every committed transaction.
- Coordination: Distributed ACID across multiple database nodes requires consensus protocols like 2PC or Paxos, which add latency.
This is why many NoSQL systems trade away ACID in exchange for write throughput and horizontal scalability. Cassandra, for instance, offers configurable consistency but not full ACID. DynamoDB added transactions later as an optional feature with additional cost.
But for anything involving money, healthcare records, or critical state — ACID isn't optional. It's the baseline.
Summary
ACID transactions are the foundation of reliable data storage. Atomicity ensures all-or-nothing execution. Consistency enforces data invariants. Isolation prevents concurrent transactions from corrupting each other. Durability means committed data survives failures. Isolation exists on a spectrum — from the free-for-all of Read Uncommitted to the total ordering guarantee of Serializable — and the right level depends on whether your application can tolerate read anomalies like dirty reads, non-repeatable reads, or phantom rows. For any system handling money or critical state, ACID isn't a nice-to-have. It's non-negotiable.
How helpful was this content?
Comments
Sign in to join the discussion
Saved on this device only
Sign in to sync progress across devices