Transactions and batch queries

Sequential Prisma Client operations

The following query returns all posts that match the provided filter as well as a count of all posts:

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

You can also use raw queries inside of a $transaction:

const [userList, updateUser] = await prisma.$transaction([
  prisma.$queryRaw`SELECT 'title' FROM User`,
  prisma.$executeRaw`UPDATE User SET name = 'Hello' WHERE id = 2;`,
])

Instead of immediately awaiting the result of each operation when it's performed, the operation itself is stored in a variable first which later is submitted to the database with a method called $transaction. Prisma Client will ensure that either all three create operations succeed or none of them succeed.

Note: Operations are executed according to the order they are placed in the transaction. Using a query in a transaction does not influence the order of operations in the query itself.

Refer to the 📖 transactions guide for more examples.

From version 4.4.0, the sequential operations transaction API has a second parameter. You can use the following optional configuration option in this parameter:

• isolationLevel: Sets the transaction isolation level. By default this is set to the value currently configured in your database.

For example:

await prisma.$transaction(
  [
    prisma.resource.deleteMany({ where: { name: 'name' } }),
    prisma.resource.createMany({ data }),
  ],
  {
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable, // optional, default defined by database configuration
  }
)

Interactive transactions

Sometimes you need more control over what queries execute within a transaction. Interactive transactions are meant to provide you with an escape hatch.

To use interactive transactions, you can pass an async function into $transaction.

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

Let's look at an example:

Imagine that you are building an online banking system. One of the actions to perform is to send money from one person to another.

As experienced developers, we want to make sure that during the transfer,

• the amount doesn't disappear
• the amount isn't doubled

This is a great use-case for interactive transactions because we need to perform logic in-between the writes to check the balance.

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.

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

import { PrismaClient } from '@prisma/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 = await 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 your application encounters an error along the way, the async function will throw an exception and automatically rollback the transaction.

To catch the exception, you can wrap $transaction in a try-catch block:

try {
  await prisma.$transaction(async (tx) => {
    // Code running in a transaction...
  })
} catch (err) {
  // Handle the rollback...
}

The transaction API has a second parameter. For interactive transactions, you can use the following optional configuration options in this parameter:

• maxWait: The maximum amount of time Prisma Client will wait to acquire a transaction from the database. The default value is 2 seconds.
• timeout: The maximum amount of time the interactive transaction can run before being canceled and rolled back. The default value is 5 seconds.
• isolationLevel: Sets the transaction isolation level. By default this is set to the value currently configured in your database.

For example:

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

Transaction isolation level

You can set the transaction isolation level for transactions.

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

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

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 ReadCommited, 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/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
       }
     }
   }