Transactions and batch queries

A database transaction refers to a sequence of read/write operations that are guaranteed to either succeed or fail as a whole. This section describes the ways in which the Prisma Client API supports transactions.

About transactions in Prisma

Prisma provides the following options for using transactions:

  • Nested writes: use the Prisma Client API to process multiple operations on one or more related records inside the same transaction.
  • Batch / bulk transactions: process one or more operations in bulk with updateMany, deleteMany, and createMany.
  • The $transaction API in Prisma Client:
    • Sequential operations: pass an array of Prisma Client queries to be executed sequentially inside a transaction, using $transaction<R>(queries: PrismaPromise<R>[]): Promise<R[]>.
    • Interactive transactions: pass a function that can contain user code including Prisma Client queries, non-Prisma code and other control flow to be executed in a transaction, using $transaction<R>(fn: (prisma: PrismaClient) => R, options?: object): R

Nested writes

A nested write lets you perform a single Prisma Client API call with multiple operations that touch multiple related records. For example, creating a user together with a post or updating an order together with an invoice. Prisma Client ensures that all operations succeed or fail as a whole.

The following example demonstrates a nested write with create:

// Create a new user with two posts in a
// single transaction
const newUser: User = await prisma.user.create({
data: {
email: 'alice@prisma.io',
posts: {
create: [
{ title: 'Join the Prisma Slack on https://slack.prisma.io' },
{ title: 'Follow @prisma on Twitter' },
],
},
},
})

The following example demonstrates a nested write with update:

// Change the author of a post in a single transaction
const updatedPost: Post = await prisma.post.update({
where: { id: 42 },
data: {
author: {
connect: { email: 'alice@prisma.io' },
},
},
})

Refer to the 📖 transactions guide for more examples.

Batch/bulk operations

The following bulk operations run as transactions:

  • deleteMany
  • updateMany
  • createMany

Refer to the 📖 transactions guide for more examples.

The $transaction API

The $transaction API can be used in two ways:

  • Sequential operations: Pass an array of Prisma Client queries to be executed sequentially inside of a transaction.

    $transaction<R>(queries: PrismaPromise<R>[]): Promise<R[]>

  • Interactive transactions: Pass a function that can contain user code including Prisma Client queries, non-Prisma code and other control flow to be executed in a transaction.

    $transaction<R>(fn: (prisma: PrismaClient) => R): R

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:

SQL Databases
MongoDB
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:

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.

Interactive transactions are available in the following versions of Prisma:
  • Interactive transactions are generally available from version 4.7.0.
  • In version 4.6, Data Proxy support for interactive transactions was available in preview.
  • From version 2.29.0 to version 4.5, interactive transactions were available in preview, but did not support the Data Proxy.
If you use interactive transactions in preview from version 2.29.0 to 4.6.1 (included), you need to add the interactiveTransactions preview feature to the generator block of your Prisma schema.

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

Use interactive transactions with caution. Keeping transactions open for a long time hurts database performance and can even cause deadlocks. Try to avoid performing network requests and executing slow queries inside your transaction functions. We recommend you get in and out as quick as possible!

Transaction isolation level

This feature is not available on MongoDB, because MongoDB does not support isolation levels.

You can set the transaction isolation level for transactions.

This is available in the following Prisma versions for interactive transactions from version 4.2.0, for sequential operations from version 4.4.0.
In versions before 4.2.0 (for interactive transactions), or 4.4.0 (for sequential operations), you cannot configure the transaction isolation level at a Prisma level. Prisma does not explicitly set the isolation level, so the isolation level configured in your database is used.

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:

DatabaseReadUncommittedReadCommittedRepeatableReadSnapshotSerializable
PostgreSQL✔️✔️✔️No✔️
MySQL✔️✔️✔️No✔️
SQL Server✔️✔️✔️✔️✔️
CockroachDBNoNoNoNo✔️
SQLiteNoNoNoNo✔️

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:

DatabaseDefault
PostgreSQLReadCommitted
MySQLRepeatableRead
SQL ServerReadCommitted
CockroachDBSerializable
SQLiteSerializable

Database-specific information on isolation levels

See the following resources:

CockroachDB and SQLite only support the Serializable isolation level.

Transaction timing issues

  • The solution in this section does not apply to MongoDB, because MongoDB does not support isolation levels.
  • The timing issues discussed in this section do not apply to CockroachDB and SQLite, because these databases only support the highest Serializable isolation level.

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

Before Prisma version 4.4.0, you could not set isolation levels on transactions. The isolation level in your database configuration always applied.

Edit this page on GitHub