Q34: What is the difference between Optimistic and Pessimistic Locking?
- Pessimistic Locking: Assumes lock conflicts are likely. It locks targeted database rows immediately upon selection using statements like
SELECT ... FOR UPDATE, blocking other concurrent operations until the lock releases. - Optimistic Locking: Assumes conflicts are rare. It avoids holding record locks during read windows. Instead, it checks a version column or timestamp during writes (
WHERE id = 1 AND version = 5) to confirm the data wasn't modified concurrently.
Key Takeaways
- Pessimistic locking prevents conflicting writes by holding locks immediately.
- Optimistic locking avoids database lock overhead using version numbers, offering better throughput for read-heavy systems.