Isolation levels: the price of SERIALIZABLE is retries, not a slow query

An isolation level answers one question: what will my transaction see while others are working next to it. It is usually taught from the standard's table — four levels, three anomalies, ticks. The table is real, but it is read wrongly.

Before arguing with the table it is worth seeing once what it is made of. An anomaly is not a wrong query but a wrong order: each command on its own is legal, yet together they produce a result no order of the shape "one whole transaction, then the other" could have produced. Here are four such interleavings — the same ones as in the measurement — and they can be played out step by step:

Switching the level on one and the same interleaving also shows what a level answers with. The database has two ways: a snapshot — the transaction reads the database as it was at the start, and somebody else's write does not enter it; and a refusal — the database rejects the transaction with the code 40001 and offers a retry. The first closes the two upper scenarios, the second the two lower ones, and the whole price of SERIALIZABLE that the article goes on to measure is the price of the second.

The table is about prohibitions, not about your database

The table records what a level may not allow. What it actually allows is a question for the specific database, and the answer can differ in either direction.

It can be stricter: PostgreSQL does not allow a phantom at REPEATABLE READ, though the standard would permit one. The level is made from a snapshot — the transaction sees the database as it was when it began, and new rows simply do not appear for it.

Or it can allow something the table never lists.

The anomaly that is not in the table

Two doctors on call. The rule: at least one must remain.

Each opens the app; it checks — two are on call, so leaving is fine. Both press "leave" at the same moment. Each transaction on its own is impeccable: it checked, saw two, removed one. Neither touched the other's row, so the database saw no conflict.

Nobody is left on call.

This is called write skew, and the standard's table has no such row. Not because the reader missed it, but because the standard's list of anomalies is incomplete — a point made in the 1995 paper that described write skew in the first place.

The rule worth remembering instead of the table: a level protects what you read, not what you concluded from what you read. "At least one on call" is a conclusion, and the level by itself does not guard it.

A conclusion can be held without SERIALIZABLE

SERIALIZABLE handles this on its own: it rejects a pair of transactions that cannot be laid out sequentially, with error 40001. But it is not the only way. If both transactions take the rows they need under lock — SELECT ... FOR UPDATE — the second has to wait for the first, and then it sees the truth. Verified: the invariant holds that way at all three levels, the weakest included.

The technique has a boundary, and it is not an obvious one. The lock goes only on the rows the query returned. If the check reads "nobody has booked this slot yet", there are no rows — and nothing to put a lock on. Both transactions see "free", both write. Here what saves you is either SERIALIZABLE or a uniqueness constraint: a defence that does not care what a transaction managed to read.

What PostgreSQL does not have at all

A dirty read — seeing someone else's uncommitted changes. The database accepts the READ UNCOMMITTED level and even reports its name in the settings, but it behaves like READ COMMITTED. Reading uncommitted data is impossible at any level.

What it costs

The main thing people say about SERIALIZABLE is that it is slow. The measurement does not bear that out.

Under dense contention SERIALIZABLE gives 1215 transactions per second against 1312 for REPEATABLE READ. The difference is a few percent, while one and the same level wanders by 16–26 percent from round to round, and across rounds they overtake each other. This measurement simply cannot tell them apart. READ COMMITTED on the same set gives 2798 — more than twice as much, and that is no longer spread.

But thin the collisions out and the lead of READ COMMITTED shrinks to a quarter. What gets expensive in this workload is not the level but the contention. And you pay not in query speed but in work that had to be thrown away: under dense contention almost half of all attempts fail with error 40001 and the transaction has to be done again.

A caveat without which the conclusion turns into a falsehood: this is about this workload. SERIALIZABLE has a cost of its own — it tracks dependencies between transactions — and on transactions of a different shape that cost can become visible. Here it did not.

Two practical consequences follow. A 40001 failure is not a breakage but half of a contract: the code must be able to redo the whole transaction, otherwise the work is simply lost. The whole one, because the failure arrives not only at the commit but also on an ordinary statement in the middle. And before arguing about the level, look at the failure rate: the level determines what the database will reject, and the density of contention determines how often that will happen.

Base: an anomaly is a property of the order, not of the query

The rest of this article argues with the isolation-level table, and the argument only makes sense if you know what the table is made of. So first, a short run-up: what a level is, what an anomaly is, and why there are four of them rather than one.

A transaction is a request that several actions be treated as one. While it is alone, there are no questions. The questions start when a second one runs alongside: the database now has a choice of how to interleave their actions, and it does not make that choice the way you imagine.

The ideal here is called serialisability: the result must be one that some order of running the transactions one after another would have produced. Any result no such order can produce is what an anomaly is. Note that the definition contains not one word about a wrong query: every query on its own is legal, and what turns out to be wrong is the interleaving.

An isolation level is a bargain. Full serialisability is expensive, and the levels sell it in parts: the lower the level, the more interleavings the database agrees to accept and the faster it runs. What exactly you give up at each step is what the standard's table describes.

Four interleavings worth seeing once

Anomalies are easier to play out once than to read definitions of. Below are the same four scenarios as in the measurement: the order of the steps in them is dictated explicitly by two connections, so they repeat and do not depend on who outruns whom.

Take them one at a time — all four are built the same way and differ by one step.

Non-repeatable read. T1 reads a row, T2 changes that row and commits between T1's reads, T1 reads again — and sees something else. Neither of them did anything forbidden; the world simply moved under T1's feet inside a single transaction.

A phantom is the same thing, but about the count. T1 reads no particular row; it counts rows matching a condition: SELECT count(*) … WHERE balance >= 100. Between the two counts T2 inserts a new matching row. The difference from the previous case is exactly one thing — UPDATE against INSERT — and that is why the standard puts them on two different levels: guarding a row that has already been read is technically easier than guarding against the appearance of a row nobody has read yet.

Lost update. Both transactions read the balance — one hundred — both add their own amount and write. The first writes 110, the second 120, and 110 disappears without trace: instead of the expected 130 the database is left with 120. Here it is not somebody's picture of the world that suffered but the data itself.

Write skew is the nastiest, and the standard's table does not contain it. Two on-call doctors, with the invariant "at least one stays on call". Each transaction checks the invariant before writing, both see two and both consider their own departure safe. The rows they touch are different, so there is no write conflict at all. Nobody is left on call, and the database says nothing about it.

What a level answers with: a snapshot or a refusal

Switching the level on one and the same interleaving shows that the database has only two ways to answer, and they work differently.

A snapshot. At REPEATABLE READ and above a transaction sees the database as it was when the transaction began. Somebody else's COMMIT that happened in the middle does not enter that snapshot — and the question "has it changed" simply does not arise. That closes the first two scenarios, and both at once: a snapshot does not care whether a row was changed or a new one added.

A refusal. A snapshot does not help where transactions write. Here the database rejects one of them with the code 40001 and offers a retry. That closes the third and fourth scenarios — and, to get ahead of the article, this is exactly what the price of SERIALIZABLE consists of: what you pay with is not a slow query but retries.

It is also worth noting at which step the refusal arrives: in the lost update it comes at the UPDATE already, in write skew only at the COMMIT. Hence the practical rule the article returns to at the end: what has to be retried is the whole transaction, not a wrapper around a single commit.

The table does not describe what you think

Isolation levels are almost always taught with one table: four levels, three anomalies, ticks. The table is real — it is in the standard and in the PostgreSQL documentation. The trouble is in how it is read.

It describes what a level is forbidden to allow. Not what it allows in your database. The PostgreSQL documentation puts this in one sentence, and that sentence removes half of the confusion: the standard specifies which anomalies must not occur at certain isolation levels; higher guarantees are acceptable.

So there is a gap between the table and your database, and it runs both ways. The database can be stricter than the table — and then you are defending against something that never happens. The database can allow something the table never mentions — and then you are not defending at all.

Both cases below are reproduced on a live PostgreSQL 16.13. The order of steps is prescribed explicitly across two connections: this is not two threads hoping to collide but a fixed interleaving, so the result does not depend on how fast the machine is, and it repeats.

Stricter than the table: no phantom at REPEATABLE READ

By the standard, REPEATABLE READ must prevent non-repeatable reads and may allow phantoms. Check both.

A non-repeatable read — the same row read twice, changed and committed by a neighbour in between:

begin(t1, level)
first  = t1.execute("SELECT balance FROM accounts WHERE id = 1")
t2.execute("UPDATE accounts SET balance = 999 WHERE id = 1")   # and commits
second = t1.execute("SELECT balance FROM accounts WHERE id = 1")

A phantom — the same thing, except what changes is not the row but how many rows match: the neighbouring transaction inserts one more.

The highlighted cell is where the database is stricter than the standard. The documentation names the reason: The table also shows that PostgreSQL's Repeatable Read implementation does not allow phantom reads. The level is built on a snapshot: the transaction sees the database as it was when it began — so the question "have new rows appeared" never arises.

The practical consequence is quiet but costly — and it is worth stating narrowly, because the wide version is wrong. For the sake of read repeatability no locks are needed at REPEATABLE READ: an ordinary SELECT run a second time will not see a phantom, and a defence added just in case against exactly that costs contention and gives nothing.

But that does not make explicit locks useless on PostgreSQL. They have another job — coordinating concurrent writes — and the snapshot does not help with it. That second job gets its own section below, with a measurement.

Weaker than expectations: write skew

Now what the standard's table does not contain. The classic example is two doctors on call and the invariant "at least one must remain".

# Both transactions read the invariant BEFORE either has written.
seen1 = t1.execute("SELECT count(*) FROM oncall WHERE on_call")   # 2
seen2 = t2.execute("SELECT count(*) FROM oncall WHERE on_call")   # 2
 
t1.execute("UPDATE oncall SET on_call = false WHERE doctor = 'Alice'")
t2.execute("UPDATE oncall SET on_call = false WHERE doctor = 'Boris'")
 
t1.commit()
t2.commit()

Each transaction on its own is impeccable: it checked the condition, saw two, took one off call — one remains. Neither touches the other's row, so there is no write conflict and the snapshot is satisfied with both.

At REPEATABLE READ the invariant is broken and the database said nothing at all. The anomaly is not in the standard's table — and that is not a reader's oversight but a known gap in the table itself. The 1995 paper that introduced write skew as A5B makes the point in its own remark: Remark 5. ANSI SQL isolation phenomena are incomplete. There are a number of anomalies that still can arise.

Hence the practical rule worth remembering instead of the table: REPEATABLE READ protects what you read, not what you concluded from what you read. The invariant "at least one on call" is a conclusion, and the level by itself does not guard it.

Write skew is stopped by SERIALIZABLE. The level is assembled on top of the same snapshot: implemented using a technique known in academic database literature as Serializable Snapshot Isolation — with dependency tracking added on top. The difference between the two upper levels lies not in what they let you read but in which execution histories they agree to accept:

The first four rows coincide word for word — not by accident but because both levels are built on one and the same snapshot. What tells them apart is the bottom two.

Where the failure actually arrives

"SERIALIZABLE fails at COMMIT" is not always true, and one run shows it. In the on-call scenario there is no conflict before the commit: the transactions touch different rows, and the dependency cycle is only discovered at COMMIT. In the lost-update scenario both reach for the same row, and the same 40001 arrives on the statement itself:

One practical conclusion follows from that pair: retry the whole transaction. A wrapper around the commit alone catches only half the cases.

Holding an invariant without SERIALIZABLE: where a lock works and where it does not

It is easy to draw a wider conclusion from the previous section than it supports: "only SERIALIZABLE guards an invariant". That is wrong, and it is checked the same way — by running it.

The same on-call scenario, but both transactions read the predicate under SELECT ... FOR UPDATE. And a second scenario differing in exactly one thing: the rows satisfying the condition do not exist yet. The invariant is "no more than one booking per slot"; both transactions check whether the slot is taken and insert.

# Scenario 1: the predicate's rows EXIST — there is something to lock.
on_call = t.execute("SELECT doctor FROM oncall WHERE on_call FOR UPDATE")
 
# Scenario 2: no rows match — there is nothing to lock.
taken = t.execute("SELECT id FROM bookings WHERE slot = 1 FOR UPDATE")

The top row refutes the wide version: the invariant held at all three levels, the weakest included. FOR UPDATE took the very rows the decision is made from, the second transaction had to wait — and then saw the truth. But what guards it here is the lock, not the level, and the outcome differs: at READ COMMITTED the second transaction re-reads the fresh row and backs off by itself, while at the upper levels the same technique turns into a 40001 failure. The invariant survives either way; what the application has to handle is not the same.

The bottom row shows the boundary. FOR UPDATE locks only the rows actually returned; an empty result creates no range lock, as it would in some other database systems. Both transactions see "the slot is free", both insert, and each is right on its own. Here what helps is either SERIALIZABLE or a uniqueness constraint — a defence that does not depend on what a transaction managed to read.

Hence the precise formulation instead of the wide one: SERIALIZABLE rejects histories that cannot be laid out sequentially — automatically, without asking which invariant you had in mind. At weaker levels the same invariant can often be defended explicitly, but correctness then rests on the chosen lock, constraint or statement shape, and those are what must be checked.

A dirty read: the level's name and its behaviour are different things

READ UNCOMMITTED in PostgreSQL is usually described as "it does not exist". That is close to the truth, but it is not checked the way it seems, and it caught me out.

begin(reader, "READ UNCOMMITTED")
reader.execute("SELECT current_setting('transaction_isolation')")   # read uncommitted

The name survives: the database accepts the level and hands it back. What is substituted is the behaviour, and behaviour is what has to be tested — let a neighbouring transaction change a row and not commit:

transaction_isolation reports: read uncommitted
attempt to read the uncommitted value: no — 100 is visible, that is,
the value from before the uncommitted change

The documentation says exactly this: PostgreSQL's Read Uncommitted mode behaves like Read Committed. A dirty read is unreachable at every level.

The remark here is not about PostgreSQL but about method. "The setting is called X" and "the behaviour is Y" are different claims, and the first says nothing about the second. The first version of this measurement asserted that the database silently substitutes the level; running it disproved that, and the run is kept alongside the correction.

The price: the gap is not where it is looked for

"SERIALIZABLE is slow" is the most common thing said about it, and the conversation usually stops there. The measurement says otherwise.

Eight workers in a read-modify-write loop over a random row from a small set. The smaller the set, the more often they collide. Levels are measured not one after another but in rounds: every round runs all three, nine rounds in total.

What to read here is not the absolute numbers but the distances between the bars — and the width of the tail. The tail is the round-to-round spread, from the worst round to the best, and here it decides everything.

Under dense contention SERIALIZABLE delivers 1215 transactions per second against 1312 for REPEATABLE READ. The difference between them is a few percent, while each one's own spread is 16 and 26 percent; across rounds SERIALIZABLE was ahead of its neighbour in three out of nine. This measurement cannot say that either of the two upper levels is faster than the other, and that is the only honest conclusion from numbers like these.

READ COMMITTED on the same set delivers 2798 — more than twice as much, and that is not explained by the spread. But thin the collisions out and its lead shrinks to a quarter (3438 against 2583) and becomes comparable to the spread.

In this workload what gets expensive is not the level but the contention. You pay not in query speed but in work that had to be thrown away: on the dense set almost half of all attempts fail with 40001 and are done again, on the sparse one about six percent.

Here a caveat is needed, without which the conclusion becomes untrue. This is a statement about this workload, not about isolation levels in general. SSI has a cost of its own: it tracks dependencies and takes predicate locks. On transactions of a different shape — longer, with a larger read set, with different plans — that cost may rise out of the spread. Here it did not; that is a measurement result, not a proof that the overhead is absent.

And a symmetric caveat about the failure rate. Saying "the level does not set it" would overshoot in the other direction: READ COMMITTED has a zero rate on both sets and the two upper levels do not, so the level plainly affects it. More precisely: the level determines which histories the database will reject, and the density of contention determines how often that happens. In this measurement the second moves things more. The practical conclusion is unchanged: before arguing about the level, look at the failure rate and at how much your transactions reach for the same rows.

What to do about it

Pick the level by the invariant, not by the table. The question is not "which anomalies are permitted" but "what do I conclude from what I read, and who guards that". If a decision is made from the result of a query — a count, a sum, the presence of a free slot — there are exactly two routes: either SERIALIZABLE, which rejects an unserializable history on its own, or an explicit defence at a weaker level — locking the rows you need, a constraint, atomic conditional DML. The second route is cheaper in failures and more expensive in attention: it works exactly as far as it was chosen correctly, and its boundary is in the section above.

A 40001 failure is not an error, it is half of a contract. A level that rejects a whole history requires the caller to be able to redo the whole transaction — the transaction, not the commit, because the failure arrives on a statement too. The documentation makes this a condition: It is important that an environment which uses this technique have a generalized way of handling serialization failures. Code that catches the exception and logs it is not handling — it is lost work.

Retrying does not mean retrying everything. It is worth keeping a short list of what each SQLSTATE means. All four were checked on the same build — raised and read off the error, not taken from a table:

What not to do is turn this into "retry any database exception". A retry belongs where the cause of the failure disappears on the next attempt; with a constraint violation it usually does not.

A defence against phantoms for the sake of read repeatability is not needed on PostgreSQL. At REPEATABLE READ a repeated ordinary SELECT will not see a phantom, and locks added for that cost contention and give nothing. That is not the same question as defending an invariant against concurrent writes: there locks are sometimes required, but they work by the rules from the section above — over returned rows, not over a range.

How it was measured

Both scripts run against a live PostgreSQL; the address comes from DE_BENCH_DSN. Each opens from here, together with its run record.

• bench/isolation/anomalies.py — four anomalies at three levels, by reproduction, with an explicit order of steps.
• bench/isolation/cost.py — throughput, the round-to-round spread and the shares of failures under dense and rare contention.
• bench/isolation/explicit_locking.py — whether an explicit lock holds an invariant at a weaker level, and where it stops holding.

The build is PostgreSQL 16.13. The numbers were taken on it; on a different build and different hardware the absolute values will differ, and the relations between levels are the subject of the same check, repeatable with one command.

A separate note on what bench/isolation/cost.py counts. Failures with 40001 and 40P01 go into separate counters rather than one: they are different SQLSTATEs, and a column labelled with one of them must not include the other. The first version of the script added them together, and the label was inexact — even though across every run not a single deadlock occurred. The defect was in the name of the quantity rather than in the numbers, but that is fixed in the code, not in a footnote to it.