Intermediate LevelSoftware Engineering Principles
Compare Optimistic Concurrency Control vs Pessimistic Locking in high-throughput systems.
❓ Interview Prompt: Compare Optimistic Locking (version columns, high-read/low-conflict) vs Pessimistic Locking (`SELECT FOR UPDATE`, high-conflict database row locks).
Differentiating concurrency control strategies for database updates:
•
Optimistic Locking (Version Column): Assumes record conflicts are rare. Includes a 'version' integer column. When updating: 'UPDATE table SET val=x, version=version+1 WHERE id=1 AND version=1'. If 0 rows updated $\rightarrow$ Concurrent modification detected! Retry transaction.
•
Pessimistic Locking ('SELECT FOR UPDATE'): Assumes conflicts are frequent. Locks the database row at the beginning of the transaction, blocking all concurrent transactions until committed.
Code Implementation Example
sql
-- Optimistic Locking Query (Fails gracefully if version changed concurrently)
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 402 AND version = 5;💡Executing optimistic concurrency update with version column.
🎯 Interview Key Takeaway
Use Optimistic Locking for high-throughput, low-conflict reads; use Pessimistic Locking for critical, high-conflict financial transactions.
#Concurrency#Optimistic Locking#Pessimistic Locking#Intermediate
Back to all Software Engineering Principles Questions→