Transactions and batch queries

A database transaction is a sequence of read/write operations guaranteed to succeed or fail as a whole (ACID properties: Atomic, Consistent, Isolated, Durable).

Prisma Client supports transactions in several ways:

Nested writes

A nested write performs multiple operations on related records in a single transaction:

// Create user with posts in a single transaction
const user = await prisma.user.create({
  data: {
    email: "alice@prisma.io",
    posts: {
      create: [{ title: "Post 1" }, { title: "Post 2" }],
    },
  },
});

Batch operations

These bulk operations run as transactions:

• createMany() / createManyAndReturn()
• updateMany() / updateManyAndReturn()
• deleteMany()

The $transaction API

Sequential operations

Pass an array of queries to execute sequentially in a transaction:

const [posts, totalPosts] = await prisma.$transaction([
  prisma.post.findMany({ where: { title: { contains: "prisma" } } }),
  prisma.post.count(),
]);

With options:

await prisma.$transaction(
  [prisma.resource.deleteMany({ where: { name: "name" } }), prisma.resource.createMany({ data })],
  { isolationLevel: Prisma.TransactionIsolationLevel.Serializable },
);

Interactive transactions

For complex logic between queries, use interactive transactions:

const result = await prisma.$transaction(async (tx) => {
  const sender = await tx.account.update({
    data: { balance: { decrement: 100 } },
    where: { email: "alice@prisma.io" },
  });

  if (sender.balance < 0) {
    throw new Error("Insufficient funds");
  }

  return await tx.account.update({
    data: { balance: { increment: 100 } },
    where: { email: "bob@prisma.io" },
  });
});

Options:

await prisma.$transaction(
  async (tx) => {
    /* ... */
  },
  {
    maxWait: 5000, // Max wait to acquire transaction (default: 2000ms)
    timeout: 10000, // Max transaction run time (default: 5000ms)
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
  },
);

Transaction isolation level

You can set the transaction isolation level for transactions.

Set the isolation level

To set the transaction isolation level, use the isolationLevel option in the second parameter of the API.

For sequential operations:

await prisma.$transaction(
  [
    // Prisma Client operations running in a transaction...
  ],
  {
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable, // optional, default defined by database configuration
  },
);

For an interactive transaction:

await prisma.$transaction(
  async (prisma) => {
    // Code running in a transaction...
  },
  {
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable, // optional, default defined by database configuration
    maxWait: 5000, // default: 2000
    timeout: 10000, // default: 5000
  },
);

Supported isolation levels

Prisma Client supports the following isolation levels if they are available in the underlying database:

• ReadUncommitted
• ReadCommitted
• RepeatableRead
• Snapshot
• Serializable

The isolation levels available for each database connector are as follows:

By default, Prisma Client sets the isolation level to the value currently configured in your database.

The isolation levels configured by default in each database are as follows:

Database-specific information on isolation levels

See the following resources:

• Transaction isolation levels in PostgreSQL
• Transaction isolation levels in Microsoft SQL Server
• Transaction isolation levels in MySQL

CockroachDB and SQLite only support the Serializable isolation level.

Transaction timing issues

When two or more transactions run concurrently in certain isolation levels, timing issues can cause write conflicts or deadlocks, such as the violation of unique constraints. For example, consider the following sequence of events where Transaction A and Transaction B both attempt to execute a deleteMany and a createMany operation:

1. Transaction B: createMany operation creates a new set of rows.
2. Transaction B: The application commits transaction B.
3. Transaction A: createMany operation.
4. Transaction A: The application commits transaction A. The new rows conflict with the rows that transaction B added at step 2.

This conflict can occur at the isolation level ReadCommitted, which is the default isolation level in PostgreSQL and Microsoft SQL Server. To avoid this problem, you can set a higher isolation level (RepeatableRead or Serializable). You can set the isolation level on a transaction. This overrides your database isolation level for that transaction.

To avoid transaction write conflicts and deadlocks on a transaction:

1. On your transaction, use the isolationLevel parameter to Prisma.TransactionIsolationLevel.Serializable.

   This ensures that your application commits multiple concurrent or parallel transactions as if they were run serially. When a transaction fails due to a write conflict or deadlock, Prisma Client returns a P2034 error.

2. In your application code, add a retry around your transaction to handle any P2034 errors, as shown in this example:

   import { Prisma, PrismaClient } from "../prisma/generated/client";
   
   const prisma = new PrismaClient();
   async function main() {
     const MAX_RETRIES = 5;
     let retries = 0;
   
     let result;
     while (retries < MAX_RETRIES) {
       try {
         result = await prisma.$transaction(
           [
             prisma.user.deleteMany({
               where: {
                 /** args */
               },
             }),
             prisma.post.createMany({
               data: {
                 /** args */
               },
             }),
           ],
           {
             isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
           },
         );
         break;
       } catch (error) {
         if (error.code === "P2034") {
           retries++;
           continue;
         }
         throw error;
       }
     }
   }

Using $transaction within Promise.all()

If you wrap a $transaction inside a call to Promise.all(), the queries inside the transaction will be executed serially (i.e. one after another):

await prisma.$transaction(async (prisma) => {
  await Promise.all([
    prisma.user.findMany(),
    prisma.user.findMany(),
    prisma.user.findMany(),
    prisma.user.findMany(),
    prisma.user.findMany(),
    prisma.user.findMany(),
    prisma.user.findMany(),
    prisma.user.findMany(),
    prisma.user.findMany(),
    prisma.user.findMany(),
  ]);
});

This may be counterintuitive because Promise.all() usually parallelizes the calls passed into it.

The reason for this behaviour is that:

• One transaction means that all queries inside it have to be run on the same connection.
• A database connection can only ever execute one query at a time.
• As one query blocks the connection while it is doing its work, putting a transaction into Promise.all effectively means that queries should be ran one after another.

Dependent writes

Writes are dependent when operations rely on the result of a preceding operation (e.g., using a database-generated ID).

Nested writes for dependent operations

Use nested writes when you need to create related records atomically:

const team = await prisma.team.create({
  data: {
    name: "Aurora Adventures",
    members: {
      create: { email: "alice@prisma.io" },
    },
  },
});

If any operation fails, Prisma Client rolls back the entire transaction.

Independent writes

Writes are independent if they don't rely on the result of a previous operation. Use these for:

• Updating the status of multiple orders to "Dispatched"
• Marking a list of emails as "Read"

Bulk operations

const updateUsers = await prisma.user.updateMany({
  where: { email: { contains: "prisma.io" } },
  data: { role: "ADMIN" },
});

Using $transaction([]) for independent writes

const [deleteResult, createResult] = await prisma.$transaction([
  prisma.post.deleteMany({ where: { authorId: 7 } }),
  prisma.user.delete({ where: { id: 7 } }),
]);

Scenario: Pre-computed IDs and the $transaction([]) API

If you pre-compute IDs (e.g., using UUIDs), you can use either nested writes or $transaction([]) since both operations know the ID upfront.

When to use bulk operations

Consider bulk operations as a solution if:

• ✔ You want to update a batch of the same type of record, like a batch of emails

Scenario: Marking emails as read

You are building a service like gmail.com, and your customer wants a "Mark as read" feature that allows users to mark all emails as read. Each update to the status of an email is an independent write because the emails do not depend on one another - for example, the "Happy Birthday! 🍰" email from your aunt is unrelated to the promotional email from IKEA.

In the following schema, a User can have many received emails (a one-to-many relationship):

model User {
  id    Int       @id @default(autoincrement())
  email           String @unique
  receivedEmails  Email[] // Many emails
}

model Email {
  id      Int     @id @default(autoincrement())
  user    User    @relation(fields: [userId], references: [id])
  userId  Int
  subject String
  body    String
  unread  Boolean
}

Based on this schema, you can use updateMany to mark all unread emails as read:

await prisma.email.updateMany({
  where: {
    user: {
      id: 10,
    },
    unread: true,
  },
  data: {
    unread: false,
  },
});

Can I use nested writes with bulk operations?

No - neither updateMany nor deleteMany currently supports nested writes. For example, you cannot delete multiple teams and all of their members (a cascading delete):

await prisma.team.deleteMany({
  where: {
    id: {
      in: [2, 99, 2, 11],
    },
  },
  data: {
    members: {}, // Cannot access members here
  },
});

Can I use bulk operations with the $transaction([]) API?

Yes — for example, you can include multiple deleteMany operations inside a $transaction([]).

$transaction([]) API

The $transaction([]) API is generic solution to independent writes that allows you to run multiple operations as a single, atomic operation - if any operation fails, Prisma Client rolls back the entire transaction.

Its also worth noting that operations are executed according to the order they are placed in the transaction.

await prisma.$transaction([iRunFirst, iRunSecond, iRunThird]);

Note: Using a query in a transaction does not influence the order of operations in the query itself.

As Prisma Client evolves, use cases for the $transaction([]) API will increasingly be replaced by more specialized bulk operations (such as createMany) and nested writes.

When to use the $transaction([]) API

Consider the $transaction([]) API if:

• ✔ You want to update a batch that includes different types of records, such as emails and users. The records do not need to be related in any way.
• ✔ You want to batch raw SQL queries ($executeRaw) - for example, for features that Prisma Client does not yet support.

Scenario: Privacy legislation

GDPR and other privacy legislation give users the right to request that an organization deletes all of their personal data. In the following example schema, a User can have many posts and private messages:

model User {
  id              Int              @id @default(autoincrement())
  posts           Post[]
  privateMessages PrivateMessage[]
}

model Post {
  id      Int    @id @default(autoincrement())
  user    User   @relation(fields: [userId], references: [id])
  userId  Int
  title   String
  content String
}

model PrivateMessage {
  id      Int    @id @default(autoincrement())
  user    User   @relation(fields: [userId], references: [id])
  userId  Int
  message String
}

If a user invokes the right to be forgotten, we must delete three records: the user record, private messages, and posts. It is critical that all delete operations succeed together or not at all, which makes this a use case for a transaction. However, using a single bulk operation like deleteMany is not possible in this scenario because we need to delete across three models. Instead, we can use the $transaction([]) API to run three operations together - two deleteMany and one delete:

const id = 9; // User to be deleted

const deletePosts = prisma.post.deleteMany({
  where: {
    userId: id,
  },
});

const deleteMessages = prisma.privateMessage.deleteMany({
  where: {
    userId: id,
  },
});

const deleteUser = prisma.user.delete({
  where: {
    id: id,
  },
});

await prisma.$transaction([deletePosts, deleteMessages, deleteUser]); // Operations succeed or fail together

Scenario: Pre-computed IDs and the $transaction([]) API

Dependent writes are not supported by the $transaction([]) API - if operation A relies on the ID generated by operation B, use nested writes. However, if you pre-computed IDs (for example, by generating GUIDs), your writes become independent. Consider the sign-up flow from the nested writes example:

await prisma.team.create({
  data: {
    name: "Aurora Adventures",
    members: {
      create: {
        email: "alice@prisma.io",
      },
    },
  },
});

Instead of auto-generating IDs, change the id fields of Team and User to a String (if you do not provide a value, a UUID is generated automatically). This example uses UUIDs:

model Team {
  id      Int    @id @default(autoincrement()) 
  id      String @id @default(uuid()) 
  name    String
  members User[]
}

model User {
  id    Int    @id @default(autoincrement()) 
  id    String @id @default(uuid()) 
  email String @unique
  teams Team[]
}

Refactor the sign-up flow example to use the $transaction([]) API instead of nested writes:

import { v4 } from "uuid";

const teamID = v4();
const userID = v4();

await prisma.$transaction([
  prisma.user.create({
    data: {
      id: userID,
      email: "alice@prisma.io",
      team: {
        id: teamID,
      },
    },
  }),
  prisma.team.create({
    data: {
      id: teamID,
      name: "Aurora Adventures",
    },
  }),
]);

Technically you can still use nested writes with pre-computed APIs if you prefer that syntax:

import { v4 } from "uuid";

const teamID = v4();
const userID = v4();

await prisma.team.create({
  data: {
    id: teamID,
    name: "Aurora Adventures",
    members: {
      create: {
        id: userID,
        email: "alice@prisma.io",
        team: {
          id: teamID,
        },
      },
    },
  },
});

There's no compelling reason to switch to manually generated IDs and the $transaction([]) API if you are already using auto-generated IDs and nested writes.

Read, modify, write

In some cases you may need to perform custom logic as part of an atomic operation - also known as the read-modify-write pattern. The following is an example of the read-modify-write pattern:

• Read a value from the database
• Run some logic to manipulate that value (for example, contacting an external API)
• Write the value back to the database

All operations should succeed or fail together without making unwanted changes to the database, but you do not necessarily need to use an actual database transaction. This section of the guide describes two ways to work with Prisma Client and the read-modify-write pattern:

• Designing idempotent APIs
• Optimistic concurrency control

Idempotent APIs

Idempotency is the ability to run the same logic with the same parameters multiple times with the same result: the effect on the database is the same whether you run the logic once or one thousand times. For example:

• NOT IDEMPOTENT: Upsert (update-or-insert) a user in the database with email address "letoya@prisma.io". The User table does not enforce unique email addresses. The effect on the database is different if you run the logic once (one user created) or ten times (ten users created).
• IDEMPOTENT: Upsert (update-or-insert) a user in the database with the email address "letoya@prisma.io". The User table does enforce unique email addresses. The effect on the database is the same if you run the logic once (one user created) or ten times (existing user is updated with the same input).

Idempotency is something you can and should actively design into your application wherever possible.

When to design an idempotent API

• ✔ You need to be able to retry the same logic without creating unwanted side-effects in the databases

Scenario: Upgrading a Slack team

You are creating an upgrade flow for Slack that allows teams to unlock paid features. Teams can choose between different plans and pay per user, per month. You use Stripe as your payment gateway, and extend your Team model to store a stripeCustomerId. Subscriptions are managed in Stripe.

model Team {
  id               Int     @id @default(autoincrement())
  name             String
  User             User[]
  stripeCustomerId String?
}

The upgrade flow looks like this:

1. Count the number of users
2. Create a subscription in Stripe that includes the number of users
3. Associate the team with the Stripe customer ID to unlock paid features

const teamId = 9;
const planId = "plan_id";

// Count team members
const numTeammates = await prisma.user.count({
  where: {
    teams: {
      some: {
        id: teamId,
      },
    },
  },
});

// Create a customer in Stripe for plan-9454549
const customer = await stripe.customers.create({
  externalId: teamId,
  plan: planId,
  quantity: numTeammates,
});

// Update the team with the customer id to indicate that they are a customer
// and support querying this customer in Stripe from our application code.
await prisma.team.update({
  data: {
    customerId: customer.id,
  },
  where: {
    id: teamId,
  },
});

This example has a problem: you can only run the logic once. Consider the following scenario:

1. Stripe creates a new customer and subscription, and returns a customer ID
2. Updating the team fails - the team is not marked as a customer in the Slack database
3. The customer is charged by Stripe, but paid features are not unlocked in Slack because the team lacks a valid customerId
4. Running the same code again either:
   • Results in an error because the team (defined by externalId) already exists - Stripe never returns a customer ID
   • If externalId is not subject to a unique constraint, Stripe creates yet another subscription (not idempotent)

You cannot re-run this code in case of an error and you cannot change to another plan without being charged twice.

The following refactor (highlighted) introduces a mechanism that checks if a subscription already exists, and either creates the description or updates the existing subscription (which will remain unchanged if the input is identical):

// Calculate the number of users times the cost per user
const numTeammates = await prisma.user.count({
  where: {
    teams: {
      some: {
        id: teamId,
      },
    },
  },
});

// Find customer in Stripe
let customer = await stripe.customers.get({ externalId: teamID }); 

if (customer) {
  // If team already exists, update
  customer = await stripe.customers.update({
    externalId: teamId, 
    plan: "plan_id", 
    quantity: numTeammates, 
  });
} else {
  customer = await stripe.customers.create({
    // If team does not exist, create customer
    externalId: teamId,
    plan: "plan_id",
    quantity: numTeammates,
  });
}

// Update the team with the customer id to indicate that they are a customer
// and support querying this customer in Stripe from our application code.
await prisma.team.update({
  data: {
    customerId: customer.id,
  },
  where: {
    id: teamId,
  },
});

You can now retry the same logic multiple times with the same input without adverse effect. To further enhance this example, you can introduce a mechanism whereby the subscription is cancelled or temporarily deactivated if the update does not succeed after a set number of attempts.

Optimistic concurrency control

Optimistic concurrency control (OCC) is a model for handling concurrent operations on a single entity that does not rely on 🔒 locking. Instead, we optimistically assume that a record will remain unchanged in between reading and writing, and use a concurrency token (a timestamp or version field) to detect changes to a record.

If a ❌ conflict occurs (someone else has changed the record since you read it), you cancel the transaction. Depending on your scenario, you can then:

• Re-try the transaction (book another cinema seat)
• Throw an error (alert the user that they are about to overwrite changes made by someone else)

This section describes how to build your own optimistic concurrency control. See also: Plans for application-level optimistic concurrency control on GitHub

When to use optimistic concurrency control

• ✔ You anticipate a high number of concurrent requests (multiple people booking cinema seats)
• ✔ You anticipate that conflicts between those concurrent requests will be rare

Avoiding locks in an application with a high number of concurrent requests makes the application more resilient to load and more scalable overall. Although locking is not inherently bad, locking in a high concurrency environment can lead to unintended consequences - even if you are locking individual rows, and only for a short amount of time. For more information, see:

• Why ROWLOCK Hints Can Make Queries Slower and Blocking Worse in SQL Server

Scenario: Reserving a seat at the cinema

You are creating a booking system for a cinema. Each movie has a set number of seats. The following schema models movies and seats:

model Seat {
  id        Int   @id @default(autoincrement())
  userId    Int?
  claimedBy User? @relation(fields: [userId], references: [id])
  movieId   Int
  movie     Movie @relation(fields: [movieId], references: [id])
}

model Movie {
  id    Int    @id     @default(autoincrement())
  name  String @unique
  seats Seat[]
}

The following sample code finds the first available seat and assigns that seat to a user:

const movieName = "Hidden Figures";

// Find first available seat
const availableSeat = await prisma.seat.findFirst({
  where: {
    movie: {
      name: movieName,
    },
    claimedBy: null,
  },
});

// Throw an error if no seats are available
if (!availableSeat) {
  throw new Error(`Oh no! ${movieName} is all booked.`);
}

// Claim the seat
await prisma.seat.update({
  data: {
    claimedBy: userId,
  },
  where: {
    id: availableSeat.id,
  },
});

However, this code suffers from the "double-booking problem" - it is possible for two people to book the same seats:

1. Seat 3A returned to Sorcha (findFirst)
2. Seat 3A returned to Ellen (findFirst)
3. Seat 3A claimed by Sorcha (update)
4. Seat 3A claimed by Ellen (update - overwrites Sorcha's claim)

Even though Sorcha has successfully booked the seat, the system ultimately stores Ellen's claim. To solve this problem with optimistic concurrency control, add a version field to the seat:

model Seat {
  id        Int   @id @default(autoincrement())
  userId    Int?
  claimedBy User? @relation(fields: [userId], references: [id])
  movieId   Int
  movie     Movie @relation(fields: [movieId], references: [id])
  version   Int
}

Next, adjust the code to check the version field before updating:

const userEmail = "alice@prisma.io";
const movieName = "Hidden Figures";

// Find the first available seat
// availableSeat.version might be 0
const availableSeat = await client.seat.findFirst({
  where: {
    Movie: {
      name: movieName,
    },
    claimedBy: null,
  },
});

if (!availableSeat) {
  throw new Error(`Oh no! ${movieName} is all booked.`);
}

// Only mark the seat as claimed if the availableSeat.version
// matches the version we're updating. Additionally, increment the
// version when we perform this update so all other clients trying
// to book this same seat will have an outdated version.
const seats = await client.seat.updateMany({
  data: {
    claimedBy: userEmail, 
    version: {
      increment: 1, 
    }, 
  }, 
  where: {
    id: availableSeat.id, 
    version: availableSeat.version, // This version field is the key; only claim seat if in-memory version matches database version, indicating that the field has not been updated
  }, 
}); 

if (seats.count === 0) {
  throw new Error(`That seat is already booked! Please try again.`); 
} 

It is now impossible for two people to book the same seat:

1. Seat 3A returned to Sorcha (version is 0)
2. Seat 3A returned to Ellen (version is 0)
3. Seat 3A claimed by Sorcha (version is incremented to 1, booking succeeds)
4. Seat 3A claimed by Ellen (in-memory version (0) does not match database version (1) - booking does not succeed)

Interactive transactions

If you have an existing application, it can be a significant undertaking to refactor your application to use optimistic concurrency control. Interactive Transactions offers a useful escape hatch for cases like this.

To create an interactive transaction, pass an async function into $transaction.

The first argument passed into this async function is an instance of the Prisma Client. Below, we will call this instance tx. Any Prisma Client call invoked on this tx instance is encapsulated into the transaction.

In the example below, Alice and Bob each have $100 in their account. If they try to send more money than they have, the transfer is rejected.

The expected outcome would be for Alice to make 1 transfer for $100 and the other transfer would be rejected. This would result in Alice having $0 and Bob having $200.

import { PrismaClient } from "../prisma/generated/client";
const prisma = new PrismaClient();

async function transfer(from: string, to: string, amount: number) {
  return await prisma.$transaction(async (tx) => {
    // 1. Decrement amount from the sender.
    const sender = await tx.account.update({
      data: {
        balance: {
          decrement: amount,
        },
      },
      where: {
        email: from,
      },
    });

    // 2. Verify that the sender's balance didn't go below zero.
    if (sender.balance < 0) {
      throw new Error(`${from} doesn't have enough to send ${amount}`);
    }

    // 3. Increment the recipient's balance by amount
    const recipient = tx.account.update({
      data: {
        balance: {
          increment: amount,
        },
      },
      where: {
        email: to,
      },
    });

    return recipient;
  });
}

async function main() {
  // This transfer is successful
  await transfer("alice@prisma.io", "bob@prisma.io", 100);
  // This transfer fails because Alice doesn't have enough funds in her account
  await transfer("alice@prisma.io", "bob@prisma.io", 100);
}

main();

In the example above, both update queries run within a database transaction. When the application reaches the end of the function, the transaction is committed to the database.

If the application encounters an error along the way, the async function will throw an exception and automatically rollback the transaction.

You can learn more about interactive transactions in this section.

Conclusion

Prisma Client supports multiple ways of handling transactions, either directly through the API or by supporting your ability to introduce optimistic concurrency control and idempotency into your application. If you feel like you have use cases in your application that are not covered by any of the suggested options, please open a GitHub issue to start a discussion.