LLM Best Practices

Prisma Transactions

5 min read

View markdown source

Overview

Prisma does not wrap queries in a transaction by default. Each operation is auto-committed. Use $transaction to group writes that must succeed or fail together. Two forms exist: the array form for independent operations and the interactive callback form for logic that reads before it writes. Choosing the wrong form or the wrong isolation level leads to race conditions, deadlocks, or lost updates.

Use the array form for independent, sequential writes

The array form accepts a list of Prisma query builders and wraps them in a single BEGIN/COMMIT.

await prisma.$transaction([
  prisma.order.create({ data: { userId, total } }),
  prisma.inventory.update({
    where: { sku },
    data: { stock: { decrement: 1 } },
  }),
  prisma.auditLog.create({ data: { event: "order_placed", userId } }),
])
  • All operations commit together or roll back together.
  • Queries inside the array cannot reference the result of a preceding query. Use the callback form when you need intermediate values.
  • The array form does not hold a connection open during application logic. It sends a single batch to postgres, which is efficient for pure write sequences.

Use the interactive callback for read-modify-write logic

When you need to read a row and update it based on what you read, use the callback form. It holds a connection for the duration of the callback.

await prisma.$transaction(async (tx) => {
  const item = await tx.inventory.findUnique({ where: { sku } })
  if (!item || item.stock < 1) throw new Error("out of stock")
  await tx.inventory.update({
    where: { sku },
    data: { stock: { decrement: 1 } },
  })
  await tx.order.create({ data: { userId, total, sku } })
})
  • Use tx (the transaction-scoped client), not the global prisma, inside the callback. Queries on the global client bypass the transaction.
  • Throwing inside the callback rolls back the transaction automatically.
  • The callback holds a connection from the pool for its entire duration. Keep callbacks short to avoid exhausting the pool. See prisma-pooling for pool sizing.

Set isolation level when concurrent writers race on the same rows

The default isolation level in Postgres is READ COMMITTED. It is not safe for the read-modify-write pattern above when two transactions run concurrently.

await prisma.$transaction(
  async (tx) => {
    const item = await tx.inventory.findUnique({ where: { sku } })
    if (!item || item.stock < 1) throw new Error("out of stock")
    await tx.inventory.update({
      where: { sku },
      data: { stock: { decrement: 1 } },
    })
  },
  { isolationLevel: Prisma.TransactionIsolationLevel.Serializable },
)
  • Serializable prevents phantom reads and write skew. Use it when two transactions can each read a row, each decide to act, and both proceed in a way that violates a business invariant.
  • RepeatableRead prevents non-repeatable reads but allows phantoms. Use for aggregation-heavy read transactions.
  • Higher isolation levels increase lock contention and abort rates. Pair with retry logic for serialization failures (SQLSTATE 40001).

Avoid deadlocks by ordering locks consistently

Deadlocks occur when transaction A holds a lock on row 1 and waits for row 2, while transaction B holds row 2 and waits for row 1.

// Consistent ordering: always process skus in sorted order.
const skus = ["sku-b", "sku-a"].sort()
 
await prisma.$transaction(async (tx) => {
  for (const sku of skus) {
    await tx.inventory.update({
      where: { sku },
      data: { stock: { decrement: 1 } },
    })
  }
})
  • Sort multi-row lock acquisition by a stable key (id, sku, slug) across all code paths that touch the same rows.
  • Use SELECT ... FOR UPDATE via $queryRaw when you need to lock rows before reading them in a pattern that cannot be expressed as sorted Prisma writes. See prisma-raw-queries.
  • Monitor pg_locks on postgres for contention. Frequent deadlocks signal a lock ordering bug, not a load problem.

Prefer atomic update operators over read-modify-write for counters

Many counter patterns do not require a read at all. Prisma’s atomic update operators let the database do the arithmetic.

// Increment a counter without a read.
await prisma.post.update({
  where: { id },
  data: { viewCount: { increment: 1 } },
})
 
// Decrement and guard against going negative in one shot.
await prisma.inventory.updateMany({
  where: { sku, stock: { gte: 1 } },
  data: { stock: { decrement: 1 } },
})
  • increment, decrement, multiply, and divide are applied atomically at the database level. No transaction required for single-row updates.
  • updateMany returns { count }. Check the count to detect the case where the guard condition (stock >= 1) prevented the update.

Set maxWait and timeout for long-running callbacks

Interactive transactions hold a connection. Prisma applies default timeouts to prevent runaway locks.

await prisma.$transaction(
  async (tx) => {
    // long-running callback
  },
  {
    maxWait: 5_000, // ms to wait for a connection from the pool
    timeout: 10_000, // ms before the transaction itself is aborted
  },
)
  • maxWait prevents queue buildup when the pool is exhausted.
  • timeout kills transactions that are holding locks too long. Set it lower than your HTTP request timeout so the database cleans up before the client disconnects.

Graph View

Backlinks