Isolation levels: why two people saw different totals

Two people ran the same query and got different totals. Nothing in the query was wrong. The table was moving underneath it, and the database was doing exactly what it had been told — by nobody, because nobody told it anything. The isolation level in force was the one it shipped with.

The video climbs the four levels by what each one still lets through: a dirty read, a non-repeatable read, a phantom, write skew, and then the level with nothing left on the list. If your model is “set it to serializable and the problem goes away,” you have the part that makes the database correct and none of the part that keeps your code correct — because serializable does not make anomalies impossible, it makes them errors, and code that does not handle the error loses work quietly under load.

Everything below is checked against PostgreSQL 18, SQL-92’s own text (the standard’s isolation subclause), and Berenson, Bernstein, Gray, Melton, O’Neil and O’Neil, “A Critique of ANSI SQL Isolation Levels” — the paper where every anomaly the standard did not name comes from.

Serializable is not free, and it is not transparent

SQL-92 promises that concurrent transactions at SERIALIZABLE get “the same effect” as some serial execution — and then, in the same subclause, the price: a rollback “may be initiated implicitly” when the implementation “detects the inability to guarantee serializability,” raising serialization failure — SQLSTATE 40001.

PostgreSQL’s docs put it in engineering terms: a serializable transaction “monitors for conditions which could make execution… behave in a manner inconsistent with all possible serial executions,” and because the monitoring is conservative, some transactions that would have been fine get rolled back anyway. So this is the contract you sign: any transaction, at any time, may fail at commit for reasons that have nothing to do with the rows it touched.

The retry has to re-run the whole transaction, not just the commit — the error means the snapshot it read from is stale, and re-issuing the last statement can’t fix a stale read:

async function withRetry(pool, fn, { attempts = 5 } = {}) {
  for (let i = 0; ; i++) {
    const client = await pool.connect();
    try {
      await client.query("BEGIN ISOLATION LEVEL SERIALIZABLE");
      const result = await fn(client);
      await client.query("COMMIT");
      return result;
    } catch (err) {
      await client.query("ROLLBACK").catch(() => {});
      const retryable = err.code === "40001" || err.code === "40P01";
      if (!retryable || i + 1 >= attempts) throw err;
      await sleep(Math.min(1000, 25 * 2 ** i) * (0.5 + Math.random()));
    } finally {
      client.release();
    }
  }
}

Everything inside fn has to be safe to run twice — no email, no payment call, no queue push before the commit succeeds, because “it is important that any data read from a permanent user table not be considered valid until the transaction which read it has successfully committed. This is true even for read-only transactions.” Side effects go after the loop returns.

And it costs throughput in ways the docs list explicitly once you turn it on: declare read-only transactions as such where you can, since they can often skip the tracking entirely; control the connection count, because the overhead is worse on a busy system; and watch for sequential scans, since “a sequential scan will always necessitate a relation-level predicate lock” — a missing index turns into retries. Be careful carrying any of this to another engine: MySQL’s SERIALIZABLE converts plain SELECTs to locking reads, so the cost there is blocking and deadlocks, not aborts. Same word, opposite failure mode.

SET TRANSACTION goes before the first query

PostgreSQL: “The transaction isolation level cannot be changed after the first query or data-modification statement… of a transaction has been executed.” The reason: under Repeatable Read, the snapshot is taken by the first real statement. Once it exists, there is nothing left for a later SET TRANSACTION to change, so PostgreSQL refuses it.

The trap: “If SET TRANSACTION is executed without a prior START TRANSACTION or BEGIN, it emits a warning and otherwise has no effect.” Not an error — a warning. On a pooled connection where the driver’s BEGIN lands later than you think, the level you set goes into the void and the transaction runs at the default. The fix that can’t be ordered wrong is to put the level in the BEGIN itself: BEGIN ISOLATION LEVEL SERIALIZABLE; — or set default_transaction_isolation for the session or role, and check with SHOW transaction_isolation from inside the transaction you’re worried about.

The anomaly the standard forgot to name: write skew

This is the reason serializable exists, and it’s the anomaly a transaction-level snapshot does not fix.

A hospital rota requires at least one doctor on call for the night shift. Two are on tonight. Both feel ill at once:

-- Alice's session                          -- Bob's session
BEGIN ISOLATION LEVEL REPEATABLE READ;      BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM on_call                SELECT count(*) FROM on_call
 WHERE shift='night' AND active;             WHERE shift='night' AND active;
-- 2, so it is safe to leave                -- 2, so it is safe to leave
UPDATE on_call SET active=false             UPDATE on_call SET active=false
 WHERE doctor='alice';                       WHERE doctor='bob';
COMMIT;                                     COMMIT;

Both commit. Nobody is on call. Walk the standard’s own checklist: no dirty read, no non-repeatable read, no phantom, no lost update — they wrote different rows, so no row lock was ever contended. Every declared constraint held. The invariant that broke — “at least one active doctor” — spans rows and lived in application code, and each transaction checked it honestly against a world that no longer existed by the time it wrote.

Berenson et al. name it write skew, and PostgreSQL’s own table agrees: under Repeatable Read, a serialization anomaly is possible, and the docs say plainly that enforcing rules at that level is “not likely to work correctly without careful use of explicit locks.” Note this is not the C in ACID — every declared constraint held, and the thing that broke is an invariant only the application knew about. There are four fixes: run at Serializable, so recomputing the count on retry sees 1 and refuses; lock what you read with SELECT ... FOR UPDATE on both doctors’ rows; lock a token for the invariant — one row per shift, or an advisory lock; or move the invariant into the database, when it can be expressed as a constraint at all.

Predicate locks: how serializable actually finds it

Neither transaction touched the other’s row, so no row lock could ever have connected them. What serializable tracks instead is reads — specifically, which writes would have changed the result of a read that already happened. This is Serializable Snapshot Isolation: Alice’s count(*) leaves a marker on what it read, Bob’s UPDATE hits a row inside that predicate, the dependency is recorded, and when the pair closes into a cycle no serial order can explain, one commit fails.

The properties that matter operationally: these locks never block — “they do not cause any blocking and therefore can not play any part in causing a deadlock” — so they’re bookkeeping, visible in pg_locks as SIReadLock; they follow the query plan, so an index scan locks only the rows it touched while a sequential scan locks the whole relation; and they can outlive the transaction that took them, which is the overhead the level charges. The contrast worth remembering: a Read Committed transaction that wants the same guarantee “may need to take out a lock on an entire table, which could block other users,” while serializable’s monitoring “does not introduce any blocking beyond that present in repeatable read.”

MVCC: why readers never wait, and why VACUUM exists

None of the above blocks a plain SELECT, and that’s a design decision with a bill attached. “In MVCC, locks acquired for querying data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading.” Every row version carries a hidden xmin (the inserting transaction) and xmax (the deleting one, or zero). An UPDATE doesn’t change a row in place — it writes a new version and stamps the old one’s xmax. A snapshot is a rule for which transaction ids count as committed for you.

So old versions pile up. “The row version must not be deleted while it is still potentially visible to other transactions… the space it occupies must then be reclaimed… This is done by running VACUUM.” Three things follow that people meet as incidents: plain VACUUM marks space reusable but “will not return the space to the operating system,” so a table that grew during a bad week stays that size; a long transaction sitting idle in transaction pins every dead row newer than its snapshot across the whole database, which is why idle_in_transaction_session_timeout exists; and transaction ids themselves wrap, so a database that cannot vacuum will eventually refuse writes to protect itself. Other engines pay the same bill elsewhere — SQL Server in a version store, Oracle in undo segments — three different places the old versions live, three different things that go wrong when kept too long.

The default is almost never serializable

Across engines, the same word means different things and the shipped default is rarely the strict one. PostgreSQL accepts READ UNCOMMITTED but runs it as Read Committed — under MVCC there’s no way to show an uncommitted row. Its Repeatable Read forbids phantoms, which the standard permits at that level; its default is Read Committed. MySQL/InnoDB defaults to Repeatable Read, and gets there with gap locks rather than pure snapshots, so its repeatable read has phantoms answered a different way. SQL Server’s READ COMMITTED uses locks unless READ_COMMITTED_SNAPSHOT is turned on, and its SNAPSHOT level — not REPEATABLE READ — is the one that matches PostgreSQL’s repeatable read. Oracle offers only read committed and serializable, no repeatable read at all.

Which anomaly you meet first was chosen for you, by whichever of these your database happens to be. The next question after that one is usually so why doesn’t the database just lock everything, which is MVCC above — and the one after it is the ACID post.