Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 36 ยท Transactions and Raw SQL

Transactions: Atomic Writes in Prisma

Understand ACID transactions and the three ways Prisma gives you atomic writes - nested writes, the $transaction array form and interactive transactions - with rollback examples.

Some changes only make sense together. Moving money means one account goes down and another goes up. Placing an order means creating the order and reducing stock. If the program crashes between the two steps, you must not be left with half the work done. A transaction groups several operations into one all-or-nothing unit: either every step is saved (commit) or none is (rollback).

Prisma code needs the npm package and a database, so the Prisma snippets are read-only. The TryIts are labelled stand-ins that run real transactions on node:sqlite.

ACID in one minute

Transactions in relational databases promise four properties:

  • Atomicity: all steps succeed or none do.
  • Consistency: constraints (foreign keys, uniqueness, checks) hold before and after.
  • Isolation: concurrent transactions do not see each other's half-finished work (how strictly depends on the isolation level).
  • Durability: once committed, data survives a crash.

Way 1: nested writes are already transactional

When one Prisma call creates or connects related records, Prisma wraps the whole operation in a transaction for you:

TypeScript
const order = await prisma.order.create({
  data: {
    customer: { connect: { id: 7 } },
    items: {
      create: [
        { productId: 1, quantity: 2 },
        { productId: 4, quantity: 1 },
      ],
    },
  },
});

If the second item fails (for example, an invalid productId), the order and the first item are rolled back. Prefer this form whenever the writes all belong to one root record: it is the simplest and the fastest.

Way 2: the sequential $transaction array

When the operations are independent calls, pass them as an array of unawaited Prisma promises:

TypeScript
const [debited, credited] = await prisma.$transaction([
  prisma.account.update({ where: { id: 1 }, data: { balance: { decrement: 100 } } }),
  prisma.account.update({ where: { id: 2 }, data: { balance: { increment: 100 } } }),
]);

Prisma sends them in order in one transaction and returns an array of results in the same order. If any throws, everything rolls back. The limitation: you cannot use the result of step one as input to step two, because all operations are prepared before the transaction starts. Note the decrement and increment operators: they compute the new value in the database, avoiding the read-then-write mistake described later.

The array may also contain raw queries made with prisma.$executeRaw.

Way 3: interactive transactions

When later steps depend on earlier results, or you need to run logic in between, pass a callback:

TypeScript
await prisma.$transaction(async (tx) => {
  const product = await tx.product.findUniqueOrThrow({ where: { id: 1 } });
  if (product.stock < 2) throw new Error('Not enough stock');

  await tx.product.update({ where: { id: 1 }, data: { stock: { decrement: 2 } } });
  await tx.order.create({ data: { productId: 1, quantity: 2 } });
});

Two rules: use tx, not prisma, for every query inside (a call on the outer client runs outside the transaction), and throw to roll back. If the callback finishes normally Prisma commits; if it throws, Prisma rolls back and rethrows your error. Interactive transactions hold a database connection for their whole duration, so keep them short and never do slow things inside, such as HTTP calls or sending email.

sequenceDiagram
  participant App
  participant DB
  App->>DB: BEGIN
  App->>DB: UPDATE account 1 (-100)
  App->>DB: UPDATE account 2 (+100)
  alt every step succeeded
    App->>DB: COMMIT
  else any step threw
    App->>DB: ROLLBACK
  end

Rollback restores the state from before BEGIN.

Seeing rollback for real

Node.js Stand-in: transfer money with rollback

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.

The failed transfer had already credited Ben before the debit violated the CHECK constraint. The rollback undid the credit, so total money never changed. Without the transaction you would have created 500 out of thin air.

The read-then-write trap

This looks right but is not safe:

TypeScript
const account = await prisma.account.findUniqueOrThrow({ where: { id: 1 } });
await prisma.account.update({ where: { id: 1 }, data: { balance: account.balance - 100 } });

Two requests running at the same time both read 500 and both write 400, so one withdrawal vanishes. Use an atomic operator (decrement) or lock/version the row, which the next lesson covers.

Node.js Stand-in: lost update vs atomic decrement

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.

Which form should I pick?

  • All writes hang off one record: nested write.
  • Independent writes that must succeed together: array $transaction.
  • Logic and reads between writes: interactive transaction, kept short.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens to a nested write such as order.create with items.create if an item fails?

    Nested writes in a single Prisma call run in one transaction, so a failure rolls back the whole operation.
  2. 2. Inside prisma.$transaction(async (tx) => { ... }), which client should you call?

    Only tx runs inside the transaction; calls on the outer client run on other connections and are not rolled back.
  3. 3. How do you roll back an interactive transaction?

    If the callback throws, Prisma rolls back and rethrows; if it returns normally, Prisma commits.
  4. 4. Why is balance: { decrement: 100 } safer than reading the balance and writing balance - 100?

    A read followed by a write allows another request to change the row in between; the operator performs the arithmetic in one UPDATE.

Summary

  • A transaction is all-or-nothing; ACID describes its guarantees.
  • Nested writes are automatically transactional; array $transaction groups independent operations.
  • Interactive transactions give you tx for logic between queries: use tx, throw to roll back, keep them short.
  • Use atomic operators (increment, decrement) instead of read-then-write.
  • The runnable examples are node:sqlite stand-ins; the Prisma calls are read-only.

Next: Interactive Transactions, Isolation and Retries