skip to content

Would you set client-wide transactionOptions in a Prisma 7 service on PostgreSQL, such as Serializable isolation or longer timeouts, or keep the defaults and opt in per transaction?

level: principalimportance: should knowfreq 32%

answer

  1. transactionOptions sets defaults; each call can override
  2. defaults: maxWait 2000 ms, timeout 5000 ms, database isolation
  3. Serializable everywhere means P2034 retries everywhere
  4. conditional writes before isolation
  5. raise timeouts per call, not globally

basics

~20 s

Usually keep the defaults (database isolation, maxWait 2000 ms, timeout 5000 ms) and opt into Serializable per transaction through one retry helper. Client-wide Serializable makes every transaction need P2034 retries; a longer global timeout lets slow transactions hold pool connections.

solid answer

~40 s

`transactionOptions` on the `PrismaClient` constructor sets `isolationLevel`, `maxWait` and `timeout` for every `$transaction` that does not override them. The defaults are the database's own isolation level (PostgreSQL `ReadCommitted`), 2000 ms and 5000 ms. Setting `Serializable` client-wide removes the need to reason about anomalies, but every transaction can then abort with P2034 under contention. Prisma does not retry, so every call site needs a bounded retry and an idempotent body. Most teams keep the default and keep invariants with conditional writes, unique constraints or nested writes, opting into `Serializable` only for rules over rows they read, through one shared retry helper. Global timeout increases are worse: an interactive transaction holds a pool connection for its whole life, so raise limits on the call that has a measured reason, not for all.

code

typescript · 20 lines
typescript
import { Prisma, PrismaClient } from './generated/prisma/client'
import { PrismaPg } from '@prisma/adapter-pg'

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL })

// Option 1: a client-wide default that every $transaction inherits
export const serializablePrisma = new PrismaClient({
  adapter,
  transactionOptions: { isolationLevel: Prisma.TransactionIsolationLevel.Serializable },
})

// Option 2: keep the defaults (database isolation, maxWait 2000, timeout 5000)
export const prisma = new PrismaClient({ adapter })

// ...and opt in on the one call whose rule spans rows it only reads
export function moveSeat(body: (tx: Prisma.TransactionClient) => Promise<void>) {
  return prisma.$transaction(body, {
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
  })
}

go deeper

for a junior

Know that $transaction calls take options such as isolationLevel, maxWait and timeout, and that the client can set defaults for them.

for a middle

Explain the defaults and how per-call options override transactionOptions.

for a senior

Keep invariants with conditional writes or a version field, and wrap Serializable work in a bounded P2034 retry.

for a principal

Choose the service's default, weigh global Serializable against per-call opt-in, and make the rule enforceable in review.

## What a client-wide default changes Prisma 7 lets a team set transaction defaults once, on the client: ```ts new PrismaClient({ adapter, transactionOptions: { isolationLevel, maxWait, timeout } }) ``` Every `$transaction` call that does not pass its own options inherits them, and any call can override them. Out of the box, `maxWait` is 2000 ms, `timeout` is 5000 ms, and the isolation level is whatever the database is configured with: `ReadCommitted` on PostgreSQL, `RepeatableRead` on MySQL. The question for a lead is whether the service should move those defaults, or leave them and make each transaction that needs more say so. ## Option 1: Serializable for everything The appeal is that no developer has to reason about anomalies, because every transaction behaves as if it ran alone. The costs: - **Every transaction can fail with P2034**, a write conflict or deadlock, whenever it overlaps another. Prisma does not retry. Every call site needs a bounded retry around the whole `$transaction` call, and the body must be safe to run more than once. - **Throughput under contention drops**, because conflicting transactions abort and run again. - Code written for the default can now fail in places nobody planned retries for. ## Option 2: the database default, opt in per transaction Keep `ReadCommitted`, and make each transaction that needs stronger guarantees ask for them. Before reaching for isolation, most invariants are better kept by a single conditional write (an `updateMany` whose `where` holds the check, or a `version` field), by unique constraints, or by a nested write. `Serializable` is then reserved for rules that span rows the code only reads, wrapped in one shared retry helper, like the one the senior question on this leaf builds. The cost is review: someone must spot the transaction that needed more and did not ask. ## The timeouts Raising `timeout` or `maxWait` for the whole client is the tempting fix after a P2028. It is usually the wrong default. An interactive transaction holds one connection from the driver adapter's pool for its whole life, and the docs ask for transactions to be short, with no network calls inside. A longer global timeout lets slow transactions hold connections longer and starve the rest. Keep the defaults, raise them on the one call that has a measured reason, and record why. ## Making the rule enforceable - Put the retry helper and any non-default options in one module, and have code call `withSerializableRetry(...)` rather than passing options by hand. - In review, check every interactive transaction for network calls, slow queries and a missing retry where it opts into Serializable. - If you do set `transactionOptions`, keep it beside the client's construction with a comment explaining why. It changes the behaviour of code far from where it is written. ## Choosing A service with heavy write contention and few cross-row invariants is usually best served by the database default plus conditional writes and per-call opt-in. A small service whose transactions all guard multi-row rules may accept a client-wide `Serializable`, with the retry and idempotency work that comes with it. CockroachDB and SQLite support only `Serializable`, so there the default is already chosen and retries are part of the design.

  • With ReadCommitted as the default, how do you find the transactions that need Serializable?
    Look for interactive transactions that read several rows, decide in code, then write a different row: "at least one doctor stays on call", or a seat count summed over bookings. Two such transactions can each pass the check and together break it (write skew), and on PostgreSQL neither ReadCommitted nor RepeatableRead stops that. A rule on one row is better kept by a conditional write. The cross-row ones go through the shared Serializable helper.
  • What must be true of a transaction body that a retry helper may run again?
    Running it twice must have the same effect as running it once. That is the docs' idempotency rule: no external side effects such as an email or a payment inside the body, and writes that converge, for example through unique constraints or upserts. A Serializable retry reruns the whole callback against the database's current state.

  • Prisma retries Serializable transactions automatically on P2034.
  • Serializable as a global default has no cost for read-heavy code.
  • A longer global timeout is the standard fix for P2028.
  • Per-call options cannot override transactionOptions.
  • PostgreSQL's default isolation level is Serializable.

Assumes Prisma ORM 7, where transactionOptions on the PrismaClient constructor sets default maxWait (2000 ms), timeout (5000 ms) and isolationLevel (the database's own default) for every $transaction call.