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

• High contention resources (last ticket, auction bid)
• Short transactions
• Cannot tolerate any oversell
• Banking, inventory, seat booking

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

• Low contention (most of the time no conflicts)
• Long transactions (don't want to hold locks)
• Distributed systems (no shared lock manager)
• Profile updates, document edits

Comparison

Aspect	Pessimistic	Optimistic
Strategy	Lock first	Check on commit
Conflicts	Prevented	Detected and retried
Performance	Lock overhead	Retry overhead
Deadlocks	Possible	Impossible
Best when	High contention	Low contention
DB support	SELECT FOR UPDATE	Version column

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;
    }
  }
}