What are optimistic and pessimistic locking in databases, and when would you use each in a real application?

The Problem: Lost Update

Customer A: Read ticket count = 1
Customer B: Read ticket count = 1
Customer A: count - 1 = 0 → SAVE ✅
Customer B: count - 1 = 0 → SAVE ✅ ← Should fail!
Result: 2 tickets sold, but only 1 exists

Pessimistic Locking (Lock first, then operate)

// "I assume conflicts WILL happen, so I lock preemptively"

async function buyTicket(ticketId: string, userId: string) {
  await prisma.$transaction(async (tx) => {
    // SELECT ... FOR UPDATE — locks the row
    const ticket = await tx.$queryRaw
      SELECT * FROM Ticket WHERE id = ${ticketId} FOR UPDATE
    ;

    if (ticket[0].status !== "AVAILABLE") {
      throw new Error("Ticket already sold");
    }

    await tx.ticket.update({
      where: { id: ticketId },
      data: { status: "SOLD", buyerId: userId },
    });
  });
}
// Customer B waits until Customer A's transaction completes

When to Use Pessimistic Locking

Optimistic Locking (Check version, fail on conflict)

// "I assume conflicts are RARE, so I check at commit time"

// Schema: add a version field
// model Ticket {
//   id      String @id
//   status  String
//   version Int    @default(0)
// }

async function buyTicket(ticketId: string, userId: string) {
  const result = await prisma.ticket.updateMany({
    where: {
      id: ticketId,
      status: "AVAILABLE",
      version: currentVersion, // Only update if version matches
    },
    data: {
      status: "SOLD",
      buyerId: userId,
      version: { increment: 1 },
    },
  });

  if (result.count === 0) {
    throw new Error("Ticket was already sold (conflict detected)");
  }
}

When to Use Optimistic Locking

Comparison

MongoDB Approach: Atomic Operations

// MongoDB has atomic findAndModify — single atomic operation
const result = await db.collection("tickets").findOneAndUpdate(
  { _id: ticketId, status: "AVAILABLE" }, // Atomic check + update
  { $set: { status: "SOLD", buyerId: userId } },
  { returnDocument: "after" }
);

if (!result) throw new Error("Ticket already sold");
// No lock needed — single atomic operation

Retry Pattern for Optimistic Locking

async function withRetry<T>(fn: () => Promise<T>, maxRetries = 3): Promise<T> {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await fn();
    } catch (error) {
      if (error.code === "CONFLICT" && attempt < maxRetries) {
        await new Promise(r => setTimeout(r, 100 * attempt)); // Exponential backoff
        continue;
      }
      throw error;
    }
  }
}