Serializable isolation, in depth: how databases enforce it, what it costs, and how to run it in production

What the guarantee covers, and what it does not

Serializable means equivalent to some serial order of the committed transactions. It does not say which order. In particular it does not promise that the order matches wall-clock time: a transaction that started after another committed could, in principle, be ordered before it. The stronger property that adds real-time order is called strict serializability, and distributed databases often advertise it separately because it needs extra machinery such as synchronized clocks.

Three practical consequences follow. First, the guarantee is about committed transactions; anything an aborted transaction read must be thrown away. Second, in many engines, PostgreSQL included, the guarantee holds only among transactions that run at the serializable level; a transaction at a weaker level writing the same rows can still create an anomaly the serializable ones cannot see. Third, the guarantee stops at the database boundary. An email sent from inside a transaction that later aborts has still been sent.

The anomalies serializable forbids, and the formal definitions behind them, are covered in isolation levels and serializability theory. Here the question is how an engine actually forbids them.

Three ways to enforce it

Strict two-phase locking (2PL). Every read takes a shared lock and every write an exclusive lock, held until commit. Rows are not enough: to stop a concurrent insert from creating a phantom, the engine also locks the gaps or key ranges a query scanned. Conflicts are found at access time and resolved by waiting. The cost shows up as blocked sessions, lock queues and deadlocks that the engine must detect and break by killing a victim. MySQL InnoDB's SERIALIZABLE level works this way: with autocommit disabled, plain SELECTs become locking reads, and next-key locks cover ranges. SQL Server's SERIALIZABLE uses key-range locks for the same purpose.

Serializable snapshot isolation (SSI). Transactions read from a snapshot, as under snapshot isolation, so readers never block writers. The engine additionally records what each transaction read and watches for read-write antidependencies: T1 read a version that T2 later overwrote. Theory shows that every serialization anomaly under snapshot isolation contains a transaction with both an incoming and an outgoing antidependency, the pivot in the diagram. SSI aborts a transaction when it sees that structure. Nobody waits; somebody gets a serialization failure. PostgreSQL has used SSI for its SERIALIZABLE level since version 9.1.

Optimistic and timestamp-ordered schemes. Transactions run without locks, record their read and write sets, and validate at commit that nothing they read has changed in a way that breaks the chosen order. Distributed SQL systems commonly use timestamp ordering with commit-time checks; CockroachDB, for example, runs SERIALIZABLE by default and returns SQLSTATE 40001 when a transaction must restart. The cost is restarts, and long transactions that touch hot data are the ones that restart most.

Not every engine named SERIALIZABLE provides it. Oracle's SERIALIZABLE level is snapshot isolation, so it still allows write skew; its failure is ORA-08177, raised when a row you are updating changed after your snapshot. Check what your engine means before relying on the word.

Inside PostgreSQL SSI

PostgreSQL records reads as predicate locks, visible in pg_locks with mode SIReadLock. They never block anything and cannot take part in a deadlock; they are bookkeeping that lets the engine detect antidependencies when another transaction writes. What gets locked depends on the plan. An index scan locks the index pages and tuples it touched. A sequential scan always takes a relation-level predicate lock, because it read the whole table, so any concurrent write anywhere in that table becomes a potential conflict.

Locks are tracked in a fixed-size shared table, so the engine promotes fine locks to coarse ones as they accumulate: many tuple locks on one page become a page lock, many page locks on one relation become a relation lock. Three settings control this. max_pred_locks_per_transaction (default 64) sizes the shared table and needs a restart. max_pred_locks_per_relation (default -2, meaning the per-transaction value divided by two) is the promotion threshold to a relation lock. max_pred_locks_per_page (default 2) is the threshold to a page lock. Promotion never makes results wrong; it makes the detector see conflicts that are not real, which shows up as extra serialization failures.

Two details surprise people. SIRead locks often outlive the transaction that took them, until overlapping read-write transactions finish, so one long transaction keeps others' bookkeeping alive. And read-only transactions get special treatment: a transaction declared READ ONLY can often prove at start that it cannot be part of an anomaly and skip predicate locking altogether. Declared READ ONLY DEFERRABLE, it waits until it can take such a safe snapshot and then runs with no risk of a serialization failure. It is the only case in which a serializable transaction blocks where a repeatable read one would not, and it is the right mode for long reports and backups that need a consistent view.

-- Who holds predicate locks right now, at what granularity?
SELECT l.relation::regclass AS rel, l.locktype, count(*) AS locks
FROM pg_locks l
WHERE l.mode = 'SIReadLock'
GROUP BY 1, 2
ORDER BY locks DESC;
-- locktype 'relation' rows on busy tables are the first thing to explain.

Worked example: room bookings

A booking service must never double-book a room. The transaction checks for an overlapping booking and inserts if there is none. Under read committed or snapshot isolation, two concurrent requests for the same slot can both see no overlap and both insert: classic write skew, because each wrote a row the other did not read. Under SERIALIZABLE, one of them fails.

CREATE TABLE booking (
  id      bigserial PRIMARY KEY,
  room_id int       NOT NULL,
  starts  timestamptz NOT NULL,
  ends    timestamptz NOT NULL
);

-- Session A and session B both run, interleaved:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM booking
 WHERE room_id = 7 AND starts < '2026-10-02 11:00' AND ends > '2026-10-02 10:00';
-- both see 0
INSERT INTO booking (room_id, starts, ends)
VALUES (7, '2026-10-02 10:00', '2026-10-02 11:00');
COMMIT;
-- the second COMMIT (or an earlier statement) fails:
-- ERROR: could not serialize access due to read/write dependencies among transactions
-- SQLSTATE 40001

That is the guarantee working. The interesting part came in load testing. With 200 rooms and steady traffic, about a third of booking transactions failed with 40001, even though genuine same-room races were rare. The pg_locks query above showed relation-level SIRead locks on booking. There was no index on room_id, so the overlap check was a sequential scan, which locks the whole relation. Every insert into any room was therefore a write into something every concurrent booking had read.

Adding CREATE INDEX ON booking (room_id, starts) changed the plan to an index scan. Predicate locks now covered only the index range for room 7, inserts for other rooms stopped conflicting, and the failure rate fell to the small number of real same-room races. The lesson generalises: under SSI, the plan determines the read footprint, and the read footprint determines the abort rate. For this specific invariant, PostgreSQL also offers an exclusion constraint on a range column, which enforces non-overlap without relying on isolation at all; when a constraint can express the rule, it is usually the cheaper tool.

Retrying correctly

Serializable shifts concurrency control from your code to a retry loop. The basics, retrying the whole transaction on SQLSTATE 40001 rather than the failed statement, are covered in transaction isolation in practice. Three refinements matter at scale: bound the attempts, add jittered backoff so the colliding transactions do not collide again in lockstep, and keep side effects out of the transaction body so a retry cannot repeat them.

import random, time
import psycopg
from psycopg import errors

def run_serializable(conn, work, attempts=5):
    for attempt in range(1, attempts + 1):
        try:
            with conn.transaction():                 # conn opened with autocommit=True
                conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                return work(conn)            # pure database work only
        except (errors.SerializationFailure, errors.DeadlockDetected):
            metrics.increment("db.serialization_retry", tags={"attempt": attempt})
            if attempt == attempts:
                raise
            time.sleep(random.uniform(0, 0.01 * 2 ** attempt))   # jittered backoff

result = run_serializable(conn, book_room)
send_confirmation_email(result)   # after commit, never inside work()

Count retries per transaction type, not just in aggregate. The standard PostgreSQL statistics views do not break out serialization failures, so count them in the application; that metric is your primary signal. A retry rate that climbs with load is normal; one that is high at low load usually means a coarse lock, as in the booking example.

Shrinking the read footprint

Every technique for running serializable well reduces either how much each transaction reads or how long it holds that read open.

• Index the predicates in your checks. An index scan locks a range; a sequential scan locks the table. Run EXPLAIN on every read inside a serializable transaction.
• Keep transactions short. Do not hold a transaction open across network calls or user think time. Set idle_in_transaction_session_timeout so a forgotten session cannot pin predicate locks for hours.
• Declare read-only work. Mark reporting transactions READ ONLY, and use READ ONLY DEFERRABLE for long ones.
• Do not read what you do not need. A SELECT * over a join reads rows that later writes will conflict with. Narrow the query.
• Limit concurrency. A connection pool sized near the core count produces fewer overlapping transactions than hundreds of active sessions, and fewer overlaps mean fewer dangerous structures.
• Raise the lock table only after fixing plans. Increasing max_pred_locks_per_transaction reduces promotion, but it treats the symptom; a sequential scan still locks the relation.

Choosing serializable, and when not to

Serializable earns its cost when invariants span rows and are enforced by application logic: balances across accounts, quotas, scheduling, any check-then-write. It is also a good default for low-to-moderate contention systems whose developers cannot audit every transaction for anomalies. It is a poor fit for hot counters and queues, where every transaction reads and writes the same rows; there, an atomic UPDATE ... SET n = n + 1, a SELECT ... FOR UPDATE SKIP LOCKED queue or a constraint does the job without aborts.

Failure modes

Under two-phase locking engines the failure picture shifts from aborts to waits: watch lock wait time and the deadlock rate described in deadlock detection, and remember that the version history SSI relies on is managed by MVCC, so long transactions also delay cleanup.

What to do next

1. Find out what SERIALIZABLE means in your engine: locking, SSI, or snapshot isolation under another name.
2. List the multi-row invariants your application enforces in code and decide, per transaction type, between serializable, explicit locks and a constraint.
3. Wrap serializable transactions in a bounded, jittered retry helper that catches SQLSTATE 40001 and keeps side effects outside.
4. Emit a retry metric per transaction type and alert when it rises at constant load.
5. Run EXPLAIN on every read inside a serializable transaction and add indexes where a sequential scan appears.
6. Query pg_locks for relation-level SIReadLock entries during peak load; each one needs an explanation.
7. Move long reports to READ ONLY DEFERRABLE and set idle_in_transaction_session_timeout.