Interactive Transactions in Prisma: A Developer's Guide

🔄 Understanding Interactive Transactions in Prisma

Interactive transactions in Prisma allow you to execute multiple queries in sequence so that either all succeed or none do. This ensures atomicity, a key part of the ACID principles meaning either the whole transaction happens or nothing happens at all.

✅ What Are Interactive Transactions?

In Prisma, you can use interactive transactions via prisma.$transaction(async (tx) => { ... }). The tx object is a special TransactionClient, passed to all queries inside the function, and it ensures that they run within the same transaction context.

💡 Basic Example

const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: { name: "John", email: "john@example.com" }
  });

  const profile = await tx.profile.create({
    data: { userId: user.id, bio: "Software developer" }
  });

  return { user, profile };
});

If the second operation fails (creating the profile), the user creation is also rolled back ensuring data consistency.

🚫 When Not to Use Interactive Transactions

Interactive transactions have performance costs and should be avoided when queries are unrelated or read-only.

❌ Bad Example: Unrelated Queries

await prisma.$transaction(async (tx) => {
  const users = await tx.user.findMany();
  const posts = await tx.post.findMany();
  const comments = await tx.comment.findMany();
});

These queries are independent so no need for a transaction.

✅ Good Alternative

const [users, posts, comments] = await Promise.all([
  prisma.user.findMany(),
  prisma.post.findMany(),
  prisma.comment.findMany()
]);

Other Situations to Avoid
• Read-only operations – No write means no need for rollback.
• Long-running operations – May lock DB resources unnecessarily.
• Built-in DB constraints – Let foreign keys and constraints handle integrity.

✅ When To Use Interactive Transactions

Use interactive transactions when operations are interdependent or must succeed/fail as a unit.

Creating Related Records

await prisma.$transaction(async (tx) => {
  const order = await tx.order.create({
    data: { userId: 1, total: 100 }
  });

  await tx.orderItem.createMany({
    data: [
      { orderId: order.id, productId: 1, quantity: 2 },
      { orderId: order.id, productId: 2, quantity: 1 }
    ]
  });

  await tx.product.update({
    where: { id: 1 },
    data: { stock: { decrement: 2 } }
  });
});

Transfers (e.g., Wallet or Bank)

await prisma.$transaction(async (tx) => {
  await tx.account.update({
    where: { id: senderId },
    data: { balance: { decrement: amount } }
  });

  await tx.account.update({
    where: { id: receiverId },
    data: { balance: { increment: amount } }
  });
});

Conditional Logic Based on Current State

await prisma.$transaction(async (tx) => {
  const product = await tx.product.findUnique({ where: { id: productId } });

  if (product.stock < requestedQuantity) {
    throw new Error("Insufficient stock");
  }

  await tx.product.update({
    where: { id: productId },
    data: { stock: { decrement: requestedQuantity } }
  });
});

🧠 Best Practices

Prepare Data Before the Transaction

Avoid delays inside the transaction block — especially I/O or API calls.

❌ Bad

await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: userData });
  const enrichedData = await fetchFromExternalAPI(user.id); // ❌ Blocks DB
  await tx.profile.create({ data: { userId: user.id, ...enrichedData } });
});

✅ Good

const enrichedData = await fetchFromExternalAPI();

await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: userData });
  await tx.profile.create({ data: { userId: user.id, ...enrichedData } });
});

⸻

Set Timeouts and Retries

You can configure timeouts and isolation level for better control:

await prisma.$transaction(
  async (tx) => {
    // transaction logic
  },
  {
    maxWait: 5000, // wait up to 5 seconds to start
    timeout: 10000, // abort if takes more than 10 seconds
    isolationLevel: Prisma.TransactionIsolationLevel.ReadCommitted
  }
);

Handle Errors Gracefully

try {
  await prisma.$transaction(async (tx) => {
    // operations
  });
} catch (error) {
  if (error.code === 'P2034') {
    console.log("Transaction conflict, retrying...");
    // Add retry logic here
  } else {
    console.error("Transaction failed:", error);
  }
}

Keep Transactions Focused

Only wrap business-critical operations. Leave logs, metrics, or unrelated actions outside.

❌ Bad

await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: userData });
  await tx.profile.create({ data: profileData });
  await tx.auditLog.create({ action: 'USER_CREATED', userId: user.id }); // ❌
});

✅ Good

const user = await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: userData });
  await tx.profile.create({ data: profileData });
  return user;
});

await prisma.auditLog.create({
  data: { action: 'USER_CREATED', userId: user.id }
});

2.ACID Properties

🧾 Conclusion

Interactive transactions in Prisma help you ensure data consistency, especially when multiple related operations must succeed or fail as a unit. Use them wisely:

•✅ Do: Use for dependent inserts, transfers, or state-based operations.
•❌ Don’t: Use for independent reads, logging, or long-running processes.

Keep them fast, focused, and fail-safe and your database will thank you.