Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 36 ยท Transactions and Raw SQL

Interactive Transactions, Isolation and Retries

Configure Prisma interactive transactions with timeout, maxWait and isolationLevel, prevent lost updates with optimistic concurrency, and retry on write conflicts and deadlocks.

A transaction that succeeds in a quiet test can misbehave when hundreds of requests hit the same rows. This lesson covers the settings that control interactive transactions, the isolation levels that decide what concurrent transactions can see, optimistic concurrency (a simple, portable way to stop lost updates), and how to retry when the database says "try again".

Prisma API snippets are read-only; the TryIts are labelled stand-ins running on node:sqlite and plain Node.

Options for interactive transactions

TypeScript
await prisma.$transaction(
  async (tx) => {
    // ... queries using tx
  },
  {
    maxWait: 5000,   // ms to wait to get a connection from the pool (default 2000)
    timeout: 10000,  // ms the transaction may run before it is rolled back (default 5000)
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
  },
);
  • maxWait: if the connection pool is busy, how long to wait for a free connection before failing.
  • timeout: how long the callback may take. When it runs out, Prisma rolls back and throws an error (code P2028). A timeout is a symptom: either the work inside is slow (move HTTP calls and heavy computing outside) or the pool is starved (see connection pooling).
  • isolationLevel: how strictly the transaction is separated from others.

You can pass the same options to the array form: prisma.$transaction([...], { isolationLevel }).

Isolation levels

Isolation defines which of these anomalies a transaction may observe:

  • Dirty read: reading another transaction's uncommitted data.
  • Non-repeatable read: reading the same row twice and getting different values because someone committed in between.
  • Phantom read: running the same query twice and getting different sets of rows.
Level (Prisma enum) Dirty read Non-repeatable Phantom
ReadUncommitted possible possible possible
ReadCommitted no possible possible
RepeatableRead no no possible*
Serializable no no no

*In PostgreSQL, RepeatableRead also prevents phantoms; the standard allows them. Defaults: PostgreSQL, SQL Server and Oracle default to ReadCommitted; MySQL defaults to RepeatableRead. SQLite is always effectively serializable, and Prisma supports only Serializable there. Not every database supports every level; Prisma throws if you ask for one the provider lacks.

Stricter is not free: Serializable makes the database abort transactions that would produce results impossible in any one-at-a-time order, and your code must retry them.

flowchart LR
  A["ReadUncommitted<br/>fastest, weakest"] --> B["ReadCommitted"] --> C["RepeatableRead"] --> D["Serializable<br/>safest, may abort"]

Move right when correctness matters more than throughput.

Optimistic concurrency

Locking rows is hard to do with an ORM, so a common pattern is optimistic concurrency: add a version column, read it, and write only if it is unchanged. If someone else changed the row first, your update matches zero rows and you know to reload and retry.

PRISMA
model Document {
  id      Int    @id @default(autoincrement())
  body    String
  version Int    @default(0)
}
TypeScript
const result = await prisma.document.updateMany({
  where: { id, version: seenVersion },
  data: { body: newBody, version: { increment: 1 } },
});
if (result.count === 0) throw new Error('Someone else edited this document');

updateMany is used because it accepts non-unique filters and returns a count instead of throwing when nothing matches.

Node.js Stand-in: optimistic locking detects a concurrent edit

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

B's write is refused instead of silently overwriting A's. In an API you would answer with HTTP 409 Conflict and ask B to reload.

Retrying on conflicts

Under Serializable, or whenever two transactions collide, the database may abort one with a serialization failure or deadlock. Prisma reports this as error code P2034 ("Transaction failed due to a write conflict or a deadlock. Please retry your transaction"). The correct response is to run the whole transaction again, a limited number of times, with a short growing delay:

TypeScript
async function withRetry<T>(fn: () => Promise<T>, attempts = 4): Promise<T> {
  for (let i = 1; ; i++) {
    try {
      return await fn();
    } catch (e: any) {
      if (e?.code !== 'P2034' || i >= attempts) throw e;
      await new Promise((r) => setTimeout(r, 25 * 2 ** i + Math.random() * 25));
    }
  }
}

await withRetry(() =>
  prisma.$transaction(async (tx) => { /* ... */ }, { isolationLevel: 'Serializable' }),
);

Three rules: retry only errors that are safe to retry, retry the whole transaction (never half of it), and cap the attempts.

Node.js Stand-in: retry with backoff on a transient conflict

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Pessimistic locking with raw SQL

If you truly need to lock a row while you work on it, Prisma's query API has no FOR UPDATE, so use a raw query inside an interactive transaction (PostgreSQL/MySQL; see raw queries):

TypeScript
await prisma.$transaction(async (tx) => {
  const [row] = await tx.$queryRaw<{ stock: number }[]>`
    SELECT stock FROM "Product" WHERE id = ${id} FOR UPDATE`;
  if (row.stock < qty) throw new Error('Out of stock');
  await tx.product.update({ where: { id }, data: { stock: { decrement: qty } } });
});

Other transactions that try to lock the same row wait until this one commits. Use it for hot rows such as stock counters; prefer optimistic locking for rarely contested data like document edits.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the timeout option of an interactive transaction control?

    timeout limits the running time of the callback; maxWait limits the wait for a connection.
  2. 2. Which isolation level prevents dirty reads, non-repeatable reads and phantoms?

    Serializable behaves as if transactions ran one after another; the cost is possible aborts that you must retry.
  3. 3. In optimistic concurrency, how do you know your update lost the race?

    If another writer already bumped the version, your WHERE clause matches nothing and count is 0.
  4. 4. What is the right reaction to Prisma error P2034?

    P2034 signals a write conflict or deadlock; rerunning the full transaction usually succeeds.

Summary

  • Interactive transactions accept maxWait, timeout and isolationLevel.
  • Stricter isolation prevents more anomalies but can abort transactions; defaults differ by database.
  • Optimistic concurrency with a version column and updateMany stops lost updates portably.
  • Retry the whole transaction on P2034, with a capped attempt count and backoff.
  • Use raw SELECT ... FOR UPDATE only for genuinely hot rows.

Next: Raw Queries with $queryRaw