Skip to main content

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.

For more in-depth examples and use cases, refer to the 📖 transactions guide.

Transactions overview​

info

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

Developers take advantage of the safety guarantees provided by the database by wrapping the operations in a transaction. These guarantees are often summarized using the ACID acronym:

  • Atomic: Ensures that either all or none operations of the transactions succeed. The transaction is either committed successfully or aborted and rolled back.
  • Consistent: Ensures that the states of the database before and after the transaction are valid (i.e. any existing invariants about the data are maintained).
  • Isolated: Ensures that concurrently running transactions have the same effect as if they were running in serial.
  • Durability: Ensures that after the transaction succeeded, any writes are being stored persistently.

While there's a lot of ambiguity and nuance to each of these properties (for example, consistency could actually be considered an application-level responsibility rather than a database property or isolation is typically guaranteed in terms of stronger and weaker isolation levels), overall they serve as a good high-level guideline for expectations developers have when thinking about database transactions.

"Transactions are an abstraction layer that allows an application to pretend that certain concurrency problems and certain kinds of hardware and software faults don’t exist. A large class of errors is reduced down to a simple transaction abort, and the application just needs to try again." Designing Data-Intensive Applications, Martin Kleppmann

Prisma Client supports six different ways of handling transactions for three different scenarios:

ScenarioAvailable techniques
Dependent writes
  • Nested writes
Independent writes
  • $transaction([]) API
  • Batch operations
Read, modify, write
  • Idempotent operations
  • Optimistic concurrency control
  • Interactive transactions

The technique you choose depends on your particular use case.

Note: For the purposes of this guide, writing to a database encompasses creating, updating, and deleting data.

About transactions in Prisma Client​

Prisma Client 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:

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​

Overview​

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

info

Interactive transactions have been generally available from version 4.7.0.

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 Client call invoked on this tx instance is encapsulated into the transaction.

warning

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!

Example​

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

function transfer(from: string, to: string, amount: number) {
  return 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...
}

Transaction options​

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

You can also set these globally on the constructor-level:

const prisma = new PrismaClient({
  transactionOptions: {
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
    maxWait: 5000, // default: 2000
    timeout: 10000, // default: 5000
  },
})

Transaction isolation level​

info

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

You can set the transaction isolation level for transactions.

info

This is available in the following Prisma ORM 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 ORM level. Prisma ORM 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​

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

Dependent writes​

Writes are considered dependent on each other if:

  • Operations depend on the result of a preceding operation (for example, the database generating an ID)

The most common scenario is creating a record and using the generated ID to create or update a related record. Examples include:

  • Creating a user and two related blog posts (a one-to-many relationship) - the author ID must be known before creating blog posts
  • Creating a team and assigning members (a many-to-many relationship) - the team ID must be known before assigning members

Dependent writes must succeed together in order to maintain data consistency and prevent unexpected behavior, such as blog post without an author or a team without members.

Nested writes​

Prisma Client's solution to dependent writes is the nested writes feature, which is supported by create and update. The following nested write creates one user and two blog posts:

const nestedWrite = await prisma.user.create({
  data: {
    email: 'imani@prisma.io',
    posts: {
      create: [
        { title: 'My first day at Prisma' },
        { title: 'How to configure a unique constraint in PostgreSQL' },
      ],
    },
  },
})

If any operation fails, Prisma Client rolls back the entire transaction. Nested writes are not currently supported by top-level bulk operations like client.user.deleteMany and client.user.updateMany.

When to use nested writes​

Consider using nested writes if:

  • ✔ You want to create two or more records related by ID at the same time (for example, create a blog post and a user)
  • ✔ You want to update and create records related by ID at the same time (for example, change a user's name and create a new blog post)
tip

If you pre-compute your IDs, you can choose between a nested write or using the $transaction([]) API.

Scenario: Sign-up flow​

Consider the Slack sign-up flow, which:

  1. Creates a team
  2. Adds one user to that team, which automatically becomes that team's administrator

This scenario can be represented by the following schema - note that users can belong to many teams, and teams can have many users (a many-to-many relationship):

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

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

The most straightforward approach is to create a team, then create and attach a user to that team:

// Create a team
const team = await prisma.team.create({
  data: {
    name: 'Aurora Adventures',
  },
})

// Create a user and assign them to the team
const user = await prisma.user.create({
  data: {
    email: 'alice@prisma.io',
    team: {
      connect: {
        id: team.id,
      },
    },
  },
})

However, this code has a problem - consider the following scenario:

  1. Creating the team succeeds - "Aurora Adventures" is now taken
  2. Creating and connecting the user fails - the team "Aurora Adventures" exists, but has no users
  3. Going through the sign-up flow again and attempting to recreate "Aurora Adventures" fails - the team already exists

Creating a team and adding a user should be one atomic operation that succeeds or fails as a whole.

To implement atomic writes in a low-level database clients, you must wrap your inserts in BEGIN, COMMIT and ROLLBACK statements. Prisma Client solves the problem with nested writes. The following query creates a team, creates a user, and connects the records in a single transaction:

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

Furthermore, if an error occurs at any point, Prisma Client rolls back the entire transaction.

Nested writes FAQs​

Why can't I use the $transaction([]) API to solve the same problem?​

The $transaction([]) API does not allow you to pass IDs between distinct operations. In the following example, createUserOperation.id is not available yet:

const createUserOperation = prisma.user.create({
  data: {
    email: 'ebony@prisma.io',
  },
})

const createTeamOperation = prisma.team.create({
  data: {
    name: 'Aurora Adventures',
    members: {
      connect: {
        id: createUserOperation.id, // Not possible, ID not yet available
      },
    },
  },
})

await prisma.$transaction([createUserOperation, createTeamOperation])
Nested writes support nested updates, but updates are not dependent writes - should I use the $transaction([]) API?​

It is correct to say that because you know the ID of the team, you can update the team and its team members independently within a $transaction([]). The following example performs both operations in a $transaction([]):

const updateTeam = prisma.team.update({
  where: {
    id: 1,
  },
  data: {
    name: 'Aurora Adventures Ltd',
  },
})

const updateUsers = prisma.user.updateMany({
  where: {
    teams: {
      some: {
        id: 1,
      },
    },
    name: {
      equals: null,
    },
  },
  data: {
    name: 'Unknown User',
  },
})

await prisma.$transaction([updateUsers, updateTeam])

However, you can achieve the same result with a nested write:

const updateTeam = await prisma.team.update({
  where: {
    id: 1,
  },
  data: {
    name: 'Aurora Adventures Ltd', // Update team name
    members: {
      updateMany: {
        // Update team members that do not have a name
        data: {
          name: 'Unknown User',
        },
        where: {
          name: {
            equals: null,
          },
        },
      },
    },
  },
})
Can I perform multiple nested writes - for example, create two new teams and assign users?​

Yes, but this is a combination of scenarios and techniques:

  • Creating a team and assigning users is a dependent write - use nested writes
  • Creating all teams and users at the same time is an independent write because team/user combination #1 and team/user combination #2 are unrelated writes - use the $transaction([]) API
// Nested write
const createOne = prisma.team.create({
  data: {
    name: 'Aurora Adventures',
    members: {
      create: {
        email: 'alice@prisma.io',
      },
    },
  },
})

// Nested write
const createTwo = prisma.team.create({
  data: {
    name: 'Cool Crew',
    members: {
      create: {
        email: 'elsa@prisma.io',
      },
    },
  },
})

// $transaction([]) API
await prisma.$transaction([createTwo, createOne])

Independent writes​

Writes are considered independent if they do not rely on the result of a previous operation. The following groups of independent writes can occur in any order:

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

Note: Independent writes may have to occur in a specific order if constraints are present - for example, you must delete blog posts before the blog author if the post have a mandatory authorId field. However, they are still considered independent writes because no operations depend on the result of a previous operation, such as the database returning a generated ID.

Depending on your requirements, Prisma Client has four options for handling independent writes that should succeed or fail together.

Bulk operations​

Bulk writes allow you to write multiple records of the same type in a single transaction - if any operation fails, Prisma Client rolls back the entire transaction. Prisma Client currently supports:

  • updateMany
  • deleteMany
  • createMany

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

info

  • If you use version 4.4.0 or earlier, you cannot use optimistic concurrency control on update operations, because you cannot filter on non-unique fields. The version field you need to use with optimistic concurrency control is a non-unique field.

  • Since version 5.0.0 you are able to filter on non-unique fields in update operations so that optimistic concurrency control is being used. The feature was also available via the Preview flag extendedWhereUnique from versions 4.5.0 to 4.16.2.

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 a 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:

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 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/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 our Transactions and Batch Queries documentation.

warning

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!

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.