How do you handle database transactions in Prisma, and what are the different transaction modes available?

Prisma Transaction Modes

1. Sequential Transactions ($transaction array)

// ✅ Simple sequential operations — all succeed or all fail
const [application, notification, log] = await prisma.$transaction([
  prisma.jobApplication.update({
    where: { id: applicationId },
    data: { status: "OFFER" },
  }),
  prisma.notification.create({
    data: { userId, type: "OFFER_ACCEPTED", message: "..." },
  }),
  prisma.log.create({
    data: { userId, type: "info", message: "Offer accepted" },
  }),
]);
// If any operation fails, ALL are rolled back

2. Interactive Transactions (callback)

// ✅ Complex logic with conditionals inside transaction
const result = await prisma.$transaction(async (tx) => {
  // Step 1: Update application
  const application = await tx.jobApplication.update({
    where: { id: applicationId },
    data: { status: "OFFER" },
    include: { offer: true },
  });

  // Step 2: Business logic — validate offer exists
  if (!application.offer) {
    throw new Error("No offer details found"); // Rolls back everything
  }

  // Step 3: Update user stats
  await tx.user.update({
    where: { id: userId },
    data: { reputation: { increment: 10 } },
  });

  // Step 4: Create audit log
  await tx.log.create({
    data: {
      userId,
      type: "info",
      message: `Offer accepted: ${application.company}`,
    },
  });

  return application;
}, {
  maxWait: 5000,    // Max time to acquire a transaction
  timeout: 10000,   // Max time for the transaction to complete
  isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
});

3. Batch Transactions (createMany, updateMany)

// ✅ Efficient bulk operations in a transaction
await prisma.$transaction([
  prisma.question.createMany({
    data: questions.map(q => ({ ...q, userId })),
  }),
  prisma.user.update({
    where: { id: userId },
    data: { reputation: { increment: questions.length } },
  }),
]);

Transaction Isolation Levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read	Performance
Read Uncommitted	❌ Possible	Possible	Possible	Fastest
Read Committed	Prevented	Possible	Possible	Fast
Repeatable Read	Prevented	Prevented	Possible	Moderate
Serializable	Prevented	Prevented	Prevented	Slowest

MongoDB Transaction Requirements

// MongoDB requires Replica Set for transactions
// In development, start MongoDB as replica set:
// mongod --replSet rs0
// rs.initiate()

// Prisma handles this automatically with:
// mongodb://localhost:27017/interviewOS?replicaSet=rs0

Error Handling

try {
  await prisma.$transaction(async (tx) => { /* ... */ });
} catch (error) {
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    switch (error.code) {
      case "P2034": // Transaction failed due to write conflict
        // Retry the transaction
        break;
      case "P2024": // Timeout
        throw new Error("Operation timed out, please try again");
    }
  }
  throw error;
}

Rule: Keep transactions short. Long-running transactions block other operations and cause timeouts.