Transactions

Prisma Intermediate/Advanced

Chapter 1 · Transactions

This course picks up where Prisma Fundamentals left off, using its capstone blog API as the starting project. The first topic is the one that separates a working demo from a trustworthy application: transactions, which make several database changes succeed or fail together, and concurrency control, which stops two people's changes from silently overwriting each other.

Why Transactions Matter

In Prisma Fundamentals, Chapter 7, deleting a user who had posts meant two steps: reassign or delete their posts, then delete the user. Run them as two separate queries, and a crash or error between them leaves the data half-changed.

// Two separate operations: NOT safe
await prisma.post.updateMany({ where: { authorId: 7 }, data: { authorId: 1 } });
// <-- if the server crashes here, the posts have moved but the user still exists
await prisma.user.delete({ where: { id: 7 } });

A transaction groups operations so the database treats them as one unit. Either every change is saved (committed), or none is (rolled back). Other queries never see a half-finished state.

Property	Meaning
Atomic	All the changes happen, or none do
Consistent	The database's rules (unique values, foreign keys) hold before and after
Isolated	Transactions running at the same time don't see each other's unfinished work
Durable	Once committed, changes survive a crash

Together these are known as ACID. Prisma gives you three ways to use transactions.

1. Nested Writes (Already Transactions)

Every nested write from Prisma Fundamentals, Chapter 8 already runs as a transaction. Creating a user with a profile and posts in one create call either saves all of them or none. When the operations are about one record and its relations, a nested write is the simplest choice.

2. Batch Transactions: $transaction([...])

// Reassign a user's posts, then delete the user -- together
const [moved, deleted] = await prisma.$transaction([
  prisma.post.updateMany({ where: { authorId: 7 }, data: { authorId: 1 } }),
  prisma.user.delete({ where: { id: 7 } }),
]);
console.log(`Moved ${moved.count} posts`);

You pass a list of queries, without await on each one. Prisma runs them in order inside one transaction and returns their results in the same order. If any fails, all are rolled back.

Don't await the queries inside the list

Writing await prisma.post.updateMany(...) inside the array runs that query immediately, on its own, before the transaction starts. Build the queries without await, and await only $transaction.

The limitation: every query is decided in advance. You can't look at the result of the first query and decide what the second should do.

3. Interactive Transactions: $transaction(async (tx) => ...)

When later steps depend on earlier results, pass a function instead of a list:

async function deleteUserKeepingPosts(userId: number) {
  return prisma.$transaction(async (tx) => {
    // Find (or create) a placeholder account to own orphaned posts
    const ghost = await tx.user.upsert({
      where:  { email: "deleted@blog.local" },
      update: {},
      create: { email: "deleted@blog.local", name: "Deleted user" },
    });

    if (ghost.id === userId) throw new Error("Can't delete the placeholder account");

    const moved = await tx.post.updateMany({
      where: { authorId: userId },
      data:  { authorId: ghost.id },
    });
    await tx.user.delete({ where: { id: userId } });

    return moved.count;
  });
}

Inside the function, use tx, not prisma. Only queries made through tx are part of the transaction.
If the function throws, everything is rolled back. If it returns, everything is committed, and $transaction returns the value.

Using prisma instead of tx is a silent bug

A query written as prisma.post.update(...) inside the function runs outside the transaction. It commits on its own and isn't rolled back if the transaction fails later. TypeScript won't warn you, so check every line.

Timeouts: keep transactions short

await prisma.$transaction(
  async (tx) => { /* ... */ },
  {
    maxWait: 5000,   // max time to wait to start (default 2000 ms)
    timeout: 10000,  // max time the transaction may run (default 5000 ms)
  },
);

An open transaction holds locks that can block other requests. Prisma cancels and rolls back a transaction that runs past its timeout. Never do slow work inside one — calling an external API, sending an email, waiting for a user. Gather what you need first, keep the transaction to database work, and do the rest after it commits.

Isolation Levels

Isolation controls how much concurrent transactions can see of each other. Stricter levels prevent more anomalies but allow less work to happen in parallel.

Database	Levels Prisma supports
PostgreSQL, MySQL	`ReadUncommitted`, `ReadCommitted`, `RepeatableRead`, `Serializable`
SQL Server	All of those, plus `Snapshot`
SQLite, CockroachDB	`Serializable` only

import { Prisma } from "./generated/prisma/client";

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

With stricter levels, the database may abort a transaction that conflicts with another. Prisma reports this as error P2034, and the right response is simply to try the transaction again:

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

Optimistic Concurrency: Stopping Lost Updates

Two editors open the same post. Ada fixes a typo and saves; a minute later Grace, still looking at the old version, rewrites a paragraph and saves. Grace's save silently wipes out Ada's fix. A transaction doesn't help here, because the two saves happen minutes apart.

The standard fix is a version number. Each save says "update this post only if it's still the version I loaded," and increases the version:

// schema.prisma: add to Post
version Int @default(0)

// Saving an edit
async function savePost(id: number, loadedVersion: number, content: string) {
  const result = await prisma.post.updateMany({
    where: { id, version: loadedVersion },          // only if nobody saved in between
    data:  { content, version: { increment: 1 } },
  });
  if (result.count === 0) {
    throw new Error("Someone else changed this post. Reload and try again.");
  }
}

Ada loads version 3 and saves: the post becomes version 4. Grace, who also loaded version 3, then tries to save — but no post matches version: 3 any more, so nothing is changed and she's told to reload. No locks are held while people are editing; conflicts are simply detected when they happen. That's why it's called optimistic.

Choosing the Right Tool

Situation	Use
Creating or updating a record with its related records	A nested write
Several independent changes that must succeed together	`$transaction([...])`
Later steps depend on earlier results	An interactive transaction
A single counter update	`{ increment: 1 }` — no transaction needed
People editing the same data over minutes	A version field (optimistic concurrency)

Hands-On Exercises

Exercise 1

Using a batch transaction, publish every draft post by one author and set their role to AUTHOR, all or nothing. Then deliberately make the second query fail and prove the first was rolled back.

📄 View solution

Exercise 2

Write an interactive transaction mergeTags(fromName, intoName) that moves every post from one tag to another and then deletes the old tag. It should throw, leaving everything unchanged, if either tag doesn't exist.

📄 View solution

Exercise 3

Add a version field to Post and a PATCH /posts/:slug route to the Fundamentals capstone API that uses it. Simulate two editors saving from the same loaded version and show that the second gets a 409.

📄 View solution

Chapter 1 Quick Reference

A transaction makes several changes commit or roll back together (ACID)
Nested writes are already transactions
$transaction([q1, q2]) runs pre-built queries in order, atomically — don't await them individually
$transaction(async (tx) => ...) for logic between steps; use tx for every query; throw to roll back
Options: maxWait (default 2000 ms), timeout (default 5000 ms), isolationLevel; keep transactions short
SQLite and CockroachDB support only Serializable; retry on P2034 conflicts
Optimistic concurrency: a version field checked in where and incremented on save