Serializable isolation

Serializable isolation is the strongest isolation level in the SQL standard: whatever transactions run at the same time, the committed result must equal running them one at a time, in some order. It is the only level that rules out write skew and every other concurrency anomaly, so you can reason about each transaction as if it ran alone. You pay in waiting, in aborted transactions you must retry, or in lost parallelism, depending on how the database builds it.

Picture it first: two siblings and one monthly budget

A family keeps a shared expense sheet with one rule: this month’s spending stays under ₹20,000. ₹15,000 is spent so far. On the same evening, Riya and her brother Arjun each open the sheet, see ₹4,000 of room, buy something for ₹4,000 and add their own line. The month ends at ₹23,000.

Nobody overwrote anybody, and each decision was right for the sheet it saw. The rule broke because neither sheet showed the other’s purchase. The family has three fixes:

• One buyer. Only Mum buys, one request at a time. Always right, but everyone waits in one queue.
• Hold the sheet. Whoever is checking keeps the sheet until the purchase is written down; the other waits, and two people can end up waiting on each other.
• Audit at night. Everyone checks freely. At night Dad asks whether the purchases could have happened one after another, in any order, given what each person saw. If not, one purchase goes back and is redone against the updated sheet.

Each fix makes the sheet look as if purchases happened one at a time, though not necessarily in the order they were made. Map it back: purchases are transactions, the sheet each person saw is a snapshot, the broken rule is write skew, and the fixes are actual serial execution, two-phase locking and serializable snapshot isolation.

What serializable means, precisely

A set of concurrent transactions is serializable if the database ends in the same state, and every transaction read the same values, as in some serial execution of the committed ones. Two details matter:

• Some order, not commit order. The equivalent serial order may put a transaction that committed later before one that committed earlier, as long as everything each one read fits that order.
• No invariant is named. The database doesn’t know your rule; it guarantees that any rule each transaction preserves alone is preserved by the mix. That is why serializable is the only level where “it works when I test it alone” implies “it works in production”.

Two neighbours get confused with it. Linearizability, the C in CAP, is about single operations on single objects: once a write is acknowledged, every later read of that key sees it. Strict serializability combines both: transactions over many objects, in a serial order that also respects real time, so if T1 finished before T2 started, T1 comes first. Spanner calls this external consistency, and its documentation describes it as stronger than both. See strong vs eventual consistency for the wider ladder.

A single-node database that takes each snapshot when a transaction starts rarely shows that stale read, but the standard and PostgreSQL’s documentation promise only serializability. Across machines the gap is real: Spanner pays for real-time order with TrueTime and commit wait, while CockroachDB guarantees serializability regardless of clock skew and adds per-key linearizability only while clocks stay within its configured offset.

Why snapshot isolation is not enough

Snapshot isolation (PostgreSQL’s REPEATABLE READ, Oracle’s SERIALIZABLE, SQL Server’s SNAPSHOT) gives every transaction a consistent snapshot and stops two transactions writing the same row. It ignores what you read, which leaves two gaps.

Write skew: a daily limit across two accounts

Say your payments app enforces a daily UPI limit of ₹1,00,000 per user, summed across all linked bank accounts. Each payment sums today’s transfers for the user, checks the limit and inserts a transfer row. User 42 has sent ₹70,000 today and starts two ₹25,000 payments at once, one from each account.

Berenson et al.’s 1995 critique named this pattern write skew (A5B): read an overlapping set, decide, write disjoint rows. Other shapes of it:

• A username claimed through two tables. Signup checks users and pending_invites, then inserts into one; two signups take the same handle through different tables.
• A rider’s cap on active orders. “At most 3 open orders per delivery rider”: two dispatchers each count 2 and assign one more.
• Any check by predicate, such as overlapping bookings or “one admin must remain”. When the conflicting row doesn’t exist yet, it is a phantom.

Each transaction is correct on its own, which is why write skew survives code review. A serializable database must reject one of the two payments; the worked example below shows how PostgreSQL does it.

The read-only anomaly

People long assumed that under snapshot isolation a read-only transaction always sees a consistent state if the update transactions are fine among themselves. In 2004 Fekete, O’Neil and O’Neil showed otherwise.

Picture a wholesaler’s billing system with a business_day control row and a sales table. A sale reads the current day and inserts a row tagged with it. Close-day increments the day. The read-only day report reads the current day and totals the previous day’s sales.

1. A sale starts and reads day 27.
2. Close-day commits: the business day is now 28.
3. The day report starts, sees day 28, and prints day 27’s total.
4. The sale inserts its row, tagged day 27, and commits.

Without the report, the history equals “sale, then close-day”. With it, no order works: the report saw close-day, so it comes after it; it missed the sale, so it comes before the sale; and the sale read the old day, so it comes before close-day. That cycle printed a day-27 total that was wrong as it was printed. The paper’s diagnosis: snapshot isolation lets transactions commit in an order different from their serial order. A serializable database must abort someone, here typically the sale, even though only a reader saw the problem.

Three ways to build serializability

Every implementation stops dependency cycles, either by preventing conflicts up front (serial execution, locking) or by detecting them and aborting (SSI, optimistic schemes). The general theory lives in concurrency control.

Actually run one transaction at a time

The simplest serializable schedule is a serial one. If transactions are short, in memory and submitted whole (a stored procedure or script), one thread can run thousands per second with no locks.

• VoltDB partitions tables by a column you choose and gives each partition a single-threaded queue; its documentation describes running each stored procedure uninterrupted, without locking or latching. A multi-partition procedure needs a coordinator and is slower.
• Redis executes commands one at a time. A MULTI/EXEC transaction is never interleaved with other clients’ commands, and a Lua script blocks the server while it runs. There is no rollback: a command that fails inside EXEC doesn’t undo the others.

You gain no locks, no deadlocks, no concurrency aborts and high throughput per core. You pay with data in memory, no interactive transactions (one slow statement stalls a partition), and cross-partition work that serializes everything it touches.

Lock what you read: strict two-phase locking

Every read takes a shared lock, every write an exclusive lock, all held until commit. To stop phantoms the engine also locks the gaps a predicate scanned, as index-range locks, so an insert into a range someone read must wait.

• MySQL InnoDB at SERIALIZABLE is REPEATABLE READ except that, per the manual, plain SELECTs become SELECT … FOR SHARE when autocommit is off. With autocommit on, a lone SELECT is its own transaction and takes no locks.
• SQL Server takes key-range locks (RangeS-S for a serializable range scan) that block inserts into the scanned range.

Under InnoDB’s SERIALIZABLE the two payments both take shared locks on the user’s range; each insert then waits for the other’s lock, a deadlock, and InnoDB rolls one back with error 1213. The limit holds.

You gain simple semantics: only deadlock victims need retrying. You pay with readers and writers blocking each other, long reports freezing writes to what they scanned, and more deadlocks under contention (optimistic vs pessimistic locking).

Snapshots plus a watcher: serializable snapshot isolation

Serializable snapshot isolation (SSI), from Cahill, Röhm and Fekete (SIGMOD 2008), keeps snapshot isolation’s non-blocking reads and adds bookkeeping that catches the cycles SI misses. PostgreSQL 9.1 shipped the first production implementation, described by Ports and Grittner (VLDB 2012).

The edge it watches. An rw-antidependency T1 → T2 means T1 read some data and a concurrent T2 wrote a newer version of it. T1 didn’t see T2’s write, so T1 must come first in any equivalent serial order. Most such edges are harmless.

The dangerous structure. Every non-serializable history under SI contains two consecutive rw edges between concurrent transactions, T1 → T2 → T3, where T3 is the first in the cycle to commit. T1 and T3 can be the same transaction, as in the two payments. SSI doesn’t search for cycles; it flags any pivot with an rw edge in and one out, and aborts someone. That is cheap, but some aborts are false positives, because the path closing the cycle may not exist.

How it sees the edges. Each serializable read takes a non-blocking SIRead lock on what it touched: tuples, pages or whole relations, plus index ranges for predicate reads. A write landing on a concurrent transaction’s SIRead lock records an rw edge; reading an old version of a row someone already replaced shows up in MVCC visibility checks. SIRead locks outlive the reader’s commit, until every overlapping transaction has finished.

The refinements that keep aborts rare:

• Commit ordering. A dangerous structure only matters if T3 commits first. If T1 or T2 commits before T3, no abort is needed.
• Safe retry. PostgreSQL waits until T3 commits, then prefers to abort the pivot T2; the retried T2 is no longer concurrent with T3, so it can’t hit the same conflict.
• Read-only rule. If T1 is read-only, the structure is only dangerous when T3 committed before T1’s snapshot was taken.
• Safe snapshots. Once the read-write transactions concurrent with a READ ONLY transaction finish without creating that pattern, it drops its SIRead locks. SERIALIZABLE READ ONLY DEFERRABLE waits up front for such a snapshot, then runs with no SSI overhead and no risk of failure; Ports and Grittner measured a median wait of 1.98 s under heavy load, and never more than 20 s.
• Granularity promotion. To bound memory, fine locks merge into coarse ones. At the defaults, more than 2 locked rows on a page become a page lock (max_pred_locks_per_page), and more than 64 ÷ 2 = 32 locked pages or tuples in one relation become a relation lock (max_pred_locks_per_relation = −2, max_pred_locks_per_transaction = 64). Coarser locks mean more false conflicts.

What you gain: readers never wait for writers, no new deadlocks, and the database enforces every invariant without you naming it. What you pay: CPU and memory for tracking, aborts (some false) to retry, and sensitivity to query plans.

A worked example in PostgreSQL

Back to the daily limit. The table and the two sessions:

CREATE TABLE upi_transfers (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint        NOT NULL,
  account_id bigint        NOT NULL,
  amount     numeric(12,2) NOT NULL,
  day        date          NOT NULL DEFAULT current_date
);
CREATE INDEX ON upi_transfers (user_id, day);
-- user 42 has already sent 70,000 today

-- t1, session A
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT coalesce(sum(amount), 0) FROM upi_transfers
WHERE user_id = 42 AND day = current_date;      -- 70000

-- t2, session B: the same BEGIN and SELECT, also 70000

-- t3, session A: 70000 + 25000 <= 100000, so pay
INSERT INTO upi_transfers (user_id, account_id, amount) VALUES (42, 1, 25000);

-- t4, session B: same check, other account
INSERT INTO upi_transfers (user_id, account_id, amount) VALUES (42, 2, 25000);

-- t5, session A
COMMIT;                                          -- succeeds

-- t6, session B
COMMIT;                                          -- fails, SQLSTATE 40001

ERROR:  could not serialize access due to read/write dependencies among transactions
HINT:  The transaction might succeed if retried.

The error carries SQLSTATE 40001 plus a DETAIL reason code, such as cancelled “on identification as a pivot, during commit attempt”. Nothing blocked: both INSERTs went through at once, as at REPEATABLE READ. In the words of the PostgreSQL wiki’s SSI examples, both transactions proceed until one commits, and the first committer wins; in other interleavings the loser gets the error on its next statement instead. B’s retry sees ₹95,000 and declines the payment.

While both sessions are open you can watch the bookkeeping:

SELECT locktype, relation::regclass, page, tuple, pid
FROM pg_locks
WHERE mode = 'SIReadLock';

Both backends hold SIRead locks on the (user_id, day) index and the heap rows they summed. The granularity depends on the plan: on a tiny table the planner may pick a sequential scan, and each session then locks the whole relation, which still catches this conflict but also flags payments by other users.

Serializable across many machines

Distributed SQL can’t afford one global lock table, so it leans on timestamps (mechanics in concurrency control).

CockroachDB runs every transaction at SERIALIZABLE unless you explicitly ask for READ COMMITTED. Transactions carry hybrid-logical-clock timestamps. A timestamp cache remembers the latest read of each key, so a write arriving “in the past” of a read is pushed to a later timestamp, and the pushed transaction must refresh its reads: prove nothing it read changed in between. If something did, the client gets SQLSTATE 40001 with “restart transaction”, typically RETRY_SERIALIZABLE or RETRY_WRITE_TOO_OLD. Per the retry reference, the database retries single statements and single-batch transactions itself while results stay under a 16 KiB buffer; interactive transactions need your loop.

Spanner’s default serializable level gives external consistency (strict serializability). Read-write transactions use pessimistic locking with wound-wait, where an older transaction aborts a younger one, so deadlocks can’t form; TrueTime commit timestamps make the serial order match real time. An optional optimistic mode validates reads at commit instead, and read-only transactions read a timestamped snapshot without locks.

Both still abort transactions under contention, so the retry contract applies unchanged.

Comparing the approaches

The retry contract

Without retries, users see random errors; with bad retries, you get duplicate side effects or a retry storm. A correct loop:

1. Restarts from BEGIN, reads included. The attempt failed because of what it read.
2. Retries only retryable errors. A constraint violation or a bug surfaces at once.
3. Backs off exponentially, with a cap and full jitter, so colliding clients don’t collide again in lockstep (timeouts, retries and backoff with jitter).
4. Bounds attempts, then fails cleanly to the caller.
5. Keeps side effects out or idempotent. No SMS or payment-gateway call inside the body; record intent in the transaction and act after commit. If the connection dies during COMMIT, you can’t tell whether it committed; an idempotency key checked inside the transaction makes the blind retry safe.

import random, time

RETRYABLE = {"40001", "40P01"}        # serialization failure, deadlock
BASE, CAP, ATTEMPTS = 0.005, 0.5, 6   # seconds, seconds, tries

def run_serializable(db, work, idem_key):
    for attempt in range(ATTEMPTS):
        try:
            with db.transaction(isolation="SERIALIZABLE") as tx:   # BEGIN ... COMMIT
                done = tx.fetch_one(
                    "SELECT result FROM idempotency WHERE key = %s", [idem_key])
                if done:                                  # an earlier attempt committed
                    return done.result
                result = work(tx)                         # all reads and writes, no side effects
                tx.execute("INSERT INTO idempotency (key, result) VALUES (%s, %s)",
                           [idem_key, result])
                return result
        except (db.Error, db.ConnectionLost) as e:
            retryable = isinstance(e, db.ConnectionLost) or e.sqlstate in RETRYABLE
            if not retryable or attempt == ATTEMPTS - 1:
                raise
        time.sleep(random.uniform(0, min(CAP, BASE * 2 ** attempt)))  # capped, full jitter

db stands in for your driver. With these numbers the sleeps before the sixth attempt total at most 5 + 10 + 20 + 40 + 80 = 155 ms, about half that on average with full jitter. Because the idempotency row commits with the work, a retry after a lost COMMIT returns the stored result instead of paying twice.

What it costs

Measured overhead. Ports and Grittner measured PostgreSQL’s SSI at under 7% below snapshot isolation on their benchmarks: 5% on a CPU-bound, in-memory TPC-C-like workload, indistinguishable on a disk-bound one (with serialization failures under 0.25%), and 10 to 20% CPU overhead on a microbenchmark that scans whole tables. Strict 2PL on the same lock manager fell well behind on read-heavy mixes.

Aborts grow with overlap, not load alone. Two things inflate overlap without any real conflict:

• Sequential scans always take a relation-level SIRead lock, so any concurrent serializable write to the table creates an rw edge. Without the (user_id, day) index, every payment would “conflict” with every other.
• Lock promotion, when the predicate lock table runs short, has the same effect.

The PostgreSQL docs’ checklist: declare transactions READ ONLY when possible; cap active connections with a pool (connection pooling); put no more in a transaction than integrity needs; don’t leave sessions idle in transaction; drop explicit locks and SELECT FOR UPDATE that SERIALIZABLE makes redundant; raise the max_pred_locks_* settings if promotion raises failures; and nudge the planner towards index scans (lower random_page_cost, raise cpu_tuple_cost), weighing fewer rollbacks against slower plans.

Do the arithmetic. At RUBiS-like rates, a million transactions a day means 1,000,000 × 0.0003 = 300 retries under SSI and 1,000,000 × 0.0076 = 7,600 under S2PL. Both are cheap. A hot row is not: when most transactions touch the same few rows, every scheme degrades, and the fix is design (contention and double booking).

When to use SERIALIZABLE, and when a targeted fix is better

SERIALIZABLE protects invariants you never wrote down; a targeted fix protects one you can name, usually more cheaply. Choose per invariant:

• Uniqueness or non-overlap. A unique index, partial unique index or PostgreSQL exclusion constraint works at every level and binds every writer, including the admin script.
• One row. An atomic UPDATE … SET balance = balance - 500 WHERE balance >= 500, or SELECT … FOR UPDATE then write (lost update problem).
• Rows sharing a parent. SELECT 1 FROM users WHERE id = 42 FOR UPDATE before summing serializes that user’s payments in any engine. Or materialise the conflict in a row every writer updates, such as daily_spend(user_id, day, total), turning write skew into a plain write-write conflict.
• Many rules, complex logic, moderate contention. Run all writers at SERIALIZABLE behind a retry framework and delete the hand-placed locks. The code stays as simple as the single-user version.
• One row everyone wants. No level helps; queue through a single owner or shard the counter.

All writers must play. PostgreSQL’s guarantee covers sets of serializable transactions; a batch job inserting transfers at READ COMMITTED escapes the tracking and can still create write skew. The docs suggest default_transaction_isolation = 'serializable' and making sure no other level sneaks in.

Mixing levels for readers. A report at REPEATABLE READ can observe the read-only anomaly; at SERIALIZABLE it may be aborted. Long reports should use SERIALIZABLE READ ONLY DEFERRABLE: one wait, then no aborts and no SIRead locks.

Going deeper: how it fails in production

• A long transaction poisons everyone. Conflict data must be kept until every overlapping transaction ends, so one session open for an hour keeps thousands of finished transactions’ bookkeeping alive. PostgreSQL bounds memory by summarising old committed transactions, which raises false positives. The fix is as for MVCC bloat: short transactions and an idle-in-transaction timeout.
• Replicas don’t do SERIALIZABLE. PostgreSQL’s hot standby rejects the level, so a report moved to a replica runs at REPEATABLE READ and can see the read-only anomaly.
• DDL coarsens locks. Rewriting a table moves tuples, so PostgreSQL promotes its page and tuple SIRead locks to relation level; dropping an index does the same for its range locks. Expect a burst of failures during migrations.
• Two-phase commit removes safe retry. A prepared transaction can’t be aborted, so the victim’s retry may hit the same conflict.
• Frameworks that retry the statement. An ORM that re-runs only the failed INSERT keeps the stale SUM.
• InnoDB in autocommit mode. A check in one autocommitted SELECT and a write in another get no protection from SERIALIZABLE at all.

Cheat sheet

How to say it in the interview

“Serializable means concurrent committed transactions end up as if they ran in some serial order, so a transaction that’s correct alone is correct in any mix. Snapshot isolation isn’t enough: two payments can each read ₹70,000 against a ₹1 lakh daily limit, insert different rows, and both commit. Engines get there by running one transaction at a time per partition, by locking what they read until commit, or, like PostgreSQL, by running on snapshots and aborting when two read-write dependencies line up. That means aborts, so I wrap transactions in a retry loop from BEGIN with jittered backoff and an idempotency key. For one named rule I’d lock the user row or add a constraint; for many rules I’d run all writers at SERIALIZABLE.”

Is serializable the same as linearizable? No. Serializable covers multi-object transactions and allows any serial order, even one putting a later transaction first. Linearizable covers single operations on one object and respects real time. Strict serializability (Spanner’s external consistency) is both.

Why not run everything at SERIALIZABLE? Every transaction then needs retry handling, contention becomes aborts (or waits and deadlocks with locking), and you pay tracking overhead plus false positives. PostgreSQL replicas can’t run it. Many workloads are fine with READ COMMITTED plus targeted locks and constraints.

How does PostgreSQL implement it without blocking readers? On top of snapshot isolation it records non-blocking SIRead locks for what each transaction read. A concurrent write hitting one records an rw-antidependency. When a transaction has an rw edge in and one out, and the third committed first, PostgreSQL aborts one, preferring the pivot so the retry succeeds.

What does CockroachDB do differently? It orders transactions by hybrid-logical-clock timestamps. A write that conflicts with a later read is pushed to a newer timestamp, and the transaction must prove its reads still hold there (read refresh) or restart with 40001. It is SERIALIZABLE by default and retries some cases itself.

How would you test that your code handles it? Replay the two sessions by hand, then fire concurrent identical requests and assert the invariant afterwards. CockroachDB even has a session setting that injects retry errors to exercise your retry path.

Where to go next

• Transaction isolation levels: the full anomaly catalogue and the ANSI levels.
• Concurrency control: conflict graphs, 2PL, OCC and timestamp ordering in depth.
• MVCC: the snapshots SSI builds on.
• Transaction isolation levels in practice: what PostgreSQL and MySQL do at each level.
• Phantom read: predicate conflicts, gap locks and range locks.
• Optimistic vs pessimistic locking: the targeted fixes in detail.
• Contention and double booking: when every transaction wants the same row.
• Timeouts, retries and backoff with jitter: retry policy beyond the database.
• Distributed transactions: two-phase commit vs sagas: when the transaction spans services.

References and further reading

1. PostgreSQL documentation: Transaction Isolation (Serializable Isolation Level)postgresql.orgThe serial-order guarantee, SIRead predicate locks that never block, READ ONLY DEFERRABLE, unique-violation caveats and the list of performance recommendations.
2. PostgreSQL documentation: Lock Management settingspostgresql.orgmax_pred_locks_per_transaction (64), max_pred_locks_per_relation (−2) and max_pred_locks_per_page (2), which control when predicate locks are promoted.
3. Serializable Isolation for Snapshot Databases (Cahill, Röhm and Fekete, SIGMOD 2008)web.archive.orgThe original SSI algorithm: consecutive rw-dependency edges, the inConflict and outConflict flags, SIREAD locks and the Berkeley DB prototype.
4. Serializable Snapshot Isolation in PostgreSQL (Ports and Grittner, VLDB 2012)drkp.netCommit-ordering and read-only optimisations, safe snapshots, deferrable transactions, safe retry, memory bounding, and the SI vs SSI vs S2PL measurements.
5. A Read-Only Transaction Anomaly Under Snapshot Isolation (Fekete, O’Neil and O’Neil, SIGMOD Record 2004)cs.umb.eduShows that a read-only transaction can observe a state no serial order produces, even when the update transactions alone are serializable.
6. MySQL 8.4 Reference Manual: Transaction Isolation Levelsdev.mysql.comInnoDB’s SERIALIZABLE turns plain SELECTs into SELECT … FOR SHARE when autocommit is off.
7. MySQL 8.4 Reference Manual: InnoDB Error Handlingdev.mysql.comA deadlock rolls back the whole transaction; a lock wait timeout rolls back only the statement unless innodb_rollback_on_timeout is set.
8. CockroachDB documentation: Transaction Retry Error Referencecockroachlabs.comSERIALIZABLE by default, SQLSTATE 40001 with “restart transaction”, timestamp pushes, read refreshing and how to reduce retries.
9. Spanner documentation: TrueTime and external consistencycloud.google.comExternal consistency defined, and why it is stronger than both serializability and linearizability.
10. VoltDB documentation: How VoltDB Worksdocs.voltdb.comPartitioned, single-threaded execution of stored procedures without locks, and the cost of multi-partition transactions.