Pessimistic Locking

Hook: Interviewers love this topic because it reveals whether you know how to stop two transactions from grabbing the same data at the same time.

Question: What is pessimistic locking in SQL?

Answer: Pessimistic locking is a technique where the database locks a row before another session can change it. It is used when conflicts are likely and correctness matters more than maximum concurrency. In SQL, you usually see it with row locks such as SELECT ... FOR UPDATE.

Interview-Ready Answer: I use pessimistic locking when I expect two users or services to touch the same row and I want the database to serialize that access. The first transaction locks the row, and the others wait, fail fast, or skip it depending on the SQL option I choose. A common example is inventory or seat reservation, where I lock the row before decrementing stock so the last item cannot be sold twice.

What it is

Detailed Explanation: “Pessimistic” means the database assumes conflicts are likely. Instead of letting two sessions read the same data and hoping they do not clash, it reserves the row first. A row lock is a short-term claim on a specific row; other transactions cannot update it until the lock is released at commit or rollback.

How it works under the hood

1. A transaction begins.
2. You run a locking query such as SELECT ... FOR UPDATE on one or more rows.
3. The database asks its lock manager, which is the component that tracks who owns which locks, to mark those rows as locked.
4. Other transactions that try to update the same rows either wait, error immediately with NOWAIT, or skip locked rows with SKIP LOCKED if the database supports it.
5. You finish your change, then COMMIT or ROLLBACK.
6. The lock is released, and the next waiting transaction can continue.

Some engines also lock extra key ranges, not just the exact row, to prevent phantoms (new rows appearing in a range you already checked). For example, InnoDB may use next-key locking in range queries under repeatable-read behavior. That is why a query on a range can block more than one row.

When to use it

• Inventory reservation: one product row, many buyers.
• Money movement: two accounts must not be updated out of order.
• Seat or ticket booking: the last seat must be protected.
• Job queues: workers can combine locks with SKIP LOCKED to safely claim work.

Use it when conflicts are common and retries would be expensive or user-visible. If conflicts are rare, pessimistic locking can reduce throughput because sessions spend time waiting instead of doing useful work.

Pessimistic vs optimistic locking

Why the choice matters: pessimistic locking spends work up front to prevent conflicts. Optimistic locking lets work proceed and only checks for a conflict at write time, often using a version column. If ten users rarely touch the same row, optimistic locking usually scales better. If ten users often race for the same row, pessimistic locking avoids repeated failures and retries.

Performance and complexity

Acquiring a row lock is usually fast, roughly O(1) per locked row from the app’s point of view, but contention changes the picture. The database must track the lock in memory, and waiting transactions create a queue. In real systems, if 30 requests line up behind one row and each transaction holds the lock for 20 ms, the last request may wait about 600 ms before it can even start its update.

Timeout behavior is database-specific. PostgreSQL waits until the lock is released unless you set lock_timeout or use NOWAIT. MySQL/InnoDB commonly uses a finite lock-wait timeout such as 50 seconds by default. SQL Server can also be configured with a lock timeout; without it, sessions may wait a long time. That is why interviewers care about both correctness and user latency.

Important edge cases

• Deadlocks: if Transaction A locks row 1 then wants row 2, while Transaction B locks row 2 then wants row 1, the database may abort one transaction to break the cycle.
• Locking too much: a table lock blocks unrelated rows and hurts throughput.
• Long transactions: a slow API call, remote request, or user think time while holding the lock can stall other sessions.
• Plain SELECT is not enough: a normal read usually does not protect the row from later updates; you need the locking form.

Memory mental model: think of pessimistic locking as putting your hand on the last donut before asking who wants it. You are not being rude; you are preventing a fight over the same donut.

Real-World Example: Imagine a ticketing checkout service for a concert. Each checkout request must reserve the last available seat in a specific row. The service starts a transaction, locks the seat record, checks availability, writes the reservation, and commits. That keeps two customers from both seeing “1 seat left” and both buying it.

What goes wrong when this is misunderstood? A team uses a plain SELECT, then later runs UPDATE without locking first. Under load, two requests read the same stock value, both pass validation, and both decrement it. Users see duplicate confirmations, inventory goes negative, and support tickets spike. In logs you may see a mix of duplicate order IDs, “could not serialize access” or lock timeout messages, and a sudden rise in 500/409 responses during traffic peaks.

In another common failure, the team locks the row but keeps the transaction open while calling a payment gateway. That turns a 20 ms database action into a 2 second lock hold. The visible symptom is request pileup: dashboards show growing latency, DB sessions waiting on locks, and customers timing out even though the database itself is not “down.”

Follow-up & Tricky Questions:

• Follow-up: How is pessimistic locking different from optimistic locking? Pessimistic locking prevents the conflict by locking first; optimistic locking detects the conflict later, usually with a version column. Use pessimistic locking when conflicts are common and retries are costly.
• Follow-up: What does SELECT ... FOR UPDATE do? It locks the selected rows so other transactions cannot update them until commit or rollback. It is the classic SQL tool for pessimistic locking.
• Follow-up: What is NOWAIT? It tells the database to fail immediately instead of waiting for the lock. This is useful when you prefer a fast error over a slow queue.
• Follow-up: What is SKIP LOCKED? It tells the database to ignore rows that are already locked by another transaction. It is popular in worker queues where many processes claim different jobs.
• Follow-up: How do you avoid deadlocks? Lock rows in a consistent order, keep transactions short, and avoid calling slow external services while holding locks. The database can still detect deadlocks, but good order greatly reduces them.
• Tricky: Does a plain SELECT lock the row? Usually no. A normal read often does not block writers, so you need an explicit locking clause when correctness depends on reserving the row.
• Tricky: Is pessimistic locking the same as a table lock? No. A row lock protects only the chosen rows, while a table lock blocks much more work and can hurt concurrency badly.
• Tricky: Does pessimistic locking remove the need for transactions? No. The lock only makes sense inside a transaction, because commit or rollback is what releases it.

Common Mistakes:

• Mistake: Holding the lock while calling payment APIs or other slow services. Correction: keep the transaction short; lock, validate, update, commit.
• Mistake: Locking many rows when you only need one. Correction: lock the smallest possible set to reduce blocking.
• Mistake: Using pessimistic locking for mostly read-only data. Correction: prefer optimistic locking or no locking when conflicts are rare.
• Mistake: Forgetting that different databases have different lock syntax and wait behavior. Correction: know your engine’s row-locking clause and timeout defaults.

Memory Hook: “Pessimistic locking is putting your hand on the last donut.” You reserve the item first, then finish the transaction.

Cheat Sheet:

• Use it when conflicts are likely.
• SELECT ... FOR UPDATE is the classic row-locking pattern.
• Locks live inside a transaction and release on commit/rollback.
• NOWAIT fails fast; SKIP LOCKED skips busy rows.
• Great for inventory, seats, transfers, and queues.
• Bad fit for long transactions and hot-table contention.

Practice Tasks:

• Write a transaction that reserves one inventory row safely.
• Open two SQL sessions and observe how FOR UPDATE NOWAIT behaves.
• Change the example to use SKIP LOCKED and see how a queue worker could claim jobs safely.