Cruxly › System design › Databases: Choosing One

Isolation levels, in depth

snapshots, write skew and serializability: what your engine really guarantees

An isolation level is a promise about which anomalies cannot happen, and the name of the level tells you almost nothing about that promise. Two engines at "Repeatable Read" can behave differently, and one engine's "Serializable" can be snapshot isolation. To justify a database choice you need the mechanism behind each level, the anomalies it still allows, and what your application must do (retry, lock, add a constraint) to stay correct.

The anomalies the SQL standard leaves out

SQL-92 defines its four levels with three phenomena: dirty read, non-repeatable read and phantom. Berenson and colleagues showed in 1995 that those definitions are ambiguous and miss several anomalies that real systems exhibit. Their names are still the vocabulary of the field, so learn them.

Two of these deserve a concrete picture because the older lesson on this site does not cover them.

Read skew. Alice and Bob hold 500 each. A transfer moves 100 from Alice to Bob while a report reads both balances:

-- reader                                           -- transfer (one transaction)
SELECT balance FROM accounts WHERE name='alice';    -- 500
                                                    UPDATE accounts SET balance = balance - 100 WHERE name='alice';
                                                    UPDATE accounts SET balance = balance + 100 WHERE name='bob';
                                                    COMMIT;
SELECT balance FROM accounts WHERE name='bob';      -- 600
-- the report adds 500 + 600 = 1100; the bank only ever held 1000

Read Committed allows this, because every statement takes a fresh view of the committed data. Nothing was dirty. Each read was of committed data, but the two reads come from different moments. A long report, a backup or an export is exactly the workload that hits it, and a snapshot is the fix.

Dirty write. Selling a car updates two rows, the buyer and the invoice recipient. If Alice's and Bob's transactions interleave their writes, the buyer ends up Bob while the invoice goes to Alice. Berenson argues every level must prevent this, and mainstream engines do it with a row lock held on the first write until commit, so the second writer waits.

:::key Lost update and write skew The older lesson, Transaction Isolation Levels, covers lost update and the basic on-call write skew. This lesson assumes both and goes into why they happen and what stops them. :::

Use the names precisely when you talk: say "read skew" for the inconsistent pair of reads, "phantom" only when a predicate's row set changes, and "write skew" when the writes hit different rows.

MVCC: how a snapshot actually works

Multi-version concurrency control keeps several versions of a row so a reader can see the data as of one moment without blocking writers. The idea is the same everywhere. The storage differs, and the differences explain operational pain.

The visibility rule. Each transaction gets an id. A snapshot is the set of transactions already committed when the snapshot was taken. A row version is visible to you if the transaction that created it is in your snapshot and the transaction that deleted or replaced it is not.

Where the old versions live:

When the snapshot is taken is what separates levels. In PostgreSQL Read Committed it is the start of each statement. In Repeatable Read it is the start of the first non-transaction-control statement, held to the end. In InnoDB Repeatable Read it is the first consistent read. In Oracle, SERIALIZABLE and read-only transactions use the time the transaction began.

The cost is an old snapshot. A version cannot be cleaned while any open transaction might still read it. At InnoDB's default Repeatable Read, and at PostgreSQL Repeatable Read or Serializable, one session that runs BEGIN, reads once and then sits idle keeps every version created since. (A PostgreSQL Read Committed session takes a new snapshot per statement, so an idle read-only one does not normally pin old versions between statements, but it still holds its locks and, if it wrote, its transaction id.) In PostgreSQL the dead rows pile up in tables and indexes. In InnoDB the MySQL manual warns that the rollback segment can grow too big and fill the undo tablespace, and it advises committing regularly, even transactions that only read. The first thing to check on a database that bloats is the oldest open transaction (in PostgreSQL, pg_stat_activity and its backend_xmin and backend_xid), and the first guard is a timeout on idle-in-transaction sessions.

What MVCC does not do. Readers do not block writers and writers do not block readers. Two writers on the same row still serialize on a row lock. "MVCC means no locks" is a wrong statement.

:::key What to say "Reads come from a snapshot of old versions, so they never wait. The price is version storage and cleanup, which a long-running transaction can block." :::

Snapshot isolation, and the two things it misses

Snapshot isolation (SI) is the model behind PostgreSQL Repeatable Read, Oracle SERIALIZABLE and SQL Server SNAPSHOT. A transaction reads from a snapshot taken at its start. Writes follow first-committer-wins: if another transaction changed a row after your snapshot and you try to write it too, you lose and must abort. PostgreSQL detects this when you reach the row: if the other transaction is still open you wait for it, and if it committed you get the error could not serialize access due to concurrent update at the UPDATE, not at COMMIT. The application must retry the whole transaction.

SI therefore prevents dirty reads, non-repeatable reads, read skew and (by aborting) lost updates. It is a strong level, and for many applications enough. Its gap is that the engine only compares write sets. The reads that justified your decision are never checked.

Write skew. Two doctors on call, rule: at least one stays.

-- both sessions: BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM oncall WHERE on_call;               -- 2 in both
UPDATE oncall SET on_call = false WHERE doctor = 'alice'; -- session 1
UPDATE oncall SET on_call = false WHERE doctor = 'bob';   -- session 2
COMMIT; COMMIT;                                           -- both succeed; nobody on call

The write sets are disjoint, so first-committer-wins sees nothing. Four ways out:

• Lock what you read: SELECT doctor FROM oncall WHERE on_call FOR UPDATE, then count the rows in the application (PostgreSQL does not allow locking clauses with aggregation such as count(*)). Works because the rows exist. At Read Committed the second session waits and re-checks; at Repeatable Read it fails with 40001 and retries.
• Materialize the conflict: make every roster change update one shifts row, so the writes overlap and SI aborts one.
• Serializable: the engine detects the dependency (see section 5).
• A constraint, when the rule can be expressed as one. A count across rows usually cannot.

Phantoms. Now the rule is "no overlapping booking for room 3". Both transactions query for bookings in the 2 pm slot, find none, and insert one:

SELECT count(*) FROM bookings WHERE room = 3 AND slot = '14:00';  -- 0 in both
INSERT INTO bookings(room, slot, who) VALUES (3, '14:00', ...);   -- both

SELECT ... FOR UPDATE here finds zero rows and in PostgreSQL locks nothing, so it cannot help. Behaviour differs by engine: InnoDB at Repeatable Read locks the scanned index range with gap or next-key locks during a locking read, which blocks other sessions from inserting into it (a unique-index lookup of one exact value takes only a record lock). Gap locks do not conflict with each other, so two sessions can both pass the check and then each insert waits on the other's gap lock, which InnoDB resolves as a deadlock. CockroachDB's documentation states that SELECT FOR UPDATE and SELECT FOR SHARE do not prevent insertion of new rows matching the search condition. The portable fixes are a unique index or exclusion constraint on the thing that must not overlap (PostgreSQL has exclusion constraints for ranges), locking a parent row both transactions must update, or Serializable.

:::warn "Repeatable Read has no phantoms" True in PostgreSQL for repeated reads, but write skew through an insert is still possible. Lock-based Repeatable Read in SQL Server still allows phantoms. Say which engine. :::

Roads one and two: run serially, or lock everything

Before SSI, there were two ways to be serializable, and they are still in production.

Road one: actual serial execution. Run transactions one at a time on a single thread. No locks, no snapshots, no anomalies. It only works if a transaction never waits on the network, so the whole transaction is submitted as one stored procedure, and the data fits in memory or I/O is fast. VoltDB is built this way: each partition has its own single-threaded executor and a stored procedure is the transaction. Throughput scales by adding partitions. The limits are plain:

• A transaction touching several partitions needs a coordinator and is slower.
• One slow transaction stalls everything queued behind it on that partition.
• You give up interactive transactions, where the client reads, thinks and writes.

Road two: two-phase locking (2PL). Take a shared lock to read and an exclusive lock to write, and hold every lock until commit. Phantoms need locks on ranges or predicates, not only rows. SQL Server SERIALIZABLE uses key-range locks. InnoDB at SERIALIZABLE turns plain SELECTs into shared locking reads when autocommit is off, and uses next-key locks.

-- deadlock: each holds one lock and wants the other
T1: UPDATE accounts SET ... WHERE id = 1;    -- locks row 1
T2: UPDATE accounts SET ... WHERE id = 2;    -- locks row 2
T1: UPDATE accounts SET ... WHERE id = 2;    -- waits for T2
T2: UPDATE accounts SET ... WHERE id = 1;    -- waits for T1: cycle
-- the engine detects it and aborts one transaction; the app retries

2PL costs real throughput: readers block writers, writers block readers, and the longest transaction sets the wait. Deadlocks are normal, so every client needs retry logic even without Serializable.

Road three: serializable snapshot isolation

SSI (Cahill, Rohm and Fekete, SIGMOD 2008) runs transactions on snapshot isolation and detects, at run time, the patterns that can only produce a non-serializable result. PostgreSQL 9.1 (2011) was the first production database to ship it; Ports and Grittner describe the implementation.

Vocabulary. If T1 reads a version of an object and T2 writes a newer one, T1 appears to run before T2, because T1 did not see T2's write. That is an rw-antidependency. A predicate read counts: if T1 scans for rows matching a condition and T2 inserts a matching row, that is an rw-antidependency too. A serialization anomaly is a cycle of dependencies. The on-call case is a cycle of two: each doctor's read is invalidated by the other's write.

The dangerous structure. Every snapshot-isolation anomaly contains two adjacent rw-antidependencies, T1 to T2 to T3, where T3 commits first. PostgreSQL waits until T3 commits, then aborts T2 when it can, because the retried T2 is no longer concurrent with T3 and cannot form the same structure again. It aborts T1 only if T2 and T3 are both committed already.

How it tracks reads. PostgreSQL takes SIRead locks on what a transaction reads. They never block anyone, so they cannot cause deadlocks. They must outlive the transaction, until overlapping read-write transactions finish. If the lock table is short of memory, tuple locks are promoted to page and then relation locks. A sequential scan always takes a relation-level lock. Coarser locks mean more false positives: the engine aborts transactions that a perfect check would allow.

What it costs. The PostgreSQL authors measured under 7 percent against snapshot isolation on two benchmarks, and better throughput than 2PL on read-intensive loads. Their numbers, on their hardware, so treat them as direction, not a promise. Read-only transactions are cheap, and SERIALIZABLE READ ONLY DEFERRABLE waits for a safe snapshot, then reads with no SSI tracking. It is the only case where Serializable blocks and Repeatable Read does not.

What you must do:

• Retry the whole transaction on SQLSTATE 40001 (in PostgreSQL also 40P01, deadlock_detected), from the top, with a bounded count and jittered backoff.
• Declare read-only work READ ONLY, keep connection counts controlled, and prefer index scans so predicate locks stay fine-grained.
• Never trust a result read inside a transaction that later aborted.

:::warn Serializable still aborts SSI is optimistic. Under heavy contention on hot rows, the abort rate is the price. Saying "Serializable means no retries" is the classic wrong answer. :::

What your engine really does

Versions matter here. This table is what the documentation and public test results say for the versions named.

PostgreSQL anomalies by level (read-modify-write code, not atomic statements):

An atomic UPDATE t SET n = n - 1 WHERE id = 1 AND n > 0 is safe even in Read Committed. PostgreSQL re-evaluates the WHERE clause against the new row version when the first updater commits. The same row is the only guarantee; multi-row statements can see odd results.

Check the engine, not the name. Kleppmann's Hermitage project runs the same anomaly tests across engines and publishes the matrix. It lists PostgreSQL Repeatable Read and Oracle SERIALIZABLE as snapshot isolation and SQL Server Repeatable Read as preventing lost update but not phantoms. It lists InnoDB Repeatable Read only as monotonic atomic view, not snapshot isolation (lost update and write skew are not prevented, and phantoms only in read-only contexts), and Jepsen later confirmed on 8.0.34 that it is not snapshot isolation (next section).

:::key The line to remember Oracle's Serializable and PostgreSQL's Repeatable Read are the same strength, snapshot isolation. Among the Serializable levels here, InnoDB's and SQL Server's rely on locking reads; PostgreSQL's is Serializable by dependency detection on top of snapshots, and CockroachDB's also resolves conflicts by aborting with 40001 rather than by locking reads. :::

Distributed databases, strictness, and what Jepsen found

Serializable is not the strongest word. Serializable says the result equals some serial order, not necessarily the real-time order. Client 1 commits x = 1, is told so, and then client 2 starts and reads x = 0. A serializable system may place client 2's transaction before client 1's. Strict serializability (Spanner calls it external consistency) forbids that: if one transaction finishes before another starts, the order respects it. Spanner provides it by default and documents it through TrueTime. Jepsen's 2016 test of a CockroachDB beta found it not strictly serializable (a causal-reverse anomaly even with clocks in bounds, stale reads in limited cases, and correctness that depended on clock offset staying under 250 ms), and it also found bugs that broke serializability itself, fixed in later betas. Do not quote that as the present state; it shows what to ask.

A level is a claim to test. Jepsen checks claims against real histories. Findings worth knowing:

• PostgreSQL 12.3 (2020): a bug in SERIALIZABLE let concurrent transactions produce G2-item anomalies (the cycle shape of write skew), in cycles of two or three transactions. Jepsen listed 9.5.22, 10.13, 11.8, 12.3 and 13 as affected. The fix shipped in the 2020-08-13 minor releases.
• MySQL 8.0.34 (2023): a single-node Repeatable Read showed G2-item, read skew and lost update, so it is not snapshot isolation. Lost updates appeared routinely. Serializable passed on single nodes and small binlog-replicated clusters with read-only replicas. An AWS RDS Multi-AZ DB cluster running "Serializable" showed G2-item and G-single anomalies; Jepsen tied them to replica_preserve_commit_order, which MySQL 8.0.27+ enables by default but the RDS default parameter group left off.
• MongoDB 4.2.6: even with snapshot read concern and write concern majority, Jepsen saw anomalies (G-single, G1c) that snapshot isolation forbids, which MongoDB attributed to a transaction retry bug. Without majority at commit there was no snapshot guarantee at all, even for read-only transactions.
• YugabyteDB 1.3.1: G2-item cycles in YSQL serializable transactions during master crashes or pauses, fixed in 1.3.1.2-b1.

Jepsen's MySQL run also confirmed that committed transactions survived crashes with innodb_flush_log_at_trx_commit=1.

Retries at the platform level. CockroachDB returns 40001 on conflicts and retries a statement or a batched transaction on its own only while the results fit a 16 KiB buffer; a multi-statement transaction over several round trips needs client retries. Spanner clients retry ABORTED, and Spanner uses wound-wait to avoid deadlock.

:::key What to say "I ask three things of any distributed database: which isolation level, whether it is strict, and what the failure-mode testing says for that version and topology." :::

Choosing a level, and how to say it in an interview

Start from the invariant, not the level. Then pick the narrowest tool that closes the race.

• One row, read then write: use an atomic statement (SET qty = qty - 1 WHERE qty >= 1). Works at Read Committed, no retries.
• A rule across rows that already exist: lock them with FOR UPDATE, or materialize the conflict on one row.
• A rule about absence (no overlap, unique name): unique or exclusion constraint. Otherwise Serializable.
• A rule across many rows, many code paths: Serializable, plus a retry loop.
• Consistent multi-query reports: a snapshot level, or READ ONLY DEFERRABLE in PostgreSQL.
• Several nodes or regions: also ask whether the system is strict, and what a partition does to it.

The retry loop is part of the design. Retry the whole transaction, not the failed statement. Cap attempts and add jittered backoff, so a hot row does not turn into a retry storm. Keep side effects (emails, payments, queue publishes) out of the transaction body: an email sent in attempt one is sent again in attempt two. Use an outbox row written in the same transaction, or an idempotency key.

A five-step answer you can say out loud:

1. State the invariant: "at least one doctor stays on call".
2. Name the anomaly: "under snapshot isolation that is write skew; both transactions read two and write different rows".
3. Name the engine's behaviour: "on PostgreSQL, Repeatable Read allows it; Serializable aborts one".
4. Choose the tool: "I would take FOR UPDATE on the roster rows, or run Serializable and retry on 40001".
5. State the cost: "retries under contention, and I keep side effects out of the transaction".

Follow-ups to expect:

• Why not Serializable everywhere? It costs aborts or blocking across code that never needed it. Escalate for a named anomaly.
• What if the abort rate is high? Find the hot row or sequential scan, shrink the transaction, split the hot counter, or lock up front.
• Does a replica read have the same guarantee? Not automatically. Check the engine and replication mode.
• Is idempotency the same thing? No. Isolation governs concurrent transactions; idempotency governs retries of the same request.

Common wrong statements

The last scene to picture: a reader sails past while writer 2 waits on writer 1's row lock. After writer 1 commits, writer 2 proceeds. In PostgreSQL Read Committed it re-checks its WHERE clause on the new version; in Repeatable Read it gets the serialization error instead.

How to say it in the interview

"I never stop at 'we'll use a transaction'. I say which invariant the code protects, which anomaly would break it on this engine at this level, and which single tool closes the gap: an atomic statement, a lock on the rows I read, a constraint, or Serializable with a retry loop."

Checklist

• Named read skew and dirty write as anomalies separate from lost update and write skew
• Explained how a snapshot works: versions, the visibility rule, and what an old open transaction costs
• Showed write skew and a phantom with runnable SQL, and gave at least three fixes
• Compared serial execution, 2PL and SSI by cost, not just by name
• Knew what each major engine's levels really are: PostgreSQL, InnoDB, Oracle, SQL Server
• Brought up retries on 40001 and deadlocks, and kept side effects out of the retried transaction
• Distinguished serializable from strictly serializable and cited a tested claim, not a brochure

Related lessons

• How to choose a database
• ACID, precisely
• Data models: what each family is built for, and where it fails
• Storage engines: B-trees, LSM-trees, row vs column
• NoSQL modelling in practice: DynamoDB, Cassandra, MongoDB
• Distributed SQL: Spanner, CockroachDB, Aurora and friends