Developer/DataManagement/Database/RowVersioning-OptimisticConcurrency/Fundamentals/optimistic-concurrency-control-and-row-versioning

Optimistic Concurrency Control and Row Versioning

Concept

When two processes read the same row, each modify it independently, and then both write their changes back, the second write can silently overwrite the first with no error raised — this is the lost update problem. It's a real correctness bug, not a theoretical one: a payment status flips back to "pending" because a stale read raced a status-transition write, or a booking's seat count gets restored to a value that ignores a concurrent reservation, and nothing in the logs flags it because, from the database's point of view, both writes succeeded.

Optimistic concurrency control (OCC) solves this without taking a lock for the duration of the read-modify-write cycle. Instead, every row carries a version marker — a monotonically incrementing integer, or an automatically-updated timestamp/binary value (SQL Server's rowversion type is a purpose-built example). A read captures the current version along with the data. When the write happens, the UPDATE is made conditional on the version still matching what was read (WHERE id = ? AND version = ?); if a concurrent write already changed the row, the version no longer matches, zero rows are affected, and the application detects the conflict from the affected-row count instead of getting silently overwritten. The application then decides how to handle it — reject and ask the user to retry with fresh data, or attempt an automatic merge for fields that don't actually conflict.

This is "optimistic" specifically because it assumes conflicts are rare and pays no cost when there isn't one — no lock is held while the user is, say, filling out a form between the read and the write. Pessimistic concurrency control takes the opposite bet: acquire a row lock at read time and hold it until the write completes, guaranteeing no conflict can occur but blocking every other writer to that row for the entire window, including idle time the user spends thinking.

Tradeoffs

Approach	Contention cost	Correctness guarantee	Best fit
No concurrency control	None	None — the lost update problem is live	Never acceptable for data where a silent overwrite matters
Optimistic (version column)	Low — no lock held between read and write; conflicts detected, not prevented	Strong, but conflicts surface as a failed write the caller must handle	Read-heavy or low-contention rows with a real gap between read and write (user-facing edits, long-running workflows)
Pessimistic (row lock)	High under contention — every reader/writer to the row blocks until the lock releases	Strong, and conflicts are prevented rather than just detected	High-contention rows where the read-modify-write window is short and blocking is cheaper than retry logic (a tight loop incrementing a counter)

The real axis isn't "optimistic is better" or "pessimistic is better" — it's how long the gap is between read and write, and how often two writers actually collide on the same row. A version column costs almost nothing when conflicts are rare, because the only overhead is one extra column comparison in the WHERE clause. A row lock costs almost nothing when the critical section is short. Each approach's cost scales with exactly the case the other one is bad at.

When to use / when not to

Use optimistic concurrency control wherever a row can be read, held (in memory, in a UI form, across a multi-step workflow) for a non-trivial time, and then written back — this is the common case for anything touched by a human in a request/response cycle.
Use it specifically on rows where silently losing an update would be a real product or financial problem: payment/booking status, inventory counts, any state machine transition where "the second write wins, unnoticed" is unacceptable.
Prefer pessimistic locking instead when the read-modify-write window is short and predictable (a single database transaction, not a user-facing form) and conflicts are frequent enough that retrying an optimistic write repeatedly would itself become the bottleneck.
Don't add a version column to every table reflexively — tables that are effectively single-writer, or where a "last write wins" outcome is genuinely fine (a cached denormalized field recomputed on every write), don't need the extra column or the retry-handling code it requires.
Don't treat a failed optimistic write as an error to surface raw to the user — the calling code needs an explicit retry-or-merge path, or the OCC mechanism just becomes a confusing failure mode instead of a correctness guarantee.

Common pitfall

Adding the version column and the conditional UPDATE, but never actually handling the zero-rows-affected case in application code — the write silently does nothing, and because no exception was thrown, the caller assumes it succeeded. Row versioning only delivers its guarantee if the application checks the affected-row count (or equivalent) after every conditional write and explicitly branches on "the version didn't match" versus "the row doesn't exist" versus "it succeeded." Many ORMs raise a specific optimistic-concurrency exception for exactly this reason (Hibernate's StaleObjectStateException, EF Core's DbUpdateConcurrencyException) — using a raw conditional UPDATE without checking its result reintroduces the lost-update problem the version column was added to prevent, just one layer further from the SQL.

Engineering Lens

Row versioning is one of the cheapest correctness guarantees available in a relational schema — one column, one clause in the WHERE, and no locking infrastructure — which is exactly why its absence on state-transition tables (payment status, order status, inventory) is a real finding in a design review, not a nitpick. The strong review question isn't whether OCC exists somewhere in the codebase; it's naming the specific tables where two writers can plausibly race on the same row, confirming each one has a version check, and confirming the retry path on conflict was actually built rather than assumed. A version column with no caller checking the update result gives false confidence — it looks like the lost-update problem was solved when it wasn't.