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 via 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.

Interactive Transactions (in Preview)

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

You can enable interactive transactions by adding interactiveTransactions in the generator of your Prisma Schema:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["interactiveTransactions"]
}

Then you can pass an async function into $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 (prisma) => {
    // 1. Decrement amount from the sender.
    const sender = await prisma.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 = prisma.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()
  .catch(console.error)
  .finally(() => {
    prisma.$disconnect()
  })

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 (prisma) => {
    // Code running in a transaction...
  })
} catch (err) {
  // Handle the rollback...
}

The interactive transaction API has a 2nd parameter with the following configuration options:

• maxWait: The maximum amount of time the 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.

For example:

await prisma.$transaction(
  async (prisma) => {
    // Code running in a transaction...
  },
  {
    maxWait: 5000, // default: 2000
    timeout: 10000, // default: 5000
  }
)

Transaction Isolation Level

Transaction isolation level is not currently configurable at a Prisma level and is not explicitly set by Prisma.

• Default transaction isolation level in PostgreSQL
• Default transaction isolation level in Microsoft SQL Server
• Default transaction isolation level in MySQL