Using Prisma Interactive Transactions for Atomic Multi‑Step Operations

Desired Outcome

Execute multiple dependent database writes as a single atomic unit using Prisma's Interactive Transaction API.

Prerequisites

• Node.js project with @prisma/client installed.
• Prisma schema configured and migrated against a transaction‑capable database (PostgreSQL, MySQL, SQL Server, or MongoDB).
• A PrismaClient instance imported as prisma.

Procedure

1. Call prisma.$transaction with an async callback that receives a transaction client tx.
2. Inside the callback, perform the first query and store its result.
3. Use that result to determine parameters for subsequent queries.
4. Return any data you need from the callback; Prisma resolves it after the transaction commits.
5. Handle errors with a try/catch; any thrown error triggers an automatic rollback.

Example: Create a user and update a counter

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function createUserAndCount(email, name) {
  return await prisma.$transaction(async (tx) => {
    // 1. Create the user
    const user = await tx.user.create({
      data: { email, name, active: true },
    });

    // 2. Guard‑rail based on the first result
    if (!user.active) {
      throw new Error('Inactive user not allowed');
    }

    // 3. Update a statistics record using the newly created user
    const stats = await tx.stats.update({
      where: { id: 'global' },
      data: { totalUsers: { increment: 1 } },
    });

    // Return combined result for the caller
    return { user, stats };
  });
}

// Usage
createUserAndCount('alice@example.com', 'Alice')
  .then(res => console.log('Committed:', res))
  .catch(err => console.error('Transaction aborted:', err.message));

Configuring the timeout

The default transaction timeout is 20 seconds. To change it, pass an options object as the second argument:

await prisma.$transaction(async (tx) => { /* … */ }, { timeout: 60000 }); // 60 seconds

Verifying atomicity

1. Temporarily insert throw new Error('forced failure') after the first query but before the second.
2. Run the function.
3. Inspect the database: the user row created by the first query must be absent, confirming a rollback.

Limitations and recovery options

• Connection pool pressure: Long‑running logic inside the callback holds a database connection for the entire duration. Avoid heavy computation, loops over large datasets, or waiting for external services.
• Non‑database work: Do not perform HTTP calls, file I/O, or other side effects inside the transaction; they extend the time the connection is checked out and can exhaust the pool.
• Provider support: Verify that your database dialect supports transactions; Prisma will surface an error if the underlying engine does not.
• If a transaction fails because of a timeout, increase the timeout value or refactor the logic to reduce execution time.