Why PostgreSQL SERIALIZABLE Transactions Still Need Retries

When PostgreSQL runs concurrent transactions at SERIALIZABLE isolation, it guarantees that the committed transactions produce the same result as if they ran one at a time. To keep that guarantee, PostgreSQL aborts a transaction if concurrent work would produce a result that no one-at-a-time order could produce. Your code must then retry the entire transaction. PostgreSQL’s serialization failure guidance requires applications using this isolation level to handle serialization errors.

A retry runs the transaction’s database work again, including the reads and decisions that determine what to write. PostgreSQL reports a serialization failure with SQLSTATE 40001, its error code for this failure. The error can occur while a statement runs or when PostgreSQL commits the transaction, so catch it around the entire transaction.

Why PostgreSQL aborts a transaction

Serializable isolation protects rules that depend on several rows or on whether a row exists. For example, a hospital might require at least one doctor to remain on call. Two transactions can each read that two doctors are on call, then take a different doctor off call. If both committed, the final state would not match what either transaction saw.

PostgreSQL tracks dependencies between concurrent reads and writes, meaning which transactions read data that other transactions change. If those dependencies mean the transactions cannot produce their results in any one-at-a-time order, PostgreSQL aborts one instead of committing an inconsistent result. Your code receives an error such as could not serialize access due to read/write dependencies among transactions, with SQLSTATE 40001.

Here is a reproducible schedule. Create the table once:

CREATE TABLE on_call (
    id integer PRIMARY KEY,
    on_duty boolean NOT NULL
);

INSERT INTO on_call (id, on_duty) VALUES
    (1, true),
    (2, true);

Open two database sessions. In session A, start a serializable transaction and read the number of doctors on duty:

BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT count(*) FROM on_call WHERE on_duty;

Before session A commits, run the same commands in session B. Both sessions see a count of 2. Each session then decides to take its own doctor off duty only if the count exceeds one:

-- Session A
UPDATE on_call SET on_duty = false WHERE id = 1;

-- Session B
UPDATE on_call SET on_duty = false WHERE id = 2;

Now commit both transactions. PostgreSQL rejects one with a serialization failure, either during a statement or at commit. The rejected transaction must start over, repeat the count, and make its decision from the new result. It sees one doctor on duty and should leave that doctor on duty.

How a serialization failure differs from a deadlock

A serialization failure means PostgreSQL found that concurrent reads and writes could violate one-at-a-time behavior. A deadlock occurs when transactions wait in a cycle: each needs a database lock, which controls access to data, held by another transaction in that cycle. PostgreSQL breaks the deadlock by aborting one transaction.

The SQLSTATE codes distinguish the errors: 40001 means serialization failure; 40P01 means deadlock detected. PostgreSQL’s failure-handling documentation identifies both codes. Applications often retry deadlocks too, but handle that as a separate error case. Do not retry every database error: a unique-constraint violation, for example, can signal a persistent problem rather than a temporary conflict.

Retry the whole transaction

A safe retry repeats every database read, decision, and write from the start. Retrying only the last failed statement reuses decisions based on the transaction’s snapshot, the view of the data it read before PostgreSQL rejected the transaction. Catch errors around the transaction context too, because a serialization failure can occur while PostgreSQL commits.

This Psycopg 3 pattern retries only SQLSTATE 40001. Set MAX_ATTEMPTS and implement backoff_seconds as application configuration; the example leaves those policies to the caller.

import time
import psycopg

def take_off_duty(conn, doctor_id, max_attempts, backoff_seconds):
    for attempt in range(max_attempts):
        try:
            with conn.transaction():
                conn.execute(
                    "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"
                )

                count = conn.execute(
                    "SELECT count(*) FROM on_call WHERE on_duty"
                ).fetchone()[0]

                if count > 1:
                    conn.execute(
                        "UPDATE on_call SET on_duty = false WHERE id = %s",
                        (doctor_id,),
                    )

            return
        except psycopg.Error as exc:
            if exc.sqlstate != "40001" or attempt + 1 == max_attempts:
                raise

            time.sleep(backoff_seconds(attempt))

The transaction context rolls back when the database raises an error, including during commit. The next loop iteration starts a fresh transaction and reruns the count and decision. Use the driver’s SQLSTATE field instead of matching error-message text, which can change.

Keep external side effects, such as sending a notification, outside the retried transaction or make them safe to repeat. Retrying a transaction does not undo an email or API call that already happened. Retries do not guarantee success. Under contention, PostgreSQL can reject repeated attempts, so stop after a configured limit and report or handle the failure.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com