A transaction is a unit of work that only succeeds if every operation within that transaction succeeds. For example, if you update 200 users within a transaction, each update must succeed - if not, all changes are rolled back and the transaction fails as a whole.

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

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.

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'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 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])
Are cascading deletes supported by the Prisma Client nested writes?

Although you cannot specifically use nested writes to perform a cascading delete (for example, delete a team and all of its members), you can still perform cascading deletes in the following ways:

Note: We are a looking into cascading deletes support at a schema level.

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([deleteUsers, 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 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 rolls back the entire transaction. Prisma currently supports:

  • updateMany
  • deleteMany

The ability to create and upsert in bulk is being considered: feel free to chime in with your use case and upvote if you're interested.

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

You an use the $transaction API to perform a cascading delete.

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 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 the 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 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 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: userId,
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)

Conclusion

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

Edit this page on GitHub