Transaction isolation: what your database actually promises

Most applications run on their database’s default isolation level, and most engineers can’t say what that level allows. That is usually fine, until two requests arrive in the same millisecond and an invariant the code checked carefully turns out not to hold. The bugs are rare, impossible to reproduce on a laptop, and they corrupt data quietly instead of throwing errors.

This article covers what the isolation levels mean in Postgres and MySQL rather than in the SQL standard, how multi-version concurrency control (MVCC) implements them, the anomalies each one lets through, write skew in particular, and how serializable snapshot isolation catches it at a price you can usually afford.

What the standard says

Isolation is the I in ACID: concurrent transactions should not see each other’s partial work. The strongest form is serializability: the result of running transactions concurrently is the same as some order of running them one at a time. If every transaction is correct on its own, a serializable database keeps them correct together, so the application never has to think about concurrency.

That guarantee costs throughput, so SQL-92 defined weaker levels by the phenomena each one rules out:

• Read Uncommitted allows dirty reads: seeing another transaction’s uncommitted writes.
• Read Committed forbids dirty reads, but a row read twice can change in between (a non-repeatable read).
• Repeatable Read forbids that too, but rerunning a query can return new rows (a phantom).
• Serializable forbids all three.

Berenson and colleagues showed in 1995 that this list is incomplete. Forbidding the three phenomena does not give you serializability, and the definitions were written with lock-based databases in mind. The anomalies that matter most in practice, the lost update and write skew, aren’t on the standard’s list at all. Their paper also described snapshot isolation, which is what most databases actually implement today, and which fits awkwardly into the standard’s levels.

The practical consequence: the names on the levels don’t tell you what you get. Oracle’s SERIALIZABLE is snapshot isolation and allows write skew. Postgres’s REPEATABLE READ is stronger than the standard requires; MySQL’s is weaker in one important way. You have to check the database, not the name.

How databases do it: MVCC

Almost every modern relational database implements isolation with multi-version concurrency control. An update does not overwrite a row; it creates a new version of it. Each transaction reads from a snapshot: a record of which transactions had committed when the snapshot was taken. For each row, the transaction reads the newest version that its snapshot considers committed.

In Postgres, every row version (a tuple) carries two transaction ids: xmin, the transaction that created it, and xmax, the transaction that deleted or replaced it. An UPDATE is a delete plus an insert: it stamps the old tuple’s xmax and writes a new tuple whose xmin is the updater. MySQL’s InnoDB updates rows in place instead, and keeps the old versions in an undo log, but the visibility logic is the same idea.

MVCC’s great property is that readers never block writers and writers never block readers. A long report and a stream of updates can run against the same table without waiting for each other. Writers still conflict with writers: two transactions updating the same row must be ordered, and the second one waits for the first to commit or abort.

The isolation levels then differ mainly in when the snapshot is taken and what happens when two writers collide.

Read Committed and the lost update

Read Committed is the default in Postgres, Oracle and SQL Server. In Postgres it takes a new snapshot for every statement. Each statement sees a consistent picture, but two statements in the same transaction can see different pictures, because other transactions committed in between.

The classic bug at this level is the lost update. Two requests each add 10 to a counter:

1. A reads the balance: 100.
2. B reads the balance: 100.
3. A writes 110 and commits.
4. B writes 110 and commits. A’s increment is gone.

Nothing in Read Committed prevents this, because each statement did exactly what it was asked. The read and the write were separate statements, and the application did the arithmetic in between. The fixes, from best to worst:

• Let the database do the arithmetic. UPDATE accounts SET balance = balance + 10 is safe at every level. In Postgres, if the row changed while the update waited for its lock, the statement re-reads the newest version and re-checks its WHERE clause before writing.
• Lock what you read. SELECT … FOR UPDATE takes a row lock, so the second request waits at its read until the first commits, then sees 110.
• Compare and set. Write UPDATE … SET balance = 110, version = 8 WHERE id = 7 AND version = 7, and treat “0 rows updated” as a conflict to retry. This is how most ORMs implement optimistic locking.

Snapshot isolation

Snapshot isolation takes one snapshot when the transaction starts (strictly, at its first statement) and uses it throughout. Every read in the transaction sees the database as of that moment, which rules out non-repeatable reads and phantoms. For writes, it adds a rule: if two concurrent transactions update the same row, only one can commit. Postgres enforces this as “first updater wins”: the second updater waits, and when the first commits, it fails with could not serialize access due to concurrent update. That rule also rules out lost updates.

Postgres calls this level REPEATABLE READ. MySQL InnoDB, whose default is also REPEATABLE READ, behaves differently. Plain SELECTs read the snapshot, but UPDATE, DELETE and locking reads operate on the latest committed rows, with no first-updater-wins check. So in InnoDB the lost update in the previous section still happens at Repeatable Read, and a statement can update rows its own snapshot doesn’t show.

Martin Kleppmann’s Hermitage project tests these behaviours across databases and is the best reference when you need to know what a specific version does.

Write skew

Snapshot isolation stops two transactions from writing the same row. Write skew is what happens when they write different rows, each based on a read that the other’s write makes false.

The textbook example is a hospital rule that at least one doctor must be on call. Alice and Bob are both on call and both feel unwell. Each of them asks to go off call at the same moment. Each request checks the rule (“are at least two doctors on call?”), sees yes, and updates its own row. Under snapshot isolation both checks read the same snapshot, both pass, the two updates touch different rows so there is no write conflict, and both commit. Nobody is on call.

The pattern is always the same: read some rows, decide based on what you saw, then write. It appears as:

• Booking. Check that a meeting room has no overlapping booking, then insert one.
• Uniqueness without a constraint. Check that a username is free, then claim it.
• Spending limits across rows. Check that two accounts together hold enough, then debit one.
• Inventory. Count the unreserved seats, then reserve one.

Several of these are phantom-driven: the check looks for rows that don’t exist yet, so there is nothing to lock. SELECT … FOR UPDATE fixes the doctors example, because it locks the rows both transactions read, but it can’t fix the booking example: you can’t lock the absence of an overlapping row. The options are:

• A constraint. A UNIQUE index fixes the username case. Postgres EXCLUDE constraints fix overlapping bookings: EXCLUDE USING gist (room WITH =, during WITH &&). When the database can enforce the invariant itself, let it.
• Materialise the conflict. Create a row that both transactions must lock, such as one row per room per time slot, and take it with FOR UPDATE. It works, but it leaks a concurrency mechanism into the data model.
• Use Serializable. Let the database detect the conflict, at the cost of retries.

Serializable, three ways

There are three practical ways to give real serializability:

• Actually run transactions one at a time. VoltDB and Redis do this: one thread per partition, transactions as stored procedures, all in memory. It is simple and fast as long as every transaction is short and touches one partition.
• Two-phase locking (2PL). Readers take shared locks and writers take exclusive locks, held until commit, plus range or predicate locks to stop phantoms. MySQL’s SERIALIZABLE works this way: it turns plain SELECTs into locking reads. It is correct but pessimistic. Readers block writers again, and deadlocks become routine.
• Serializable snapshot isolation (SSI). Proposed by Cahill, Röhm and Fekete (2008) and in Postgres since 9.1. Transactions run under snapshot isolation without extra blocking, while the database tracks read-write dependencies: “T1 read something that T2 then wrote”. Snapshot isolation can only produce a non-serializable result when there are two consecutive such edges between concurrent transactions, so when SSI sees that pattern it aborts one of them.

SSI is optimistic. Transactions that don’t conflict pay a small bookkeeping cost, and transactions that might conflict get aborted with SQLSTATE 40001. It has false positives, because it tracks reads at a coarser grain than individual rows when memory runs short, so it sometimes aborts transactions that would have been fine. Ports and Grittner (VLDB 2012) report that Postgres’s implementation performs within a few percent of snapshot isolation on most workloads, with the gap widening on workloads that read a lot and conflict often.

What it costs in production

The level you pick is only part of the cost. How long your transactions stay open matters as much, because MVCC has to keep old versions alive for every open snapshot:

• Long transactions hold back cleanup. In Postgres, VACUUM can’t remove a dead tuple while any open snapshot might still see it. One forgotten transaction left idle in transaction for a day stops cleanup across the whole database: tables and indexes bloat, and queries slow down as they skip dead versions. Set idle_in_transaction_session_timeout. In InnoDB the same problem shows up as a growing history list length in the undo log.
• Long transactions conflict more. Under snapshot isolation and SSI, the probability of a conflict grows with how long a transaction is open. A transaction that makes an HTTP call in the middle is open for the call’s whole latency. Do network calls before or after, never inside.
• Read-only work has an escape hatch. Postgres’s SERIALIZABLE READ ONLY DEFERRABLE waits for a snapshot that can’t be part of an anomaly, then runs with no SSI overhead and no risk of a serialization failure. It is ideal for reports and backups.
• Replicas change the picture. Reads from an asynchronous replica see a snapshot that is behind the primary, whatever level you ask for. A transaction that reads on a replica and writes on the primary has no isolation between the two.

Choosing a level

A workable policy for a service on Postgres:

1. Default to Read Committed, and write every read-modify-write as a single atomic statement or a compare-and-set.
2. Enforce invariants with constraints wherever the database can express them: unique, foreign key, check and exclusion constraints. They hold at every isolation level, against every code path, including the one written next year.
3. Use Serializable for the transactions that check an invariant across rows that no constraint can express, with a retry loop. You can set the level per transaction, so this doesn’t have to be global.
4. Test it. Concurrency bugs don’t appear in single-threaded tests. Run the two transactions from two connections and pause one between its read and its write, or use a tool such as Jepsen’s Elle, which checks histories from real runs for isolation anomalies.

Some databases make the stronger choice for you. CockroachDB defaults to Serializable, and Spanner offers external consistency, which is stronger still. The same trade-off applies across services too: once a workflow spans two databases, there is no isolation between them at all, which is the problem sagas exist to manage.

Serializable with retries

A small helper for Postgres with node-postgres: run a function as a Serializable transaction, and retry the whole function on serialization failures and deadlocks, with jittered backoff. The doctors example on top of it can no longer leave the shift empty.

// Run a transaction at SERIALIZABLE and retry it when Postgres reports a
// serialization failure (40001) or a deadlock (40P01). The whole function is
// retried, reads included: its decisions were made on a snapshot that lost.
import { Pool, type PoolClient } from "pg";

const RETRYABLE = new Set(["40001", "40P01"]);

export async function serializable<T>(pool: Pool, fn: (tx: PoolClient) => Promise<T>, maxAttempts = 5): Promise<T> {
  for (let attempt = 1; ; attempt++) {
    const tx = await pool.connect();
    try {
      await tx.query("BEGIN ISOLATION LEVEL SERIALIZABLE");
      const result = await fn(tx);
      await tx.query("COMMIT"); // SSI can also fail here, at commit time
      return result;
    } catch (err) {
      await tx.query("ROLLBACK").catch(() => {});
      const code = (err as { code?: string }).code;
      if (!code || !RETRYABLE.has(code) || attempt >= maxAttempts) throw err;
      await new Promise((r) => setTimeout(r, Math.random() * 10 * 2 ** attempt)); // jittered backoff
    } finally {
      tx.release();
    }
  }
}

// The doctors example, now safe. fn may run several times, so it must not
// send emails or call other services: do that after the commit, or via an outbox.
export const goOffCall = (pool: Pool, name: string) =>
  serializable(pool, async (tx) => {
    const { rows } = await tx.query("SELECT count(*)::int AS n FROM doctors WHERE shift_id = 1 AND on_call");
    if (rows[0].n < 2) throw new Error("someone has to stay on call");
    await tx.query("UPDATE doctors SET on_call = false WHERE shift_id = 1 AND name = $1", [name]);
  });

References

1. Berenson, H., Bernstein, P., Gray, J., Melton, J., O’Neil, E. & O’Neil, P. (1995). A Critique of ANSI SQL Isolation Levels. SIGMOD. arXiv:cs/0701157
2. Adya, A. (1999). Weak Consistency: A Generalized Theory and Optimistic Implementations for Distributed Transactions. PhD thesis, MIT. MIT-LCS-TR-786
3. Cahill, M. J., Röhm, U. & Fekete, A. D. (2008). Serializable Isolation for Snapshot Databases. SIGMOD. doi:10.1145/1376616.1376690
4. Ports, D. R. K. & Grittner, K. (2012). Serializable Snapshot Isolation in PostgreSQL. VLDB. arXiv:1208.4179
5. PostgreSQL Documentation. Chapter 13: Concurrency Control. postgresql.org/docs/current/mvcc.html
6. MySQL Reference Manual. InnoDB Transaction Isolation Levels and Consistent Nonlocking Reads. dev.mysql.com
7. Kleppmann, M. Hermitage: Testing transaction isolation levels. github.com/ept/hermitage
8. Kleppmann, M. (2017). Designing Data-Intensive Applications, ch. 7: Transactions. O’Reilly. dataintensive.net
9. Kingsbury, K. & Alvaro, P. (2020). Elle: Inferring Isolation Anomalies from Experimental Observations. VLDB. arXiv:2003.10554