Detailed explanation
Optimistic and pessimistic locking are concurrency-control strategies for protecting data when multiple transactions try to change it. Consider two users booking seat 9: both might read that it is available and then attempt to reserve it. Without suitable protection, the application can accept conflicting decisions or lose an update. Pessimistic locking prevents competing changes while a transaction makes its decision; optimistic locking lets transactions proceed independently but rejects a write if the data has changed since it was read.
With pessimistic locking, a transaction executes SELECT * FROM seats WHERE id = 9 FOR UPDATE before checking availability and updating the seat. The database acquires a lock that blocks conflicting updates or locking reads until the transaction commits or rolls back. Ordinary reads may still proceed, depending on the database and isolation level. The SELECT, availability check, and UPDATE must run within the same explicit transaction; otherwise, autocommit may release the lock before the application finishes its work. This strategy suits short transactions with frequent contention, but waiting can reduce throughput, and inconsistent lock ordering can cause deadlocks.
With optimistic locking, the application reads the row and its version, then attempts UPDATE seats SET taken = 1, version = version + 1 WHERE id = 9 AND version = 3 AND taken = 0. The database evaluates the condition and update atomically. One affected row means success; zero means the expected state was not found, so the application must reload, reject the booking, or retry after checking availability again. The availability predicate enforces the booking rule, while the version predicate detects intervening changes. Every relevant writer must maintain the version consistently. Optimistic locking does not mean the database takes no locks: the UPDATE still uses normal database concurrency mechanisms.
Interviewers expect you to connect the choice to contention, transaction duration, and failure handling rather than call either strategy universally better. Optimistic locking often suits low-contention workloads and user-editing flows because it avoids holding locks during user interaction. Pessimistic locking can simplify highly contested operations, provided transactions stay short. Both need appropriate constraints and transaction boundaries; protecting one row does not automatically preserve invariants spanning multiple rows.
Key points to remember
- Pessimistic locking acquires a lock before making a change, typically with SELECT ... FOR UPDATE.
- Keep the locking read, validation, and update inside one transaction.
- Optimistic locking uses an atomic conditional update against the version originally read.
- Check affected-row counts and handle rejected writes explicitly.
- Choose based on contention and transaction length; plan for conflicts, deadlocks, and timeouts.
Common mistakes candidates make
- Saying optimistic locking takes no database locks or pessimistic locking blocks every ordinary read.
- Using SELECT ... FOR UPDATE in autocommit mode and assuming its lock protects a later UPDATE.
- Ignoring a zero-row update or blindly retrying without revalidating the business rule.
Likely follow-up questions
Why use a version column instead of just checking whether the seat is available?
For a simple booking, an atomic UPDATE with WHERE taken = 0 may be sufficient. A version column additionally detects intervening updates, including changes that leave the seat available again.
How should an application handle a deadlock?
Databases generally detect deadlocks and abort a participating transaction to release its locks. The application should roll back as required and retry the whole transaction with a bounded policy; consistent lock ordering helps prevent recurrence.
Is optimistic locking the same as MVCC?
No: MVCC is a database mechanism that maintains row versions to support concurrent access and snapshot visibility. Application-level optimistic locking uses an explicit conflict check, such as a version predicate, and may operate on top of an MVCC database.
Interview tip: Use the seat-booking example to contrast blocking before a change with detecting a conflict during the write, then explain failure handling.