Intermediate LevelSQL & MySQL
How is Optimistic Locking implemented in SQL using a version or timestamp column?
❓ Interview Prompt: Explain Optimistic Concurrency Control, version/timestamp columns, detecting concurrent update collisions, and retry handling.
Optimistic Locking prevents concurrent update overwrites without holding database locks:
Implementation:
1
Include a 'version INT DEFAULT 1' column on target tables.
2
Query row: 'SELECT id, balance, version FROM accounts WHERE id = 1' (returns 'version = 5').
3
Execute conditional update: 'UPDATE accounts SET balance = 150, version = version + 1 WHERE id = 1 AND version = 5'.
4
If affected rows count is '0', a concurrent transaction modified the row! Abort or retry.
Code Implementation Example
sql
-- Optimistic Locking UPDATE query enforcing version matching
UPDATE accounts
SET balance = balance - 100.00,
version = version + 1
WHERE id = 101 AND version = 3; -- Fails gracefully if version != 3💡Executing conditional updates with Optimistic Locking.
🎯 Interview Key Takeaway
Use Optimistic Locking with a version column for high-throughput applications with low update collision rates.
#SQL#Optimistic Locking#Version Column#Concurrency#Intermediate
Back to all SQL & MySQL Questions→