Transactions & ACID
All or nothing — plus the isolation levels that decide which anomalies you're allowing.
50 minDifficulty 4/5database · reliabilityAI-writtenWritten by a model on 16 August 2026 and not yet read by a person. Checked automatically: schema, the pedagogical rules the hand-written material is held to, and every diagram parsed for real.
Before this
Why this exists
Transferring $100 between two accounts requires two writes: debit one, credit the other. If the server crashes after the debit but before the credit, $100 has vanished — deducted from one account, never added to the other. A transaction wraps both writes into a single, indivisible unit: either BOTH happen, or NEITHER does, with no possibility of the crash landing exactly in between.
The mental model
ACID is four separate promises a database makes about transactions: Atomicity (all-or-nothing), Consistency (the database moves between valid states, never a broken intermediate one), Isolation (concurrent transactions don't interfere with each other's intermediate states), and Durability (once committed, a transaction survives a crash). Each letter protects against a DIFFERENT specific failure mode.
How it works
Atomicity: a transaction either fully commits or fully rolls back
If any statement within a transaction fails — a constraint violation, a crash mid-transaction — every change made so far within that SAME transaction is undone, as if none of it ever happened. There's no partial state where the debit succeeded but the credit didn't; atomicity guarantees exactly one of those two outcomes, never a mix.
Isolation levels trade consistency guarantees for concurrency performance
At the strictest isolation level (SERIALIZABLE), concurrent transactions behave AS IF they ran one after another, with zero interference — the safest option, at a real performance cost from the extra locking or conflict-checking required. Weaker levels (READ COMMITTED, the common default) allow certain anomalies — like reading a value that a concurrent, not-yet-committed transaction changed — in exchange for better throughput.
A dirty read is what weaker isolation can expose, and it's exactly as dangerous as it sounds
Under a weak isolation level, transaction A can read a value transaction B has written but NOT YET COMMITTED — if B then rolls back, A has now acted on data that, in the end, never actually existed. This 'dirty read' is prevented by READ COMMITTED and stricter levels, but is technically possible under the weakest level, READ UNCOMMITTED.
Durability means committed really means committed, even through a crash
Once a transaction returns success to the caller, the database guarantees that data survives — a power outage or crash the very next instant does NOT lose it, typically because the change was written to a durable log on disk before the commit was acknowledged. This is the specific promise that lets an application trust a 'success' response without needing its own separate confirmation that the data really persisted.
The mechanism
A transaction begins, accumulating a series of changes without making them visible to other transactions yet. If every statement succeeds and the transaction commits, all changes become durably persisted and visible atomically, as one unit. If any statement fails, or the transaction is explicitly rolled back, every change made within it is discarded entirely, returning the data to its pre-transaction state as if nothing had happened.
What people get wrong
- Wrapping multiple statements in a transaction automatically makes them run faster.
- A transaction's purpose is correctness (atomicity, isolation), not speed — in fact, stricter isolation levels and longer-held transactions can measurably REDUCE throughput due to locking and conflict-checking overhead. Conflating transactions with performance leads to either avoiding them when they're actually necessary for correctness, or misunderstanding why a heavily-transactional workload is slower than an equivalent one without transactional guarantees.
- The default isolation level (often READ COMMITTED) prevents all possible concurrency anomalies.
- READ COMMITTED specifically prevents dirty reads, but still permits other anomalies like non-repeatable reads (re-reading the same row within a transaction and getting a different value, because another transaction committed a change in between) — only SERIALIZABLE prevents essentially all such anomalies. Assuming the default isolation level provides maximum safety leads to subtle concurrency bugs in code that actually needed a stricter guarantee for its specific correctness requirements.
- A transaction that returns 'success' guarantees the data is safe, full stop, with no further consideration needed.
- Durability is a guarantee about the DATABASE's own storage surviving a crash — it says nothing about, for example, a REPLICA that hasn't yet received the change, or a downstream system that was supposed to be notified but wasn't, both of which are separate concerns beyond the transaction's own durability promise. In distributed systems specifically, 'the transaction committed' and 'every system that needs to know about it now knows' are genuinely different guarantees, and conflating them is a common source of distributed consistency bugs.
When not to use it
- A single statement affects only one row and no other operation depends on it succeeding together with anything else.
- An explicit transaction may not be necessary — most databases already treat a single statement as atomic by default, without needing an explicit BEGIN/COMMIT wrapper.
- The workload is extremely read-heavy and can tolerate slightly stale reads in exchange for significantly better throughput.
- A weaker isolation level (READ COMMITTED, or even READ UNCOMMITTED for genuinely tolerant use cases) rather than SERIALIZABLE, trading some consistency guarantees for real performance gains where the tradeoff is acceptable.
Terms
- Atomicity
- — The guarantee that a transaction's changes either all take effect or none do, with no partial application possible.
- Isolation level
- — A configurable setting determining how much concurrent transactions can affect or see each other's uncommitted or intermediate state, trading consistency for performance.
- Dirty read
- — Reading data written by a concurrent, not-yet-committed transaction, which may later be rolled back, making the read data never have truly existed.
- Durability
- — The guarantee that a committed transaction's changes survive a subsequent crash or power failure.
In an interview
Why might a bank transfer implemented as two separate, non-transactional UPDATE statements lose money during a server crash?
- without a transaction wrapping both statements, a crash between the debit and the credit leaves the system in a partial, inconsistent state
- the debit has already been committed and persisted, but the credit never happened, permanently losing the transferred amount
- wrapping both statements in a single transaction (atomicity) ensures either both happen or neither does, with no possibility of this partial state
Can you recall it?
What specific guarantee does atomicity provide for a multi-statement transaction, and what failure does it prevent?