PostgreSQL Transactions with Prisma

A transaction protects a database state transition, not an entire HTTP request. If creating a booking succeeds but its billing row fails, atomicity lets PostgreSQL roll back both database writes so the application does not retain half of the intended state.

Atomic does not mean that concurrent requests cannot conflict, and a database rollback cannot undo an email, payment-provider call, or object-storage write. Choose the smallest Prisma transaction API that matches the dependency between queries.

Pick the transaction shape from the operation

Prisma's current transaction documentation groups common cases this way:

Not every multi-query operation needs an interactive transaction. Extra transaction scope increases connection time, contention, and retry complexity.

Nested writes for dependent records

If billing data belongs to a newly created booking and needs its generated ID, a nested write is focused and atomic:

const booking = await prisma.booking.create({
  data: {
    workspaceId,
    startsAt,
    endsAt,
    status: "PENDING",
    billing: {
      create: {
        currency: "NPR",
        amountMinor: quotedAmountMinor,
        status: "UNPAID",
      },
    },
  },
  include: { billing: true },
});

Prisma creates the related rows in one transaction and returns the requested booking and billing values. If either write violates a constraint, both are rolled back. This is a teaching example; field names and money rules must match the actual schema.

Nested writes are often clearer than manually creating a parent, reading its generated ID, and opening a general interactive callback.

Sequential operations with $transaction([])

Pass Prisma promises in an array when operations are independent but must succeed together:

const [expiredHolds, auditRecord] = await prisma.$transaction([
  prisma.booking.updateMany({
    where: {
      status: "PENDING",
      holdExpiresAt: { lt: new Date() },
    },
    data: { status: "EXPIRED" },
  }),
  prisma.maintenanceEvent.create({
    data: { type: "EXPIRE_BOOKING_HOLDS" },
  }),
]);

The operations execute sequentially in the array order and the resolved array returns each result in the same position. If one fails, Prisma rolls the transaction back.

An array transaction cannot take an auto-generated ID returned by its first operation and inject it into the second. Use a nested write, precompute stable IDs, or use an interactive transaction when later logic genuinely depends on the first result.

Interactive transactions for read-modify-write

An interactive callback provides a transaction-scoped client, conventionally named tx:

import { Prisma } from "@prisma/client";
 
const result = await prisma.$transaction(
  async (tx) => {
    const quote = await tx.workspaceRate.findUniqueOrThrow({
      where: { workspaceId },
    });
 
    const durationMinutes = Math.floor(
      (endsAt.getTime() - startsAt.getTime()) / 60_000,
    );
    if (durationMinutes <= 0) throw new Error("Invalid booking duration");
 
    const amountMinor = calculateQuote(quote, durationMinutes);
 
    return tx.booking.create({
      data: {
        workspaceId,
        startsAt,
        endsAt,
        amountMinor,
        status: "PENDING",
      },
    });
  },
  {
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
    maxWait: 2_000,
    timeout: 5_000,
  },
);

The callback's return value becomes result. Throwing rejects the callback and rolls back its database changes. Use tx, not the global prisma client, for every query intended to participate.

The timeouts above illustrate explicit bounds; use values supported by the installed Prisma version and measured workload. Keep the callback short. Long transactions hold a connection and may retain locks or stale snapshots, increasing conflicts and deadlocks.

Do not call remote services inside the transaction

Payment gateways, email APIs, storage services, and webhooks have unpredictable latency and cannot be rolled back by PostgreSQL. Calling them while a database transaction waits wastes connection capacity and can produce both an external side effect and a database rollback.

A safer payment flow commonly uses an idempotency key and states:

1. In a short transaction, create or find a unique payment attempt in PENDING state.
2. Commit.
3. Call the payment provider using its idempotency key.
4. In another short transaction, record the verified result and related outbox event.
5. Deliver email or webhook work from the outbox after commit.

This is a state machine with compensation, not one distributed database transaction. Enforce a unique idempotency key so a client retry cannot create two charges or bookings.

Isolation levels and concurrent requests

PostgreSQL defaults to Read Committed; Prisma normally uses the database-configured default unless an isolation level is supplied. At Read Committed, each statement can observe data committed before that statement begins, so a prior availability read does not reserve a slot.

Serializable aims to make committed transactions behave as if run one at a time. PostgreSQL may abort one participant with a serialization failure rather than let an unsafe ordering commit. Prisma documents P2034 for write conflicts or deadlocks that should be retried when the operation is safe to retry.

Isolation is not a replacement for constraints. The booking database should still enforce non-overlap as described in Prevent Overlapping Time-Based Bookings in PostgreSQL. A constraint provides the invariant; the transaction groups related changes.

Retry only the complete idempotent unit

Use a bounded retry for recognized transient transaction conflicts, with jittered backoff. Retry the entire transaction from the beginning because every earlier read belonged to the aborted snapshot.

import { Prisma } from "@prisma/client";
 
async function withTransactionRetry<T>(operation: () => Promise<T>) {
  const maxAttempts = 3;
 
  for (let attempt = 1; attempt <= maxAttempts; attempt += 1) {
    try {
      return await operation();
    } catch (error) {
      const retryable =
        error instanceof Prisma.PrismaClientKnownRequestError &&
        error.code === "P2034";
 
      if (!retryable || attempt === maxAttempts) throw error;
      await new Promise((resolve) =>
        setTimeout(resolve, 25 * attempt + Math.random() * 25),
      );
    }
  }
 
  throw new Error("Unreachable transaction retry state");
}

Do not retry validation errors, unique violations that represent a real duplicate, authentication failures, or arbitrary unknown exceptions. If a remote side effect occurred before the error, retrying is safe only when that side effect is idempotent too.

Preserve useful errors

Map only recognized errors at the service or HTTP boundary. A booking exclusion violation can become 409 Conflict; invalid input can become 400; an unknown database or programming error should retain its cause for sanitized server logging and become a generic 500 response.

Avoid this pattern:

try {
  return await createBooking(input);
} catch {
  throw new Error("Transaction failed");
}

It discards the error code, stack, constraint, and cause needed to distinguish conflict from outage. When wrapping adds domain context, use the standard cause option and never send raw database details or submitted secrets to the client.

Common mistakes

• Using an interactive transaction when a nested write expresses the same dependency.
• Starting queries with global prisma inside an interactive tx callback.
• Assuming a preliminary findFirst prevents a concurrent booking.
• Calling a payment or email API while holding the transaction open.
• Retrying every error indefinitely without idempotency.
• Setting Serializable and assuming transactions can no longer abort.
• Catching the exception and replacing it with an uninformative message.

Verification checklist

• Each workflow uses nested, array, bulk, or interactive transactions for a stated reason.
• Dependent writes roll back together and return the values the caller needs.
• Interactive callbacks contain only short database and in-memory work.
• PostgreSQL constraints still enforce unique, foreign-key, and scheduling invariants.
• Recognized serialization/deadlock errors use bounded whole-transaction retries.
• Payment and booking requests have database-enforced idempotency keys.
• Error mapping preserves the original cause in sanitized server diagnostics.

References

Documentation checked on 2026-08-12:

• Prisma: transactions, nested writes, sequential operations, and isolation
• Prisma error reference: P2034
• PostgreSQL transaction isolation
• PostgreSQL application-level consistency and serialization retries