Compare optimistic and pessimistic locking.
Assesses fundamental understanding of DBMS conventions, runtime behavior, and memory/performance considerations.
Hiring managers look for precision, avoidance of ambiguous jargon, and ability to explain trade-offs under real production conditions.
Both approaches handle concurrent updates to the same data.
Pessimistic locking assumes conflicts are likely, so it locks a row when read: SELECT ... FOR UPDATE or a shared lock. Other transactions block until the lock is released at commit or rollback. This prevents lost updates directly but reduces concurrency and risks deadlocks and long waits. It suits high-contention, short transactions.
Optimistic locking assumes conflicts are rare. You read a row with its version or timestamp, do your work, then update with a condition that the version is unchanged.
UPDATE items SET qty = 5, version = version + 1
WHERE id = 7 AND version = 3;
-- 0 rows affected means someone else changed it
If zero rows are affected, you detect the conflict and retry or report it. This avoids holding locks during user think time and scales well, but every write must check the result and handle retries. Many ORMs expose both, and hybrid strategies are common.
Candidate Response Strategy & Interview Tips
- Start with a concise one-sentence summary: Deliver a direct, confident answer first before expanding into nuances.
- Demonstrate real-world trade-offs: Discuss where this approach excels and when you would avoid it in production systems.
- Discuss complexity & edge cases: Proactively explain time/space complexity or boundary conditions (null values, scale limits).
- Prepare for interviewer follow-ups: Technical hiring panels frequently probe deeper into concurrency, backward compatibility, or alternative libraries.