Transactions and Isolation
also called ACID Isolation Levels
What a database guarantees when concurrent transactions touch the same data — and the anomalies each level permits.
Definition
Isolation defines what one transaction can see of another's in-flight work. Weaker levels permit specific anomalies in exchange for concurrency.
| Level | Permits | Typical use |
|---|---|---|
| Read uncommitted | Dirty reads | Effectively never |
| Read committed | Non-repeatable reads, phantoms | Default in most engines; fine for most work |
| Repeatable read / snapshot | Phantoms (engine-dependent); write skew | Reports needing a consistent view |
| Serializable | Nothing — equivalent to some serial order | Money, inventory, anything with a cross-row invariant |
The anomaly that catches people out
Write skew. Two transactions each read a set of rows, each verify an invariant that holds, and each write a different row. Individually correct; together they violate the invariant.
The classic form: a rule that at least one engineer must remain on call. Two people each check that the other is on call, each remove themselves, and both commits succeed. Nobody is on call. Snapshot isolation permits this; serializable does not.
Business versions are everywhere: two concurrent bookings each checking that capacity remains, two withdrawals each checking a combined balance, two approvals each checking that a limit is not exceeded. Anywhere an invariant spans rows rather than living in one row, snapshot isolation is not sufficient and a unique constraint will not save you either.
Practical guidance
- Know your engine's default, and know that "repeatable read" means different things in different engines.
- Use serializable where money or a hard invariant is involved, and handle serialisation failures by retrying. The retry is part of the pattern, not a workaround.
- Prefer a single-row invariant where you can: a conditional update on a version column, or a constraint the database can enforce, is cheaper and more robust than any isolation level.
- Keep transactions short. A transaction held open across a network call to a third party is a lock held for as long as that third party is slow.
- Never hold a transaction across user think-time. Use optimistic concurrency with a version check instead.
Industry example
Financial systems make the trade explicit. A ledger has a hard invariant — entries must balance, and an account's derived balance must reflect exactly the entries recorded — so the write path is serializable or protected by conditional writes, and the cost in throughput is accepted because the alternative is unexplainable money.
The interesting part is what is not held to that standard in the same system: reporting views, fraud scores, dashboards and search all read replicas or projections and are explicitly eventually consistent. The architecture is not "strongly consistent" or "eventually consistent" — it assigns each path the weakest guarantee that is still correct for its purpose.
Failure scenarios
- Assuming read committed prevents lost updates. Read-modify-write without a version check loses one of two concurrent updates, at every isolation level below serializable.
- Long transactions causing lock queues that present as a latency spike with no obvious cause.
- Retry logic missing on serializable transactions, so legitimate serialisation failures surface as user-facing errors.
- Deadlocks from inconsistent lock ordering across code paths — fixed by always acquiring in a defined order.
Interview question
"Explain write skew with a business example, and tell me three different ways to prevent it."