How Does Optimistic Concurrency Control Work With Version Columns?
Learn how version columns implement optimistic concurrency control, detect lost updates, and avoid locking rows during edits.
Expected Interview Answer
Optimistic concurrency control uses a version (or updated_at) column on each row so an UPDATE only succeeds if the version read by the client still matches the version currently stored, catching conflicting concurrent edits without ever taking a database lock.
A client reads a row along with its current version number, edits the data locally, then issues an UPDATE that both writes the new values and increments the version, guarded by a WHERE clause requiring the version to still equal what was originally read. If another transaction updated the row in between, the version has already moved on, the WHERE clause matches zero rows, and the client's update fails and must retry after re-reading the fresh data. This trades pessimistic locking's blocking behavior for a cheap check that only rejects work in the rare case of an actual conflict, which is ideal when conflicts are infrequent and holding locks for the duration of user think-time would hurt throughput.
- Avoids holding database locks across slow user interactions
- Detects lost-update conflicts reliably with a simple WHERE clause
- Scales well when conflicting edits are rare
- Keeps conflict resolution logic in the application, not in lock management
AI Mentor Explanation
A scoring app lets two statisticians edit the same match record, and each edit form silently carries the version number of the scoreboard at the moment it was loaded, so if statistician A saves an update, the scoreboard's version ticks up, and then statistician B tries to save based on the old version, the save is rejected because the version no longer matches. Neither statistician was ever blocked from opening the form; the conflict is only caught at save time. This mirrors optimistic concurrency in a database: the WHERE clause checks the version at commit time rather than locking the row the entire time someone is editing.
Step-by-Step Explanation
Step 1
Add a version column
Every row carries an integer or timestamp version column that increments on each successful update.
Step 2
Read row and version together
The client loads the current data plus the version number, without taking any lock.
Step 3
Attempt a conditional update
The UPDATE sets new values and increments the version, guarded by WHERE version = <the version originally read>.
Step 4
Detect and handle conflicts
If zero rows are affected, another writer already moved the version forward; the client re-reads the latest row and retries.
What Interviewer Expects
- Clear explanation of the conditional WHERE version = ? update pattern
- Understanding that no lock is held between read and write
- Awareness of when optimistic concurrency is preferable to pessimistic locking
- A plan for what the client does when a conflict is detected (retry, merge, or surface to user)
Common Mistakes
- Confusing optimistic concurrency with row-level locking (SELECT FOR UPDATE)
- Forgetting to increment the version column on every write
- Not checking the number of affected rows to detect a conflict
- Using optimistic concurrency in high-contention scenarios where retries thrash constantly
Best Answer (HR Friendly)
“Optimistic concurrency control adds a version number to each row, and every update includes a check that the version has not changed since the client last read the row. If another update happened in between, the version has moved on, so the write is rejected and the client re-reads and retries instead of silently overwriting someone else's change. It avoids locking rows while users think or edit, which keeps the system responsive when conflicts are rare.”
Code Example
CREATE TABLE documents (
document_id BIGINT PRIMARY KEY,
content TEXT NOT NULL,
version INT NOT NULL DEFAULT 1
);
-- Client read version = 5 for document_id = 100, then edits it.
UPDATE documents
SET content = 'updated content',
version = version + 1
WHERE document_id = 100
AND version = 5;
-- If 0 rows are affected, someone else already updated this row
-- (its version is no longer 5) — re-read and retry the edit.Follow-up Questions
- When would you prefer pessimistic locking (SELECT FOR UPDATE) over optimistic concurrency?
- How would you surface a version conflict to an end user in a UI?
- How does optimistic concurrency interact with database transaction isolation levels?
- Would you use a timestamp or an incrementing integer for the version column, and why?
MCQ Practice
1. What does an UPDATE with WHERE version = ? return when a concurrent update already happened?
The WHERE clause simply matches no rows because the version has already moved, so the client detects the conflict by checking affected row count.
2. Optimistic concurrency control is best suited for which scenario?
It avoids locking overhead when conflicts are rare, but frequent conflicts cause repeated retries, making it less suitable for high contention.
3. What must every successful update do to keep optimistic concurrency correct?
Incrementing the version on every write is what allows the next reader's stale version to be detected as outdated.
Flash Cards
What is optimistic concurrency control? — A conflict-detection pattern using a version column checked at write time, with no locks held during editing.
How is a conflict detected? — An UPDATE with WHERE version = <read version> affects zero rows if another write already advanced the version.
When is it a good fit? — When conflicting concurrent edits to the same row are rare, avoiding the cost of holding locks.
Optimistic vs pessimistic concurrency? — Optimistic checks for conflicts after the fact; pessimistic locks the row upfront to prevent conflicts entirely.