What is the difference between optimistic and pessimistic locking?
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.