What is the difference between optimistic and pessimistic locking?
Why Interviewers Ask This
This tests whether you can apply MySQL / SQL knowledge to real-world scenarios. Interviewers are looking for clarity of thought and evidence that you've encountered this in production code.
Answer
Locking prevents concurrent transactions from corrupting shared data. Pessimistic locking assumes conflicts will happen — it locks the data before reading/modifying, preventing other transactions from accessing it until the lock is released. In MySQL: SELECT * FROM orders WHERE id = 1 FOR UPDATE; — acquires an exclusive row lock. Other transactions block until this transaction COMMITs or ROLLBACKs. Safe but reduces concurrency. Best for: high-contention data (bank account balances, inventory counts where oversell is unacceptable). Optimistic locking assumes conflicts are rare — it doesn't lock data during reads. Instead, it detects conflicts at write time by checking if the data has changed since it was read. Typically implemented with a version column (integer or timestamp): read version=3, make changes, then UPDATE with WHERE id=1 AND version=3 — if another transaction already incremented version to 4, the UPDATE affects 0 rows, and the application retries. Better concurrency, more application complexity. Best for: low-contention data, web applications where users rarely edit the same records simultaneously. Most ORMs support optimistic locking natively.
Common Mistake
Many candidates answer correctly but can't explain the 'why'. Always be prepared to justify your answer with a concrete example or use case from your MySQL / SQL experience.