Isolation levels

How can two transactions that are each correct combine into a result neither would produce alone?

Here is a rule an on-call system must enforce: at least one engineer must always remain on duty. So the code that removes someone counts how many are currently on duty, and refuses if the count is one.

Two engineers, Ana and Ben, both on duty, both tired, both click "go off duty" at the same instant. Each transaction counts two, each concludes that removing one is safe, each removes one. Nobody is on duty. Read either transaction on its own and it is plainly correct. Run them together and the invariant they were written to protect is broken.

Nothing was wrong with either program. So where exactly did the correctness go?

Look closely at what each transaction assumed. Ana's transaction counted two and then acted on that count. Between the counting and the acting, it assumed the count was still true. Nothing enforced that assumption — and Ben's transaction falsified it in the gap.

So the failure is not in either program's logic. It is in the gap, and specifically in a promise nobody made: that what you read at the start of a transaction is still true when you write at the end. That promise is what we mean by isolation.

Now, the strongest possible version of the promise is easy to state: run transactions as though one at a time, in some order. That is serializability, and under it our anomaly is impossible — in any serial order, the second transaction counts one and refuses.

Then why is that not simply the default everywhere? Because keeping the promise costs coordination. To guarantee that nothing you read changed, the system must either hold locks on everything read until you commit — blocking other work — or track what everyone read and abort transactions whose assumptions were violated. Both reduce throughput; the second turns some failures into retries the application must handle.

So databases offer weaker promises, each of which admits specific anomalies in exchange for less coordination. And here is the thing worth understanding, because it is where most confusion lives: the levels are defined by which anomalies they permit, not by how they are implemented.

Walk up the ladder. Read uncommitted lets you see another transaction's uncommitted writes — a dirty read, which may then be rolled back, leaving you having acted on a fact that never happened. Read committed forbids that, and is the default in PostgreSQL, Oracle and SQL Server; you only ever see committed data. But each statement gets a fresh view, so reading the same row twice in one transaction can return two values — a non-repeatable read. Repeatable read pins your view so that re-reading a row gives the same answer. Yet it still permits something subtler: you re-run a query and find rows that were not there before, because the new rows were never read by you and so were never protected. That is a phantom.

Now return to Ana and Ben with this vocabulary, because their anomaly is none of those three. Neither read anything the other wrote. Each read a set, each wrote a different row, and the two writes together broke a constraint spanning both. This is write skew, and it is the characteristic failure of snapshot isolation — the level many systems ship as "repeatable read". Snapshot isolation gives each transaction a consistent frozen view and prevents dirty, non-repeatable and most phantom reads; it does not prevent two transactions from making disjoint changes that are jointly invalid, precisely because neither one conflicts with the other on any single row.

That is why the anomaly is disturbing. It is not caused by seeing something wrong. It is caused by two transactions each acting truthfully on a premise the other quietly removed.

One caution about naming: the SQL standard's level names are notoriously loose, and vendors implement them differently. PostgreSQL's "repeatable read" is snapshot isolation; its "serializable" uses serializable snapshot isolation, which detects the dangerous read-write patterns and aborts one transaction. Reading the vendor's own documentation is not pedantry here — the same level name genuinely means different guarantees in different engines.

The refinement that matters most: isolation is a promise about what you read, not about whether your write succeeds. Locking a row you intend to write protects that row. It does nothing for the rows you counted to decide whether writing was allowed — and in write skew, those are entirely different rows. This is why "we use transactions" is not by itself an answer to a concurrency question. The relevant question is which level, and which anomalies it admits.

The practical toolkit follows from that. You can raise the level to serializable and be prepared to retry aborted transactions. You can take an explicit lock on the rows the decision depends on, so the premise is protected as well as the conclusion. You can materialize the constraint into something the database can enforce directly — a row representing "the on-duty count", which both transactions must now write, converting an invisible conflict into an ordinary one. Or you can decide the anomaly is rare and harmless. All four are legitimate; only choosing none of them by not noticing is not.

• Serializable snapshot isolation: how conflicts are detected after the fact rather than prevented.
• Two-phase locking, and why it trades throughput for a simpler guarantee.
• Why distributed systems make this harder still, and what "consistency" means there by contrast.

Every term the collection defines is gathered in the glossary.