Prisma Transactions: Batching and Interactive Transactions

A database transaction ensures that multiple query operations execute as a single, atomic unit of work. If any query inside the transaction fails, the entire transaction rolls back, preventing partial data updates.

1. Sequential transactions (Batching array)

The simplest transaction pattern in Prisma is passing an array of queries to the $transaction helper. Prisma runs these queries sequentially in a single transaction block:

const deleteSpamPosts = db.post.deleteMany({ where: { spam: true } });
const incrementUserPoints = db.user.updateMany({
  where: { active: true },
  data: { points: { increment: 1 } },
});

// Execute both queries atomically
const [deleteResult, updateResult] = await db.$transaction([
  deleteSpamPosts,
  incrementUserPoints,
]);

If the user points update fails, the spam posts deletion rolls back automatically.

2. Interactive Transactions

Sometimes, the input parameters for a query depend on the result of a previous query in the transaction. In this case, use an interactive transaction.

To write an interactive transaction, pass an async callback function to $transaction:

const transferResult = await db.$transaction(async (tx) => {
  // 1. Check sender balance using tx client
  const sender = await tx.user.findUnique({ where: { id: 1 } });
  
  if (!sender || sender.balance < 100) {
    throw new Error("Insufficient funds transfer aborted");
  }

  // 2. Decrement sender balance
  const updatedSender = await tx.user.update({
    where: { id: 1 },
    data: { balance: { decrement: 100 } },
  });

  // 3. Increment receiver balance
  const updatedReceiver = await tx.user.update({
    where: { id: 2 },
    data: { balance: { increment: 100 } },
  });

  return { updatedSender, updatedReceiver };
});

Important Interactive Transaction Rules:

• Use the Transaction Client: Always use the callback client parameter tx to execute queries inside the transaction block. Using the default client instance db routes queries outside the transaction scope.
• Handle Timeouts: Keep code inside interactive transactions fast. Long-running asynchronous operations can cause database connection timeouts.