Transaction Isolation Levels: MVCC, Snapshot Isolation, Write Skew and SSI (2026)

Two doctors are on call tonight. Each opens the scheduling app, sees that two doctors are on call, and requests to go off duty. Both requests succeed, both transactions commit, and the hospital now has nobody on call. No transaction read dirty data, no row was updated twice, and the database reported no error. Every check the application performed was valid at the moment it ran.

This is write skew, and it is the reason the label on your isolation setting cannot be trusted at face value. What most engineers call “repeatable read” is, in PostgreSQL, Oracle’s serializable mode and several newer engines, really snapshot isolation, which is not serializable. This article explains how multiversion concurrency control works, what each of the transaction isolation levels really guarantees on each major engine, and how serializable snapshot isolation closes the gap without returning to heavy locking.

What this covers: the ANSI anomalies and why Berenson et al. found them inadequate, MVCC mechanics in PostgreSQL, InnoDB and Oracle, snapshot isolation and its anomalies, a runnable two-session write skew repro, how SSI detects dangerous structures, engine-by-engine mappings, vacuum and long-transaction cost, retry loops for SQLSTATE 40001, and a decision guide.

Context and Background

The SQL standard defines four transaction isolation levels in terms of three phenomena. A dirty read sees data from a transaction that has not committed. A non-repeatable read returns different values when the same row is read twice in one transaction. A phantom is a new row that appears in a repeated range query. Read Uncommitted permits all three, Read Committed forbids dirty reads, Repeatable Read additionally forbids non-repeatable reads, and Serializable forbids all three.

These definitions were written with lock-based implementations in mind, and they leave gaps. In their 1995 paper “A Critique of ANSI SQL Isolation Levels”, Berenson, Bernstein, Gray, Melton, O’Neil and O’Neil showed that the phenomena are ambiguous, that they omit anomalies such as lost update and write skew, and that a level can satisfy the ANSI wording while still being non-serializable. The same paper introduced snapshot isolation as a distinct level that the standard did not name. Adya’s later dissertation reframed isolation in terms of dependency graphs between transactions, an approach the Jepsen project now uses to classify anomalies in real systems.

Implementation matters as much as definition. Two-phase locking (2PL) gets serializability by making readers take shared locks and writers take exclusive locks, held until commit. It is simple to reason about and expensive: readers block writers. Optimistic concurrency control (OCC) lets transactions run without locks and validates at commit. Multiversion concurrency control (MVCC) keeps several versions of each row so readers never block writers. Almost every mainstream database now uses MVCC for reads, which is why the mapping from standard level names to behavior differs so much between engines.

For the storage-engine side of versioned data, see our guide to LSM trees, RocksDB compaction and write amplification, where versions are retained until compaction rather than vacuum. For how isolation guarantees stretch across data centers, see multi-region active-active database architecture. The PostgreSQL documentation chapter on transaction isolation is the primary reference for everything PostgreSQL-specific below.

The practical consequence is that “which isolation level am I on” is a question with three parts: which anomalies the level permits, how the engine implements it, and what the application must do when the engine says no. Skipping any of the three is how write skew reaches production.

How MVCC Implements Transaction Isolation Levels

MVCC gives each transaction a consistent view of the database by keeping multiple versions of every row and deciding, per transaction, which version is visible. Readers read old versions while writers create new ones, so reads do not block writes. The isolation level only decides when the visible view is fixed: per statement or per transaction.

Figure 1: Two heap tuple versions of one row. Each snapshot sees the version whose creator committed before the snapshot and whose deleter had not. Vacuum can reclaim a version only after no snapshot needs it, and a long-running transaction pins that horizon.

Figure 1 shows the core idea in PostgreSQL terms. The diagram is illustrative: transaction IDs are invented, but the mechanism matches how PostgreSQL stores versions. An UPDATE does not modify a row in place. It marks the old tuple as deleted and inserts a new tuple. Which tuple a transaction sees depends on its snapshot and on the commit status of the transactions named in the tuple header.

PostgreSQL: versions in the heap, cleaned by vacuum

Every PostgreSQL heap tuple carries two transaction ID fields. xmin is the ID of the transaction that created the version. xmax is the ID of the transaction that deleted or locked it, or zero if none has. An UPDATE sets xmax on the old tuple and writes a new tuple whose xmin is the updater’s ID, with the old tuple’s ctid pointing at the new one. You can see the fields yourself:

CREATE TABLE acct (id int PRIMARY KEY, balance int);
INSERT INTO acct VALUES (1, 500);
SELECT xmin, xmax, ctid, * FROM acct;      -- xmin=creator, xmax=0
UPDATE acct SET balance = 450 WHERE id = 1;
SELECT xmin, xmax, ctid, * FROM acct;      -- new xmin, new ctid

A snapshot is a compact description of which transactions were in flight when it was taken: the lowest still-active transaction ID, the next ID to be assigned, and the list of active IDs between. A tuple is visible if its xmin committed before the snapshot and its xmax is either unset, aborted, or not yet visible to the snapshot. Because visibility is computed from tuple headers, old versions stay in the heap until vacuum proves nobody can need them.

The isolation level controls snapshot lifetime. Under Read Committed, PostgreSQL takes a new snapshot at the start of every statement. Under Repeatable Read and Serializable it takes one snapshot at the first non-transaction-control statement and keeps it until the end. PostgreSQL’s documentation states this directly, and also notes that it accepts Read Uncommitted but treats it as Read Committed, because dirty reads have no meaning when readers only ever see committed versions.

InnoDB and Oracle: versions in undo

InnoDB and Oracle put the newest version in the main structure and reconstruct older versions from undo. In InnoDB, a clustered index record points to a rollback pointer into the undo log, and a consistent read builds an older view by walking undo records back until it finds a version its read view may see. Oracle similarly uses undo segments and the system change number (SCN): for each block a query reads, the database uses undo to build a consistent-read clone as of the query’s start SCN, according to Oracle’s concurrency documentation.

The trade is symmetrical to PostgreSQL’s. Undo-based engines keep the live table compact and avoid heap bloat from dead tuples, but old-version reads cost extra work to reconstruct, and a long-running reader can run out of undo. Oracle names that failure ORA-01555, “snapshot too old”, and mitigates it with an undo retention setting. PostgreSQL never fails a reader that way, but it pays in table bloat instead. Both designs make the same promise: a reader gets a stable view without taking locks that block writers.

What the snapshot does not protect

MVCC solves the reader-writer conflict. It does not by itself solve writer-writer conflict, and it says nothing about decisions made on data the transaction read but did not write. That second gap is where the interesting anomalies live. A snapshot guarantees that what you read stays the same; it does not guarantee that what you read is still true by the time you commit.

ANSI Isolation Levels, Anomalies and What Each Engine Actually Gives You

Before engines diverge, it helps to lay out the anomalies the original ANSI wording missed. Here is the fuller set used in the literature, with the shortest possible illustration of each.

Anomaly	One-line example	ANSI covers it?
Dirty write	T1 and T2 both write x before either commits	Implicitly forbidden in all levels
Dirty read	T2 reads T1’s uncommitted write	Yes
Non-repeatable read	T1 reads x twice, T2 commits a change between	Yes
Phantom	T1 repeats a range query, T2 inserted a matching row	Yes
Lost update	T1 and T2 read x, both write x+1, one increment vanishes	No
Read skew	T1 reads x, T2 updates x and y and commits, T1 reads y	No
Write skew	T1 and T2 read overlapping data, write disjoint rows, break an invariant	No

Lost update

Lost update is the classic read-modify-write race. Under Read Committed, two sessions read a balance of 100, each adds 10 in application code, and each writes 110. One increment is lost. Under PostgreSQL Repeatable Read the second writer fails with could not serialize access due to concurrent update, which is the first-committer-wins rule of snapshot isolation. Under Read Committed the safe fix is to push the arithmetic into SQL (UPDATE acct SET balance = balance + 10) or to take SELECT ... FOR UPDATE before computing.

Read skew

Read skew happens when a transaction sees one half of another transaction’s committed changes. Imagine a transfer of 50 from account X to account Y. A report that reads X before the transfer and Y after sees 50 units vanish. Snapshot isolation prevents it, because both reads come from the same snapshot. Read Committed does not, because each statement can see a different committed state.

Phantoms and range reads

The ANSI phantom anomaly is about predicates, not rows. Locking engines prevent it with range locks: InnoDB’s next-key and gap locks block inserts into scanned ranges under Repeatable Read. Snapshot-based engines prevent phantoms in a different way, by never showing a transaction rows committed after its snapshot. That is why PostgreSQL’s table of anomalies lists phantom reads as “allowed, but not in PG” at Repeatable Read: the standard permits them, the implementation does not exhibit them. The anomaly it still permits is write skew, which is a phantom problem in disguise, as we are about to see.

Engine mapping

The same level name means different things across engines. The following table summarizes the documented defaults and implementations; verify against your exact version before depending on it.

Engine	Default level	Repeatable Read is	Serializable is	Serialization error
PostgreSQL	Read Committed	Snapshot isolation	SSI (since 9.1)	SQLSTATE 40001
MySQL InnoDB	Repeatable Read	Snapshot for plain reads, locks for locking reads and DML	Locking reads: plain SELECT becomes FOR SHARE when autocommit is off	Deadlock error 1213, SQLSTATE 40001
Oracle	Read Committed	Not offered	Snapshot-based, not truly serializable	ORA-08177
CockroachDB	Serializable	Not offered	Serializable by default	40001 restart transaction
Spanner	Serializable with external consistency	Not offered in the default mode	Strict serializability by design	Aborted, retry required

Two rows deserve emphasis. Oracle’s “serializable” is snapshot isolation under another name, and Oracle’s documentation describes the failure mode as ORA-08177 when a row was changed after the transaction began. That blocks lost update but not write skew, the general weakness of snapshot isolation that the Berenson paper analyzed. CockroachDB, in contrast, documents that it runs transactions at serializable by default and offers only Serializable and Read Committed. Spanner, according to Google’s documentation, provides external consistency, a property stronger than serializability, using TrueTime-based commit timestamps. Consult the vendor pages for specifics on your version; Spanner internals are outside this article’s scope.

Snapshot Isolation and Write Skew in Practice

Snapshot isolation (SI) gives every transaction a private snapshot and applies one write rule: first committer wins. If two concurrent transactions write the same row, the second to commit is aborted. Because reads never conflict with anything, SI is fast, and it eliminates dirty reads, non-repeatable reads, read skew and lost updates on a single row. What it cannot see is a conflict between a read in one transaction and a write in another.

Figure 2: Alice and Bob each verify that two doctors are on call, then each takes themselves off. The write sets are disjoint, so first-committer-wins sees no conflict and both commit.

The on-call doctors example, step by step

The scenario follows the canonical example from the snapshot isolation literature. A table lists doctors and whether each is on call. The business rule is that at least one doctor must always be on call. Each request runs the same logic: count the on-call doctors, and if the count is at least two, set the requesting doctor to off call.

Under snapshot isolation both transactions read the same snapshot, where the count is 2. Alice updates her row, Bob updates his, and the write sets do not intersect. First-committer-wins finds nothing to abort. After both commit, the count is zero. The invariant lived in the relationship between rows, and neither transaction wrote the rows the other had read.

The general shape is a read-write antidependency in both directions. T1 reads something T2 later writes, and T2 reads something T1 later writes. In any serial order one of them would have seen the other’s change and refused to proceed. SI has no such ordering, so it cannot enforce the rule.

Reproduce it with two sessions

Run this in two psql terminals against PostgreSQL. The setup uses a single table:

-- Setup (either session)
CREATE TABLE doctors (name text PRIMARY KEY, on_call boolean NOT NULL);
INSERT INTO doctors VALUES ('alice', true), ('bob', true);

-- Session A                                   -- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;         BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors                   SELECT count(*) FROM doctors
  WHERE on_call;            -- 2                 WHERE on_call;            -- 2
UPDATE doctors SET on_call = false             UPDATE doctors SET on_call = false
  WHERE name = 'alice';                          WHERE name = 'bob';
COMMIT;                     -- ok              COMMIT;                     -- ok, invariant broken

Check with SELECT count(*) FROM doctors WHERE on_call; and you get zero. Now reset the table and repeat with BEGIN ISOLATION LEVEL SERIALIZABLE; in both sessions. The second COMMIT (or occasionally an earlier statement) fails with ERROR: could not serialize access due to read/write dependencies among transactions and SQLSTATE 40001. Re-running the failed transaction now sees a count of 1 and correctly declines to proceed.

Three ways to prevent write skew without SSI

Before reaching for Serializable, know the alternatives, because each is a legitimate engineering choice.

Materialize the conflict. Add a row that both transactions must write, such as a single coverage row per shift. Both then write the same row, and first-committer-wins aborts one. This works on any snapshot-isolation engine, but it requires you to know the invariant in advance and it creates a hotspot.

Lock the rows that were read. Use SELECT name FROM doctors WHERE on_call FOR UPDATE at the start of the transaction. The second session blocks until the first commits, then re-evaluates against the new state. This serializes the critical section, at the cost of lock waits and potential deadlocks when row order differs between transactions.

Enforce the invariant in the database. Constraints, exclusion constraints and unique indexes are checked against the latest committed state rather than the snapshot, at least for unique indexes. A well-designed unique key or exclusion constraint turns a race into a constraint violation. This only covers invariants expressible as constraints; the “at least one on call” rule is not one.

Where write skew hides in real systems

Write skew is not an exotic test-suite curiosity. It appears whenever a transaction does a check-then-act across rows. Typical shapes include double-booking a meeting room or seat (check no overlapping booking, insert a booking), claiming a unique username across two tables, enforcing a spending limit across multiple accounts, and assigning the last unit of inventory in two warehouses. The multiple-row, multi-table nature is exactly why single-row tests never catch it. Where the check is a range query and the act is an insert, practitioners call the problem a phantom, because the row that would have changed the check result did not exist at read time to be locked.

Serializable Snapshot Isolation: How SSI Detects the Dangerous Structure

Serializable snapshot isolation keeps SI’s non-blocking reads and adds a monitor that aborts transactions when it detects a pattern that could be non-serializable. The idea comes from Cahill, Röhm and Fekete, who published their SSI algorithm at SIGMOD 2008. Ports and Grittner then built it into PostgreSQL 9.1, described in their 2012 paper “Serializable Snapshot Isolation in PostgreSQL” (arXiv 1208.4179). The authors present PostgreSQL’s release as the first production database to use SSI.

Figure 3: Reads are recorded as non-blocking SIREAD predicate locks. When two consecutive read-write antidependency edges form a dangerous structure, the engine aborts one transaction with SQLSTATE 40001.

The theory in one paragraph

Adya, Liskov and O’Neil, and later Fekete and colleagues, showed that every non-serializable execution under snapshot isolation contains a cycle in the dependency graph, and that every such cycle contains two adjacent rw-antidependency edges where the transaction in the middle is concurrent with both neighbors. An rw-antidependency from T1 to T2 means T1 read a version that T2 later overwrote. SSI does not search for full cycles. It aborts whenever it sees a transaction with both an incoming and an outgoing rw edge to concurrent transactions, the “pivot”. That is a conservative test: it never misses a real cycle but sometimes aborts a transaction that was actually safe, which is a false positive.

What PostgreSQL does

PostgreSQL tracks reads with predicate locks, shown in pg_locks with mode = 'SIReadLock'. As its documentation emphasizes, these locks never block anything and cannot cause deadlocks. They only record who read what, so that a later writer can detect the rw edge. Locks are taken at the finest useful granularity, such as a tuple, and are promoted to a page or relation lock when memory runs short, which increases the false-positive rate. Sequential scans always take a relation-level predicate lock, according to the manual.

Two practical details follow. First, the SIReadLock may outlive the transaction that took it, because it must remain visible to concurrent transactions that overlap. Second, only transactions that write can receive serialization failures, so declaring a transaction READ ONLY removes it from the failure population. The manual also describes SERIALIZABLE READ ONLY DEFERRABLE, the one case where a Serializable transaction blocks: it waits until it can obtain a snapshot that is guaranteed anomaly-free, then runs with no SSI overhead at all.

What you pay for

SSI cost has three components. Memory goes to the predicate-lock table, bounded by max_pred_locks_per_transaction, max_pred_locks_per_relation and max_pred_locks_per_page. CPU goes to conflict checks on every read and write. Retries go to the false positives and true conflicts that abort transactions. The Ports and Grittner paper reports that, in their benchmarks, performance was close to plain snapshot isolation and clearly better than two-phase locking on read-heavy workloads. Treat that as a published result under their workloads, not a prediction for yours: measure your own abort rate under realistic concurrency before choosing.

2PL versus OCC versus SSI

Strict two-phase locking prevents write skew by making the first transaction’s read lock block the second’s write. It is pessimistic: cost is paid as waiting, even when no conflict would have occurred. SSI is optimistic about reads and pays as aborts. Classic OCC, validating read sets at commit, also pays as aborts, but SSI’s rw-edge tracking keeps the read-only fast path and the snapshot semantics. For read-heavy mixed workloads, the SSI approach usually wins on latency, while for high-contention hot rows locking may waste less work than repeated aborts. Which regime you are in is an empirical question.

Engine Behavior in Detail: InnoDB, Oracle and Distributed SQL

MySQL InnoDB: snapshot reads plus locks

InnoDB defaults to Repeatable Read, but its implementation differs from PostgreSQL’s snapshot isolation in an important way. Plain SELECT statements are consistent reads against the snapshot established by the first read. Locking reads (SELECT ... FOR UPDATE), UPDATE and DELETE instead see the latest committed data and take locks. For a search on a unique index with a unique condition, InnoDB locks only the matching index record. For ranges and non-unique conditions it takes gap or next-key locks to block inserts into the scanned range.

The consequence is a split personality inside one transaction. A plain read can show the old state while an UPDATE in the same transaction operates on newer committed rows. The MySQL manual explicitly advises against mixing the two in a Repeatable Read transaction and suggests Serializable if you need to. On the upside, the gap locks mean that a locking read of a range prevents the phantom that would drive write skew, so the doctors example becomes safe in InnoDB if the count uses FOR UPDATE, and unsafe if it uses a plain SELECT.

InnoDB Serializable converts plain SELECT to SELECT ... FOR SHARE when autocommit is disabled. This is classic lock-based serializability, and it brings classic lock-based costs: readers block writers and deadlocks become routine. InnoDB also supports Read Committed, where each consistent read gets a fresh snapshot and gap locks are used only for foreign key and duplicate key checks, so phantoms can appear. Many high-throughput MySQL deployments choose Read Committed to reduce gap-lock contention, accepting the weaker guarantee.

Oracle: snapshot semantics under the Serializable name

Oracle offers three levels: Read Committed (default), Serializable, and Read-only. Its Serializable mode gives the transaction a view as of its start and raises ORA-08177 when it tries to modify a row that another transaction committed since. That is snapshot isolation. It prevents lost update and non-repeatable reads, and it does not prevent write skew. Oracle’s documentation lists the usual responses to ORA-08177: commit what you have, roll back to a savepoint, or roll back and retry. Anyone porting logic that assumed true serializability from another engine to Oracle should add explicit locking or constraints for multi-row invariants.

CockroachDB and Spanner: serializable by design

CockroachDB documents that it runs every transaction at Serializable by default and exposes only Serializable and Read Committed. Retries are the price: a transaction that cannot be ordered serially fails with SQLSTATE 40001 and the message restart transaction. The documentation recommends the SAVEPOINT cockroach_restart protocol or an application-level retry loop with exponential backoff, and notes that small result sets and batched statements allow the server to retry automatically, up to a 16 KiB default results buffer. Read Committed transactions need no client retries but permit anomalies, including write skew-style ones.

Google Spanner takes a different route to strong guarantees: externally consistent transactions using TrueTime timestamps, with lock-free snapshot reads for read-only transactions. The relevant point for this article is the pattern, not the internals. Distributed engines that default to Serializable make retry handling a first-class application concern from day one, whereas engines that default to a weaker level let the problem hide until concurrency rises. For how those engines interact with sharding and routing, see database sharding with consistent and rendezvous hashing, because cross-shard transactions amplify every cost described here.

A note on Read Committed in PostgreSQL

Read Committed is PostgreSQL’s default, and its semantics for concurrent updates are subtle. When an UPDATE finds a row that a concurrent transaction has changed, it waits for that transaction. If it commits, the UPDATE re-evaluates its WHERE clause against the new row version and proceeds if it still matches. This “EvalPlanQual” re-check is why UPDATE acct SET balance = balance + 10 is safe under Read Committed. It also means a multi-row statement can observe a mixture of states. The manual’s example is a DELETE ... WHERE hits = 10 that silently skips a row another transaction changed from 10 to 11. Treat Read Committed as a statement-level guarantee only.

Vacuum, Bloat and the Cost of Long Transactions

MVCC is not free, and the bill arrives as stale versions. Every UPDATE in PostgreSQL leaves a dead tuple. Autovacuum removes dead tuples once no snapshot can still see them, and the key quantity is the xmin horizon: the oldest transaction ID any active snapshot might still need. Vacuum can reclaim only versions deleted before that horizon.

A single long-lived snapshot pins the horizon for the entire cluster. That snapshot might belong to an analytical query, an abandoned BEGIN left in an application connection pool, a replication slot, or a prepared transaction nobody committed. Meanwhile updates continue, dead tuples accumulate, tables and indexes bloat, queries slow, and autovacuum burns I/O without reclaiming anything. Under Repeatable Read and Serializable the effect is worse than under Read Committed, because the snapshot lasts for the whole transaction rather than one statement.

Finding the culprit

Start with pg_stat_activity, which shows xact_start, state and backend_xmin for each session. Sessions in idle in transaction state with an old xact_start are the usual suspects. Also check pg_replication_slots for xmin and catalog_xmin, and pg_prepared_xacts for forgotten two-phase commits.

SELECT pid, state, now() - xact_start AS xact_age, backend_xmin, left(query, 60) AS q
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

Guardrails

Set idle_in_transaction_session_timeout so abandoned transactions are killed instead of pinning the horizon. The PostgreSQL manual lists it among SSI performance advice, together with keeping transactions short and using connection pooling to limit concurrency. For analytical workloads, route long reads to a replica or use SERIALIZABLE READ ONLY DEFERRABLE so they do not accumulate SSI state. On the InnoDB and Oracle side, the equivalent signal is undo growth: a long transaction forces the engine to retain old undo records, and in Oracle can eventually cause ORA-01555 for readers when undo is overwritten. In all three engines, transaction duration is the single most important operational variable in an MVCC database.

Cost summary by engine

Engine	Where old versions live	Cleanup	Failure signal of long transactions
PostgreSQL	Heap tuples, same table	Autovacuum	Table and index bloat, wraparound risk
InnoDB	Undo log (history list)	Purge threads	Growing history list length, slower reads
Oracle	Undo segments	Undo reuse by retention	ORA-01555 snapshot too old
RocksDB-style LSM	SSTables with sequence numbers	Compaction	Space and read amplification

Handling Serialization Failures: Retry Loops That Actually Work

Moving to Repeatable Read or Serializable converts silent corruption into loud errors. This is a good trade, but only if the application handles the errors. Every engine discussed here documents the same rule: a serialization failure means abort the transaction and rerun it from the beginning. PostgreSQL’s manual adds that you must not rely on data read by a transaction that later aborts, and that failures are hard to predict, so the handling must be generic.

Which errors to retry

In PostgreSQL, SQLSTATE 40001 (serialization_failure) covers both the snapshot-isolation conflict and the SSI dependency failure, and 40P01 (deadlock_detected) is retryable too. CockroachDB uses 40001 for transaction restarts. MySQL reports deadlocks as error 1213 with SQLSTATE 40001, and lock wait timeouts as error 1205, which is a different case that may or may not warrant retry. Oracle surfaces ORA-08177 for serializable conflicts and ORA-00060 for deadlocks. Retry only on these codes; a constraint violation or syntax error will fail every time.

A runnable retry wrapper

The function below retries the whole unit of work, uses capped exponential backoff with full jitter, and bounds the attempts. It targets psycopg 3, where the exception exposes sqlstate.

import random, time
import psycopg
from psycopg import errors

RETRYABLE = {"40001", "40P01"}

def run_txn(dsn, work, isolation="serializable", max_attempts=6,
            base=0.02, cap=1.0):
    attempt = 0
    while True:
        attempt += 1
        try:
            with psycopg.connect(dsn) as conn:
                conn.isolation_level = getattr(
                    psycopg.IsolationLevel, isolation.upper())
                with conn.transaction():
                    return work(conn)          # whole unit of work
        except psycopg.Error as e:
            if e.sqlstate not in RETRYABLE or attempt >= max_attempts:
                raise
            sleep = random.uniform(0, min(cap, base * 2 ** attempt))
            time.sleep(sleep)

def go_off_call(conn, name):
    n = conn.execute("SELECT count(*) FROM doctors WHERE on_call").fetchone()[0]
    if n < 2:
        raise RuntimeError("someone must stay on call")
    conn.execute("UPDATE doctors SET on_call = false WHERE name = %s", (name,))

# run_txn("dbname=hospital", lambda c: go_off_call(c, "alice"))

The commit itself can raise the error, so the commit must be inside the try. Here the context manager commits on exit from conn.transaction(), and the exception propagates to the handler. Real code should open connections from a pool rather than per attempt.

Retry hygiene

Make side effects idempotent. If the unit of work sends an email or calls an external API, perform it after the commit, or guard it with an idempotency key, because the work function may execute several times. Do not retry inside a long-lived interactive transaction: the retry must restart from the first statement, so the unit of work has to be a function, not a sequence of ad hoc calls. Cap the attempts and surface the failure, otherwise a hot spot becomes a retry storm that makes contention worse. Our post on retry budgets, jitter and circuit breakers covers the fleet-level version of this concern. Finally, track the retry rate as a metric. A rising 40001 rate is an early warning of contention long before latency percentiles move.

Trade-offs, Gotchas, and What Goes Wrong

Figure 4: A decision flow for picking among transaction isolation levels. Any path that relies on the database to abort a transaction must include a retry loop.

Serializable is not free, and not a switch. Turning it on without a retry loop converts rare anomalies into frequent user-visible errors. SSI false positives mean some aborted transactions would have been safe, and the false-positive rate climbs when predicate locks are promoted from tuple to page to relation granularity. Sequential scans take relation-level predicate locks, so a query that scans a big table can conflict with every concurrent writer to it. Index-friendly queries produce far fewer conflicts than scans.

Read-only does not mean anomaly-free under SI. PostgreSQL’s documentation notes that read-only transactions at Repeatable Read may see a state that corresponds to no serial order. The well-known illustration involves a batch-close report that reads totals between two concurrent transactions’ commits. At Serializable, read-only transactions can participate in a dangerous structure and be aborted, which is why READ ONLY DEFERRABLE exists for reports that must be correct.

Mixed isolation levels are a trap. SSI only protects transactions that all run at Serializable. If one code path runs at Read Committed and writes the rows that serializable code reads, those writes are not tracked, and the guarantee degrades. The rule is operational: pick the level per invariant, then apply it to every code path that touches that invariant.

Unique violations still escape. PostgreSQL’s manual warns that under Serializable, unique-constraint violations can still occur for keys that were checked but not inserted, because the existence check and the insert are not seen as conflicting in the way you might expect. It recommends that all transactions inserting potentially conflicting keys check for existence first. Handle 23505 as a possible retry condition when you follow a check-then-insert pattern.

Explicit locks can duplicate SSI’s job. The manual suggests removing SELECT FOR UPDATE and FOR SHARE where SSI already protects the data, since mixing blocking locks with SSI adds waits and deadlocks without adding safety. The inverse is also true: if you cannot adopt Serializable, explicit locks are the main tool left.

Replicas and caches break the snapshot. A snapshot is consistent within one database node. Reading from a lagging replica for the check and writing to the primary reintroduces the check-then-act race at a different layer, and no isolation level can see it. The same applies to a cache consulted inside the transaction. Cross-node correctness requires either a single-node transaction or a distributed engine, as discussed in our multi-region active-active architecture guide.

Beware of lock timeouts that mask retries. Teams that set short lock timeouts to avoid pile-ups sometimes turn serialization conflicts into generic timeout errors that no retry classifier recognizes. Map every error code deliberately.

Vector and secondary indexes follow the same rules. Index types that do not participate in predicate locking can force coarse relation-level locks, which inflates SSI conflicts. If you run vector search in Postgres alongside transactional work, test the abort rate with the index in place; our comparison of pgvectorscale and pgvector covers index behavior from the search side. I have not verified how specific vector index types interact with predicate locks, so treat that as something to test, not a documented fact.

Practical Recommendations

Start from invariants, not from levels. List every rule that spans more than one row: uniqueness across tables, capacity limits, “at least one” rules, balances that must sum. For each, decide whether a constraint can enforce it. Constraints are the cheapest and most reliable defense, because they hold at every isolation level and every code path.

For invariants a constraint cannot express, choose between materializing the conflict, explicit row locks, and Serializable. If the invariant is localized and the code path is small, a lock on a well-chosen parent row is simple and predictable. If invariants are numerous or hard to enumerate, Serializable with retries is the more robust choice, because it protects code you have not written yet. That is the usual argument for CockroachDB-style defaults and for running PostgreSQL critical paths at Serializable.

Keep Read Committed for what it is good at: single-statement atomic updates, independent inserts, and workloads where occasional statement-level inconsistency is acceptable. Do not run reports that need cross-table consistency at Read Committed.

Whatever you choose, measure. Record the retry rate, the transaction duration distribution, the oldest transaction age, and dead tuple or undo growth. Test with a concurrency harness that runs your critical transactions in parallel against a copy of the schema; the two-session script above is the minimal version.

Checklist

[ ] Every multi-row invariant is listed and has an owner: constraint, lock, or Serializable.
[ ] Every transaction at Repeatable Read or Serializable runs inside a retry wrapper that restarts the whole unit of work.
[ ] Retry classifier covers 40001, 40P01, ORA-08177, ORA-00060 and MySQL 1213 as appropriate.
[ ] Retries use capped exponential backoff with jitter and a hard attempt limit.
[ ] Side effects occur after commit or are idempotent.
[ ] idle_in_transaction_session_timeout and statement timeouts are set.
[ ] Read-only transactions are declared READ ONLY; long reports use a replica or DEFERRABLE.
[ ] All code paths touching an invariant run at the same isolation level.
[ ] Dashboards track 40001 rate, oldest transaction age and bloat or undo growth.
[ ] A two-session test exists for each critical invariant and runs in CI.

Frequently Asked Questions

What are the four transaction isolation levels?

The SQL standard defines Read Uncommitted, Read Committed, Repeatable Read and Serializable, ordered by how many anomalies they forbid. Read Uncommitted allows dirty reads, Read Committed forbids them, Repeatable Read also forbids non-repeatable reads, and Serializable forbids phantoms and promises results equivalent to some serial order. Engines implement these differently: PostgreSQL treats Read Uncommitted as Read Committed, and its Repeatable Read is snapshot isolation. Always check your engine’s documentation rather than trusting the name.

Is snapshot isolation the same as serializable?

No. Snapshot isolation gives each transaction a consistent snapshot and aborts concurrent writers of the same row, but it permits write skew, where two transactions read overlapping data and write different rows. Serializable guarantees an outcome equal to some serial execution. PostgreSQL’s Repeatable Read is snapshot isolation, and Oracle’s Serializable mode is also snapshot-based. PostgreSQL’s Serializable adds SSI monitoring on top, which aborts transactions that could produce a non-serial result.

What is write skew and how do you prevent it?

Write skew happens when two concurrent transactions each read a shared condition, then update different rows in a way that together violates an invariant, such as two on-call doctors both going off duty. Prevent it by enforcing the rule with a constraint, by materializing the conflict into a row both transactions write, by locking the rows that were read with SELECT ... FOR UPDATE, or by running at Serializable and retrying on SQLSTATE 40001.

How does MVCC work in PostgreSQL versus MySQL?

PostgreSQL stores every row version as a separate heap tuple stamped with xmin and xmax, and autovacuum later removes versions no snapshot needs. InnoDB keeps the newest version in the clustered index and rebuilds older versions from the undo log using a read view, with purge threads discarding old undo. The visible behavior is similar for plain reads, but PostgreSQL pays in table bloat while InnoDB pays in undo growth and reconstruction work.

Do I need retry logic if I use Serializable?

Yes. Serializable engines resolve conflicts by aborting a transaction, and PostgreSQL, CockroachDB, Oracle and MySQL all document that the application must rerun the entire transaction. In PostgreSQL the error carries SQLSTATE 40001. Without a retry loop, users see random failures under load. Retry the whole unit of work with capped, jittered exponential backoff, keep side effects idempotent, and cap attempts so contention does not become a retry storm.

Which isolation level should I choose in 2026?

Use Read Committed for simple single-statement updates, and add constraints or row locks for known invariants. Use Serializable with a tested retry wrapper when correctness depends on multi-row checks and you cannot enumerate every race. Repeatable Read or snapshot isolation fits consistent reporting reads and workloads where you have explicitly handled write skew. If you run on CockroachDB or Spanner, you already get serializable behavior and need the retry code regardless.