A retry can recover a transaction rejected because of concurrent work. It cannot guarantee success, and it does not automatically make external effects idempotent. The retry unit should include every database read and application decision that determined the writes. Reusing an amount calculated from the first attempt’s snapshot can defeat the purpose of starting a fresh transaction.

When it breaks

PostgreSQL identifies serialization failures with 40001; deadlocks use 40P01. Either can justify another complete attempt after rollback, within a finite budget. A duplicate identifier producing 23505 may be permanent. Retrying all exceptions indiscriminately wastes capacity and hides bad input. Some uniqueness failures depend on earlier key-selection reads, but their policy requires knowledge of that particular operation.

Errors can appear during statements or commit. A server-declared rejection differs from losing the connection while awaiting commit confirmation: the latter may leave a committed operation whose acknowledgement never reached the caller. Replaying it blindly can duplicate work. A payment or message sent outside the database transaction also survives database rollback; a retry loop alone cannot undo or deduplicate it.

Check it quickly

Use Python 3 on Linux or macOS and PostgreSQL 18’s psql in your executable path. Set PGDATABASE, PGHOST, and any required connection settings to a disposable database where you can create a schema. Arrange authentication beforehand; the example disables interactive password prompts. Save the following program as retry-demo.py and run python3 retry-demo.py. It creates and removes only its own fixture schema; an existing schema with that name makes setup fail.

import os
import random
import re
import selectors
import subprocess
import time

PSQL = ['psql', '-X', '-w', '-qAt', '-v', 'ON_ERROR_STOP=1', '-v', 'VERBOSITY=sqlstate']
ENV = dict(os.environ, PGCONNECT_TIMEOUT='2')


def run(sql):
    return subprocess.run(PSQL, input=sql, text=True, capture_output=True,
                          check=True, timeout=5, env=ENV).stdout.strip()


def read_value(worker):
    deadline = time.monotonic() + 5
    data = bytearray()
    with selectors.DefaultSelector() as ready:
        ready.register(worker.stdout, selectors.EVENT_READ)
        while b'\n' not in data:
            remaining = deadline - time.monotonic()
            if remaining <= 0 or not ready.select(remaining):
                raise TimeoutError('Initial read was not confirmed')
            chunk = os.read(worker.stdout.fileno(), 4096)
            if not chunk:
                raise RuntimeError('Connection ended before initial read')
            data.extend(chunk)
    return int(data.strip())


def attempt(mode, number):
    worker = subprocess.Popen(PSQL, stdin=subprocess.PIPE, stdout=subprocess.PIPE,
                              stderr=subprocess.PIPE, text=True, env=ENV)
    try:
        worker.stdin.write("SET statement_timeout='1s';\n"
                           'BEGIN ISOLATION LEVEL REPEATABLE READ;\n'
                           'SELECT n FROM retry_demo.counter WHERE id=1;\n')
        worker.stdin.flush()
        observed = read_value(worker)
        # A real second connection commits after this attempt's snapshot.
        if mode == 'always' or (mode == 'once' and number == 1):
            run('UPDATE retry_demo.counter SET n=n+1 WHERE id=1;')
        statement = ('INSERT INTO retry_demo.counter VALUES (1,99);' if mode == 'permanent'
                     else f'UPDATE retry_demo.counter SET n={observed + 1} WHERE id=1;')
        finish = r'''
\set ON_ERROR_STOP off
__STATEMENT__
\if :ERROR
  \echo ATTEMPT :SQLSTATE
  ROLLBACK;
\else
  COMMIT;
  \if :ERROR
    \echo ATTEMPT :SQLSTATE
  \else
    \echo ATTEMPT 00000
  \endif
\endif
'''.replace('__STATEMENT__', statement)
        output, _ = worker.communicate(finish, timeout=5)
        match = re.fullmatch(r'ATTEMPT ([0-9A-Z]{5})', output.strip())
        if worker.returncode != 0 or match is None:
            raise RuntimeError('No confirmed transaction outcome; do not replay blindly')
        return match[1], observed
    finally:
        if worker.poll() is None:
            worker.kill()
            worker.communicate()


def increment(mode):
    observations = []
    for number in range(1, 4):
        state, observed = attempt(mode, number)
        observations.append(observed)
        if state not in {'40001', '40P01'} or number == 3:
            return {'attempts': number, 'state': state, 'reads': observations}
        time.sleep(random.uniform(0, 0.01 * 2 ** (number - 1)))


run('CREATE SCHEMA retry_demo; '
    'CREATE TABLE retry_demo.counter (id integer PRIMARY KEY,n integer); '
    'INSERT INTO retry_demo.counter VALUES (1,0);')
try:
    cases = [
        ('once', {'attempts': 2, 'state': '00000', 'reads': [0, 1]}, 2),
        ('permanent', {'attempts': 1, 'state': '23505', 'reads': [0]}, 0),
        ('always', {'attempts': 3, 'state': '40001', 'reads': [0, 1, 2]}, 3),
    ]
    for mode, expected, expected_value in cases:
        run('UPDATE retry_demo.counter SET n=0 WHERE id=1;')
        result = increment(mode)
        value = int(run('SELECT n FROM retry_demo.counter WHERE id=1;'))
        assert result == expected and value == expected_value, (mode, result, value)
        print(mode, result, 'stored', value)
finally:
    run('DROP SCHEMA retry_demo CASCADE;')

The program uses two actual database connections. Each attempt starts Repeatable Read, reads the counter, and calculates a new absolute value. A competing connection can commit between that read and update, forcing the original snapshot’s update to fail. This deliberately exposes whether the retry reads again rather than merely resending the original calculated value.

For the once case, expect two attempts, final state 00000, observations zero then one, and stored value two. One increment belongs to the competing connection and one to the successful retry. The rejected first attempt contributes nothing. If the second attempt reused the old calculated value, it would not preserve that result.

For permanent, expect one attempt, state 23505, observation zero, and stored value zero. The existing primary key makes the requested insert invalid. The classifier stops immediately instead of spending the whole retry allowance on unchanged input. No successful application increment occurred.

For always, expect three attempts, state 40001, observations zero, one, and two, and stored value three. All three stored increments belong to the competing connection; the application never committed its operation. This is deliberate exhaustion, not eventual success. The assertions check both the control-flow report and durable data, because the database value alone would not identify which participant succeeded.

Keep error handling attached to the decision

The example asks psql for its structured SQLSTATE and emits a dedicated result marker. It does not search localized error-message wording. Expected statement errors are followed by rollback; commit errors are checked separately. Missing confirmation, process failure, and transport timeouts escape the retry classifier instead of being guessed into a concurrency category. The initial read and process completion have separate time bounds.

In an application, use the database driver’s transaction and structured error APIs rather than copying this process harness. Include connection acquisition, every attempt, and backoff in the request’s total deadline. Record attempts and the final error, and stop when the caller cancels. Jitter spreads retries but does not repair permanent contention or guarantee fairness.

Give externally visible operations stable identifiers and a durable way to discover their outcome. Reconcile an uncertain commit before reissuing a non-idempotent action. Keep irreversible effects outside a retryable callback unless their delivery protocol explicitly tolerates repetition. These boundaries are what make recovery safe; a small loop and a sleep only implement the scheduling part.

Sources