Lesson 17 · Transactions & isolation · Module 2

Serializable and Its Price

Every isolation level below the top one asks you to reason about which anomalies you can live with. SERIALIZABLE ends that conversation — and sends you a bill. The bill is not slowness. It is that your transactions can now fail for reasons that have nothing to do with your code.

The win in this lesson: you will understand that SERIALIZABLE in PostgreSQL is optimistic. It does not make your transaction wait; it makes your transaction fail. And you will know the retry loop that fact forces on every caller.

1. The anomaly a snapshot cannot see

Repeatable Read gives you a stable snapshot. That kills most anomalies, and it feels like enough. It is not, and the reason is a class of bug that only exists when two transactions read overlapping data and then each write somewhere the other read.

"When two concurrent transactions each determine what they are writing based on reading a data set which overlaps what the other is writing, you can get a state which could not occur if either had run before the other. This is known as write skew, and is the simplest form of serialization anomaly against which SSI protects you."

PostgreSQL wiki, SSI

The manual's own example is worth carrying around, because it is small enough to hold in your head. A table mytab, initially:

"Suppose that serializable transaction A computes: SELECT SUM(value) FROM mytab WHERE class = 1; and then inserts the result (30) as the value in a new row with class = 2. Concurrently, serializable transaction B computes: SELECT SUM(value) FROM mytab WHERE class = 2; and obtains the result 300, which it inserts in a new row with class = 1. Then both transactions try to commit. If either transaction were running at the Repeatable Read isolation level, both would be allowed to commit; but since there is no serial order of execution consistent with the result, using Serializable transactions will allow one transaction to commit and will roll the other back."

PostgreSQL 18 documentation, Transaction Isolation · Serializable Isolation Level

Nothing here is a lost update. Neither transaction overwrote the other. Both snapshots were perfectly consistent. Every row each one read was still true at commit time. And the result is still impossible, because the docs finish the thought: "if A had executed before B, B would have computed the sum 330, not 300, and similarly the other order would have resulted in a different sum computed by A."

That is the shape to recognise in your own schema. Two balances that must sum to something. Two on-call doctors where at least one must stay rostered. A seat count and a booking. Any invariant that spans rows nobody is updating — because the rows you read to decide are not the rows you write, and a snapshot only protects the ones you wrote.

2. What SERIALIZABLE actually promises

The standard's definition is unusually blunt:

"The most strict is Serializable, which is defined by the standard in a paragraph which says that any concurrent execution of a set of Serializable transactions is guaranteed to produce the same effect as running them one at a time in some order."

PostgreSQL 18, Transaction Isolation

Read the consequence for your working day rather than the definition:

"The guarantee that any set of successfully committed concurrent Serializable transactions will have the same effect as if they were run one at a time means that if you can demonstrate that a single transaction, as written, will do the right thing when run by itself, you can have confidence that it will do the right thing in any mix of Serializable transactions, even without any information about what those other transactions might do, or it will not successfully commit."

PostgreSQL 18, Serializable Isolation Level

That is an enormous reduction in what you have to hold in your head. You stop reasoning about interleavings. You reason about one transaction, alone, and the database promises the rest — which is exactly the promise a design review can actually check.

But notice the last clause, which is the whole lesson: "or it will not successfully commit". The guarantee is not "your transaction will be correct". It is "your transaction will be correct, or it will be destroyed". There is no third option, and there is no waiting.

3. The mechanism: watching, not waiting

The implementation is Serializable Snapshot Isolation, and the manual describes it as a strict addition to Repeatable Read rather than a different animal:

"In fact, this isolation level works exactly the same as Repeatable Read except that it also monitors for conditions which could make execution of a concurrent set of serializable transactions behave in a manner inconsistent with all possible serial (one at a time) executions of those transactions. This monitoring does not introduce any blocking beyond that present in repeatable read, but there is some overhead to the monitoring, and detection of the conditions which could cause a serialization anomaly will trigger a serialization failure."

PostgreSQL 18, Serializable Isolation Level

The word "locking" appears, and it is the thing most likely to mislead you. Postgres does take predicate locks. They do not block anybody.

"To guarantee true serializability PostgreSQL uses predicate locking, which means that it keeps locks which allow it to determine when a write would have had an impact on the result of a previous read from a concurrent transaction, had it run first. In PostgreSQL these locks do not cause any blocking and therefore can not play any part in causing a deadlock."

PostgreSQL 18, Serializable Isolation Level

So a predicate lock is not a lock in the sense you use the word. It is a record of what you read, kept so that somebody else's write can be recognised, later, as having invalidated your reasoning. It is evidence, not a barrier. You will find it in pg_locks with a mode of SIReadLock.

What Postgres watches for is a pattern, not a collision. A transaction that both reads something another transaction overwrote and writes something a third transaction read is sitting in the middle of a potential cycle. When that structure completes, some transaction has to go.

The wiki puts the resolution rule in one line, and it matters for liveness:

"When there is write skew in SSI, both transactions proceed until one transaction commits. The first committer wins and the other transaction is rolled back… The 'first committer wins' rule ensures that there is progress and that the transaction which is rolled back can immediately be retried."

PostgreSQL wiki, SSI

"Immediately" is doing real work in that sentence. A deadlock victim retries into the same deadlock. A serialization failure victim retries into a world where the winner has already committed — so the second attempt sees a newer snapshot and normally succeeds. The failure is self-clearing.

4. You have seen this shape twice already

Lesson 05's quorums are the third member of the family, one layer out. A quorum write does not lock the replica set. It fires at all N, counts acknowledgements, and checks the witness — did W of them answer? — after the work is done. If the answer is no, the request is declined, not queued. Nobody was ever blocked; a caller was simply told no.

The bottom two rows are the pattern. Optimistic concurrency always moves the same cost to the same place: out of the database's latency budget and into your application's error handling. That is a good trade, and it is only a good trade if you actually do the error handling.

5. The price, item by item

Aborts you did not cause

A transaction can be rolled back because of what two other transactions did. Your SQL is correct; your code is unchanged; the error arrives anyway. The manual is explicit that this is not something you can design your way out of case by case:

"It is important that an environment which uses this technique have a generalized way of handling serialization failures (which always return with an SQLSTATE value of '40001'), because it will be very hard to predict exactly which transactions might contribute to the read/write dependencies and need to be rolled back to prevent serialization anomalies."

PostgreSQL 18, Serializable Isolation Level

"Very hard to predict" is a strong admission in a reference manual. Take it literally: you cannot audit your way to a list of transactions that need retrying. Every one of them does.

Results are not true until you commit

This one catches people who log, cache, or return values from mid-transaction reads:

"When relying on Serializable transactions to prevent anomalies, it is important that any data read from a permanent user table not be considered valid until the transaction which read it has successfully committed. This is true even for read-only transactions… In all other cases applications must not depend on results read during a transaction that later aborted; instead, they should retry the transaction until it succeeds."

PostgreSQL 18, Serializable Isolation Level

Monitoring state, and what makes it degrade

Tracking every read costs memory, and Postgres protects itself by coarsening. Coarser tracking means more false positives, which means more aborts:

"When the system is forced to combine multiple page-level predicate locks into a single relation-level predicate lock because the predicate lock table is short of memory, an increase in the rate of serialization failures may occur. You can avoid this by increasing max_pred_locks_per_transaction, max_pred_locks_per_relation, and/or max_pred_locks_per_page."

PostgreSQL 18, Serializable Isolation Level

And the one that connects this lesson straight back to indexing:

"A sequential scan will always necessitate a relation-level predicate lock. This can result in an increased rate of serialization failures. It may be helpful to encourage the use of index scans by reducing random_page_cost and/or increasing cpu_tuple_cost."

PostgreSQL 18, Serializable Isolation Level

Read that twice. Under SERIALIZABLE, a missing index is no longer only a latency problem. A sequential scan says "I read the entire table", so every concurrent write to that table is now a potential conflict with you. Lesson 08's index is suddenly load-bearing for correctness throughput, not just speed.

The rest of the manual's tuning list is short and worth following as written: declare transactions READ ONLY when possible; control the number of active connections, using a connection pool if needed; "Don't put more into a single transaction than needed for integrity purposes"; don't leave connections "idle in transaction" longer than necessary; and drop the SELECT FOR UPDATE and SELECT FOR SHARE calls that serializable transactions have made redundant.

6. The retry loop

The manual tells you where this belongs, and it is not in each call site:

"When using this technique, it will avoid creating an unnecessary burden for application programmers if the application software goes through a framework which automatically retries transactions which are rolled back with a serialization failure. It may be a good idea to set default_transaction_isolation to serializable."

PostgreSQL 18 documentation, Data Consistency Checks at the Application Level

One wrapper, used everywhere. Roughly this:

import random, time
import psycopg
from psycopg import errors

MAX_ATTEMPTS = 5
BASE_DELAY   = 0.010   # 10 ms

def serializable(pool, work):
    """Run work(cur) under SERIALIZABLE, retrying on SQLSTATE 40001.

    work(cur) MUST be safe to run more than once. It may read, it may
    write, it may raise. It may NOT send an email, charge a card, or
    publish to a queue — collect those and fire them after we return.
    """
    for attempt in range(MAX_ATTEMPTS):
        try:
            with pool.connection() as conn:
                conn.isolation_level = psycopg.IsolationLevel.SERIALIZABLE
                conn.read_only = False
                with conn.cursor() as cur:
                    outcome = work(cur)
                # COMMIT happens on leaving the `with conn` block.
                # This is where 40001 is usually raised — not earlier.
                return outcome
        except errors.SerializationFailure:
            if attempt == MAX_ATTEMPTS - 1:
                raise                       # give up; surface it as 503
            # Full jitter, per Lesson 04. Everyone who lost is retrying.
            time.sleep(random.uniform(0, BASE_DELAY * 2 ** attempt))

# Call site. Note that nothing here knows about retries.
def transfer(cur, src, dst, amount):
    (bal,) = cur.execute(
        "SELECT sum(amount) FROM ledger WHERE account = %s", (src,)
    ).fetchone()
    if bal < amount:
        raise InsufficientFunds(src)        # a real error: do not retry
    cur.execute(
        "INSERT INTO ledger (account, amount) VALUES (%s, %s), (%s, %s)",
        (src, -amount, dst, amount),
    )
    return bal - amount

new_balance = serializable(pool, lambda cur: transfer(cur, a, b, 500))

Four details in there are not decoration.

The failure surfaces at COMMIT. Not at the statement that conflicted. Your except has to wrap the commit, which means it has to wrap the whole connection block — a try around the individual cur.execute calls will catch nothing.

The retry starts from the beginning. A new transaction means a new snapshot; that is the entire point. You cannot re-issue the COMMIT, and you must not reuse any value the failed attempt read — the balance you computed last time is exactly the number that was proved wrong.

Backoff with jitter, from Lesson 04. A serialization failure means somebody else committed. Under contention, many losers are retrying at once, and an unjittered retry re-synchronises them into the same collision. This is the thundering herd with a different hat.

The transaction body must be idempotent. This is the part that turns a clean abstraction into a production incident. A serialization failure rolls back the database work; it cannot roll back the email you sent, the payment you captured, or the message you published from inside the block. Under SERIALIZABLE, the retriable unit is the transaction — so every side effect either moves outside it or becomes something safe to repeat, which is Lesson 04's precondition arriving from a completely different direction. The usual fix is the outbox: write the intent to a table inside the transaction, and let a separate process deliver it after the commit is real.

7. What it still does not cover

Three residual risks survive, and all three have bitten people who believed the guarantee was total.

Unique constraint violations still leak through. The manual is careful about this, because it looks like a bug in the guarantee:

"While PostgreSQL's Serializable transaction isolation level only allows concurrent transactions to commit if it can prove there is a serial order of execution that would produce the same effect, it doesn't always prevent errors from being raised that would not occur in true serial execution. In particular, it is possible to see unique constraint violations caused by conflicts with overlapping Serializable transactions even after explicitly checking that the key isn't present before attempting to insert it."

PostgreSQL 18, Serializable Isolation Level

The fix given is a protocol, not a setting: "This can be avoided by making sure that all Serializable transactions that insert potentially conflicting keys explicitly check if they can do so first." One service that inserts without the select-first check reintroduces the problem for everybody.

It stops at the primary. If your reads have been moved to replicas, the guarantee did not move with them:

"This level of integrity protection using Serializable transactions does not yet extend to hot standby mode or logical replicas. Because of that, those using hot standby or logical replication may want to use Repeatable Read and explicit locking on the primary."

PostgreSQL 18, Data Consistency Checks at the Application Level

This collides directly with Lesson 01. The standard scaling move — push reads to a replica — is the move that quietly downgrades your isolation. A serializable read on a hot standby is not a serializable read.

Mixed isolation levels break the promise. The guarantee is about "any mix of Serializable transactions". A single service still running Read Committed against the same tables is outside the proof, and the docs suggest defending the boundary: set default_transaction_isolation, and "take some action to ensure that no other transaction isolation level is used, either inadvertently or to subvert integrity checks, through checks of the transaction isolation level in triggers."

What you are left with is a good deal, stated honestly. The docs' own summary: "The monitoring of read/write dependencies has a cost, as does the restart of transactions which are terminated with a serialization failure, but balanced against the cost and blocking involved in use of explicit locks and SELECT FOR UPDATE or SELECT FOR SHARE, Serializable transactions are the best performance choice for some environments." Note "some". It is a choice, and you now know what you are paying.

8. Check yourself

9. Back to your world

Two questions are worth asking about any system you are responsible for. First: which invariants span rows that no single statement writes? A limit, a balance, a minimum staffing level, a capacity check. Those are the write-skew candidates, and Repeatable Read will not save them. Second, and more uncomfortable: if the database returned SQLSTATE 40001 to a random one per cent of your write paths tomorrow, what would happen? If the answer is "a 500 reaches the user" or "the email went out twice", the retry wrapper is the work — before the isolation level is, because the wrapper is worth having either way.

Then measure before tuning. Count 40001s per minute, and check whether the transactions raising them are running sequential scans, because a relation-level predicate lock is often the whole story.