Designing Retryable Transactions, Idempotency, and Serializable Workflows

Learning outcomes

Serializable isolation is only production-safe when the application accepts that a correct concurrency decision can be “abort this transaction and try the whole business operation again.” Retries introduce a second correctness problem: if a client repeats a request after a timeout or transaction abort, how do you ensure the business effect happens once? ServiceHub will combine SQLSTATE-aware retry boundaries, an idempotency key, state transitions, and postcondition verification.

1. Retryable is not the same as “catch Exception and run again”

PostgreSQL documents 40001 as the canonical serialization failure and says it is appropriate to retry it unconditionally. It also says retrying 40P01 deadlocks can be advisable. Other codes—such as unique or exclusion violations—sometimes arise from concurrency patterns but can also represent persistent input bugs, so they require domain-specific classification. Authentication failures, syntax errors, violated CHECK constraints, and permission errors are not made correct by sleeping and trying again.

2. Build an idempotent transfer/request model

DROP TABLE IF EXISTS app.ch07_transfer_request;DROP TABLE IF EXISTS app.ch07_wallet;CREATE TABLE app.ch07_wallet (    wallet_id integer PRIMARY KEY,    owner_name text NOT NULL,    balance numeric(12,2) NOT NULL CHECK (balance >= 0));INSERT INTO app.ch07_wallet VALUES(1,'Ava',500.00),(2,'Ben',300.00),(3,'Cara',200.00);CREATE TABLE app.ch07_transfer_request (    request_key text PRIMARY KEY,    from_wallet integer NOT NULL REFERENCES app.ch07_wallet(wallet_id),    to_wallet integer NOT NULL REFERENCES app.ch07_wallet(wallet_id),    amount numeric(12,2) NOT NULL CHECK (amount > 0),    status text NOT NULL CHECK (status IN ('processing','completed','rejected')),    result_from_balance numeric(12,2),    result_to_balance numeric(12,2),    created_at timestamptz NOT NULL DEFAULT clock_timestamp(),    completed_at timestamptz,    CHECK (from_wallet <> to_wallet));

The client supplies request_key and must reuse the same key when retrying the same semantic request. The primary key turns “did this request already create a durable intent/result?” into an atomic database fact.

3. Idempotency starts by claiming the request key

A robust protocol must distinguish “first execution” from “duplicate delivery.” One approach is to insert the request row first. If the key already exists, read and validate the existing request rather than applying the transfer again.

BEGIN ISOLATION LEVEL SERIALIZABLE;INSERT INTO app.ch07_transfer_request(request_key, from_wallet, to_wallet, amount, status)VALUES ('req-20260818-001', 1, 2, 75.00, 'processing')ON CONFLICT (request_key) DO NOTHINGRETURNING request_key;-- If zero rows were returned, fetch the existing request and ensure-- its from/to/amount match the retried semantic request.SELECT request_key, from_wallet, to_wallet, amount, status,       result_from_balance, result_to_balanceFROM app.ch07_transfer_requestWHERE request_key = 'req-20260818-001';

If an existing row uses the same key but different amount or endpoints, treat that as an idempotency-key misuse, not as permission to overwrite history.

4. The transfer itself must preserve business invariants

For a simple transfer, lock the participating wallets in deterministic key order to reduce deadlock risk, verify funds, then update both balances. The sum of all wallet balances should remain constant.

-- Continue only for a newly claimed processing request.SELECT wallet_id, balanceFROM app.ch07_walletWHERE wallet_id IN (1,2)ORDER BY wallet_idFOR UPDATE;-- Application checks wallet 1 has at least 75.00.UPDATE app.ch07_walletSET balance = balance - 75.00WHERE wallet_id = 1  AND balance >= 75.00RETURNING balance AS from_balance;-- Require exactly one source row to have been updated.UPDATE app.ch07_walletSET balance = balance + 75.00WHERE wallet_id = 2RETURNING balance AS to_balance;UPDATE app.ch07_transfer_request AS rSET status = 'completed',    result_from_balance = (SELECT balance FROM app.ch07_wallet WHERE wallet_id = r.from_wallet),    result_to_balance = (SELECT balance FROM app.ch07_wallet WHERE wallet_id = r.to_wallet),    completed_at = clock_timestamp()WHERE request_key = 'req-20260818-001'RETURNING status, result_from_balance, result_to_balance;COMMIT;

status    | result_from_balance | result_to_balance----------+---------------------+------------------completed | 425.00              | 375.00Total wallet balance remains 1000.00.

The source UPDATE must affect exactly one row. If it affects zero because the balance is insufficient, the application should transition the request to a defined rejected outcome or roll back according to the contract; it should not proceed to credit the destination.

5. Duplicate delivery returns the prior result instead of transferring again

BEGIN ISOLATION LEVEL SERIALIZABLE;INSERT INTO app.ch07_transfer_request(request_key, from_wallet, to_wallet, amount, status)VALUES ('req-20260818-001', 1, 2, 75.00, 'processing')ON CONFLICT (request_key) DO NOTHINGRETURNING request_key;-- Expected: 0 rows, because the key already exists.SELECT request_key, from_wallet, to_wallet, amount, status,       result_from_balance, result_to_balanceFROM app.ch07_transfer_requestWHERE request_key = 'req-20260818-001';COMMIT;

A duplicate client request now observes the already completed result. The wallets do not change again. Idempotency is a business protocol, not merely a retry loop.

6. Concurrency: force a Serializable conflict and classify it correctly

To create a simple cross-row invariant, suppose two concurrent requests each decide whether a maintenance reserve may be moved based on the total balance across selected wallets. Both Serializable transactions read the same predicate and then update different rows. PostgreSQL may reject one history with 40001. The lesson is the retry contract, not a specific victim.

-- Session ABEGIN ISOLATION LEVEL SERIALIZABLE;SELECT sum(balance) FROM app.ch07_wallet WHERE wallet_id IN (1,2,3);UPDATE app.ch07_wallet SET balance = balance - 10 WHERE wallet_id = 1;-- Session BBEGIN ISOLATION LEVEL SERIALIZABLE;SELECT sum(balance) FROM app.ch07_wallet WHERE wallet_id IN (1,2,3);UPDATE app.ch07_wallet SET balance = balance - 10 WHERE wallet_id = 2;-- Add corresponding business-state writes as needed, then COMMIT both.-- Depending on the complete dependency structure, PostgreSQL can reject one-- transaction with SQLSTATE 40001. Do not hard-code which session loses.

7. Application retry pseudocode: classify, bound, restart everything

The driver must expose SQLSTATE (often called SQL state or server error code). Match structured codes, not English error strings. Each attempt starts a new transaction and recomputes all decisions. Backoff should be bounded and can include jitter to avoid synchronized clients immediately colliding again.

MAX_ATTEMPTS = 5RETRYABLE = {"40001", "40P01"}for attempt in range(1, MAX_ATTEMPTS + 1):    try:        with new_transaction(isolation="serializable") as tx:            # Re-read everything inside THIS attempt.            request = claim_or_load_idempotency_key(tx, request_key, payload)            if request.status == "completed":                return request.result            result = execute_business_rules(tx, request)            persist_result(tx, request_key, result)            tx.commit()            return result    except DatabaseError as exc:        sqlstate = exc.sqlstate        if sqlstate not in RETRYABLE:            raise        if attempt == MAX_ATTEMPTS:            raise RetryBudgetExhausted() from exc        sleep(bounded_backoff_with_jitter(attempt))raise AssertionError("unreachable")

This is deliberately not tied to psycopg, JDBC, Npgsql, node-postgres, or another driver API. In real code use the driver's documented SQLSTATE field, transaction lifecycle, cancellation, and connection-pool behavior.

8. Timeout and side-effect boundaries

Database retries become dangerous when a transaction performs nontransactional external effects such as sending email, charging an external payment gateway, or commanding equipment. If the database transaction later aborts, PostgreSQL cannot roll those external effects back. Use an outbox/state-machine pattern or another durable protocol so external work is driven from committed state and is itself idempotent.

Keep transactions short. Configure client/server statement and lock timeouts appropriate to the request, and ensure the client can distinguish timeout/cancellation from connection-loss ambiguity. An unknown commit outcome is a different problem from a known 40001 rollback; the idempotency key is what lets the client safely ask, “did request X already complete?”

9. Prove postconditions, not just successful COMMIT

SELECT wallet_id, owner_name, balanceFROM app.ch07_walletORDER BY wallet_id;SELECT sum(balance) AS total_balanceFROM app.ch07_wallet;SELECT request_key, from_wallet, to_wallet, amount, status,       result_from_balance, result_to_balance, completed_atFROM app.ch07_transfer_requestORDER BY request_key;SELECT request_key, count(*)FROM app.ch07_transfer_requestGROUP BY request_keyHAVING count(*) > 1;-- Last query must return 0 rows because request_key is the primary key.

Ava:  425.00Ben:  375.00Cara: 200.00Total: 1000.00Exactly one request row exists for req-20260818-001 and status = completed.

10. Cleanup and chapter synthesis

ROLLBACK;DROP TABLE IF EXISTS app.ch07_transfer_request;DROP TABLE IF EXISTS app.ch07_wallet;

11. Production judgment and bridge to Chapter 08

Concurrency correctness is a joint database/application contract. PostgreSQL supplies MVCC, isolation, locks, deadlock detection, Serializable conflict detection, SQLSTATEs, constraints, and transactional atomicity. Applications must supply retry scope, idempotency, request identity, bounded backoff, external-side-effect discipline, and invariant verification. Chapter 08 descends from logical concurrency into heap pages, tuple headers, TOAST, HOT updates, bloat, and physical evidence—the storage machinery that makes many of Chapter 07's behaviors possible.

Authoritative references

• PostgreSQL 18 — Serialization Failure Handling
• PostgreSQL 18 — Transaction Isolation / Serializable
• PostgreSQL 18 — Application-Level Consistency
• PostgreSQL 18 — Error Codes
• PostgreSQL 18 — INSERT / ON CONFLICT
• PostgreSQL 18 — Explicit Locking