LLM Best Practices

Prisma Transactions

Last updated 3 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

Prisma auto-commits each operation unless you group them with $transaction. The array form runs independent writes atomically; the interactive callback form is for logic that reads before it writes. The wrong form or isolation level produces lost updates, write skew, and deadlocks.

Use the array form for independent writes

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 } }),
])

The queries run sequentially in one transaction and commit or roll back together. They cannot pass results to each other; use nested writes or the callback form when one write needs another’s generated id.

Use the interactive callback for read-modify-write

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, not the global prisma, inside the callback. Calls on the global client run outside the transaction.
  • Throwing inside the callback rolls back.
  • The callback holds a pooled connection for its whole duration. Keep it short and do no network calls inside it; see prisma-pooling.

Set timeouts deliberately

Interactive transactions default to maxWait: 2000 ms (waiting for a connection) and timeout: 5000 ms (running time).

await prisma.$transaction(fn, { maxWait: 5_000, timeout: 10_000 })

Set timeout below your HTTP request timeout so the database cleans up before the client gives up.

Raise isolation when concurrent writers race

Postgres defaults to ReadCommitted, which allows the read-modify-write race above when two transactions run together. RepeatableRead in Postgres is snapshot isolation: no phantoms, but write skew is still possible. Serializable prevents it.

await prisma.$transaction(fn, { isolationLevel: Prisma.TransactionIsolationLevel.Serializable })

Prisma returns error P2034 for a write conflict or deadlock. Higher isolation raises abort rates, so retry:

async function withRetry<T>(fn: () => Promise<T>, tries = 3): Promise<T> {
  for (let i = 1; ; i++) {
    try {
      return await fn()
    } catch (e) {
      const retryable = e instanceof Prisma.PrismaClientKnownRequestError && e.code === "P2034"
      if (!retryable || i >= tries) throw e
    }
  }
}

Prefer atomic operators to read-modify-write

increment, decrement, multiply, and divide run in the database, so single-row counters need no transaction.

const { count } = await prisma.inventory.updateMany({
  where: { sku, stock: { gte: 1 } },
  data: { stock: { decrement: 1 } },
})
if (count === 0) throw new Error("out of stock")

updateMany returns { count }; zero means the guard condition blocked the update.

Order locks to avoid deadlocks

Deadlocks happen when transaction A holds row 1 and waits for row 2 while B holds row 2 and waits for row 1. Touch rows in a stable order (sorted by id or sku) on every code path. When you must lock rows before reading, use SELECT ... FOR UPDATE through $queryRaw (prisma-raw-queries). Frequent deadlocks mean an ordering bug, not load; check pg_locks on postgres.

Backlinks