import { Prisma, PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();
type TxClient = Prisma.TransactionClient;
interface RetryOptions {
maxRetries?: number;
baseDelayMs?: number;
maxDelayMs?: number;
}
const SERIALIZATION_SQLSTATES = new Set(["40001", "40P01"]);
function isSerializationError(err: unknown): boolean {
if (err instanceof Prisma.PrismaClientKnownRequestError) {
if (err.code === "P2034") return true;
const sqlState = (err.meta && (err.meta.code as string)) || "";
if (SERIALIZATION_SQLSTATES.has(sqlState)) return true;
}
const msg = err instanceof Error ? err.message : String(err);
return /could not serialize access|deadlock detected/i.test(msg);
}
function computeDelay(attempt: number, base: number, max: number): number {
const backoff = Math.min(max, base * 2 ** attempt);
return Math.floor(Math.random() * backoff);
}
const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));
export async function retryableTransaction<T>(
fn: (tx: TxClient) => Promise<T>,
opts: RetryOptions = {}
): Promise<T> {
const { maxRetries = 5, baseDelayMs = 25, maxDelayMs = 500 } = opts;
let lastError: unknown;
for (let attempt = 0; attempt <= maxRetries; attempt++) {
try {
return await prisma.$transaction(fn, {
isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
});
} catch (err) {
lastError = err;
if (!isSerializationError(err) || attempt === maxRetries) throw err;
await sleep(computeDelay(attempt, baseDelayMs, maxDelayMs));
}
}
throw lastError;
}
export { prisma };
import { retryableTransaction } from "./retryableTransaction";
interface TransferResult {
fromBalance: number;
toBalance: number;
}
export async function transferFunds(
fromId: string,
toId: string,
amount: number
): Promise<TransferResult> {
if (amount <= 0) throw new Error("amount must be positive");
return retryableTransaction(async (tx) => {
const [from, to] = await Promise.all([
tx.account.findUniqueOrThrow({ where: { id: fromId } }),
tx.account.findUniqueOrThrow({ where: { id: toId } }),
]);
if (from.balance < amount) {
throw new Error("INSUFFICIENT_FUNDS");
}
const [updatedFrom, updatedTo] = await Promise.all([
tx.account.update({
where: { id: fromId },
data: { balance: { decrement: amount } },
}),
tx.account.update({
where: { id: toId },
data: { balance: { increment: amount } },
}),
]);
await tx.ledgerEntry.create({
data: { fromId, toId, amount },
});
return {
fromBalance: updatedFrom.balance,
toBalance: updatedTo.balance,
};
});
}
import { Request, Response } from "express";
import { transferFunds } from "./transferFunds";
import { sendTransferReceipt } from "./notifications";
export async function postTransfer(req: Request, res: Response) {
const { fromId, toId, amount } = req.body as {
fromId: string;
toId: string;
amount: number;
};
try {
const result = await transferFunds(fromId, toId, Number(amount));
// side effects run only after the transaction commits successfully
await sendTransferReceipt(fromId, toId, Number(amount));
return res.status(200).json({ status: "ok", ...result });
} catch (err) {
if (err instanceof Error && err.message === "INSUFFICIENT_FUNDS") {
return res.status(409).json({ error: "insufficient funds" });
}
req.log?.error({ err }, "transfer failed after retries");
return res.status(500).json({ error: "transfer failed" });
}
}
This snippet shows how to run Prisma interactive transactions at the Serializable isolation level and survive the conflicts that isolation level inevitably produces. When two transactions read overlapping data and then write, Postgres may abort one of them with SQLSTATE 40001 (could not serialize access) or 40P01 (deadlock detected). Those errors are not bugs — they are the database telling the application to retry the whole transaction. The correct response is to re-run the transaction body, not to lower the isolation level.
In retryableTransaction, the core is a helper that wraps prisma.$transaction in a bounded loop. Prisma surfaces database errors as a PrismaClientKnownRequestError carrying a code; for raw serialization conflicts the driver code lands in meta.code or the error message, so isSerializationError inspects both the Prisma code (P2034, Prisma's own "transaction failed due to a write conflict or deadlock") and the underlying Postgres SQLSTATE. Only those transient codes are retried — any other error is rethrown immediately so genuine bugs and constraint violations surface fast.
The retry loop uses exponential backoff with jitter via computeDelay, which spreads competing clients apart instead of having them collide again on a fixed schedule. After maxRetries attempts the last error is rethrown so callers still get a real failure rather than an infinite hang. Crucially, the transaction body must be idempotent-on-retry: it receives a fresh tx client each attempt, so all reads and writes go through tx, never the outer prisma, otherwise retried work would escape the transaction.
In transferFunds, the pattern is applied to a classic write-skew scenario: reading two account balances and updating them. Under Serializable, a concurrent transfer touching the same rows will abort one caller, which retryableTransaction transparently retries. The explicit balance check inside the transaction is safe precisely because serialization guarantees the read set stays consistent.
The trade-off is throughput under contention — retries cost latency and CPU — so the isolation level is reserved for correctness-critical paths. A subtle pitfall is doing non-idempotent side effects (sending email, calling external APIs) inside the transaction body; those must be deferred until after the transaction commits, since the body can run several times.
Related snips
export interface RetryOptions {
retries: number;
baseMs: number;
maxMs: number;
signal?: AbortSignal;
onRetry?: (attempt: number, delay: number, err: unknown) => void;Exponential backoff with jitter for retries
package dbutil
import (
"context"
"github.com/jackc/pgconn"Retry Postgres serialization failures with bounded attempts
require "csv"
class PeopleCsvStream
include Enumerable
HEADERS = %w[id full_name email signed_up_at plan].freezeResilient CSV Export as a Streamed Response
class SignupForm
include ActiveModel::Model
include ActiveModel::Attributes
attribute :account_name, :string
attribute :email, :stringShallow Controller, Deep Params: Form Object Pattern
use crossbeam::channel::unbounded;
use std::thread;
fn main() {
let (tx, rx) = unbounded();Crossbeam for advanced concurrent data structures
// Creating a Promise
const myPromise = new Promise((resolve, reject) => {
const success = true;
setTimeout(() => {
if (success) {Promises and async/await patterns for asynchronous JavaScript
Share this code
Here's the card — post it anywhere.
