Optimistic Concurrency Control.

🐞The bug

Two support agents open the same ticket. Both see "priority: low". Anna bumps it to High and saves. Bob, a second later, changes the assignee and saves. Anna's High is gone — silently reverted to low. No error, no warning. This is the lost update problem.

The naive code both ran:

row = SELECT * FROM tickets WHERE id = 42
// ...user edits in the UI...
UPDATE tickets SET priority=?, assignee=? WHERE id = 42

Bob's UPDATE overwrites every column with the stale snapshot he loaded BEFORE Anna saved. Classic read-modify-write race.

🔒The heavy fix: pessimistic locking

SELECT ... FOR UPDATE locks the row so Bob blocks until Anna commits. It works, but it holds a DB lock across the user's "thinking time" — seconds or minutes while a human edits a form. That kills throughput and invites deadlocks. Overkill when conflicts are rare.

🎯The lighter fix: optimistic concurrency control (OCC)

Assume conflicts are RARE. Don't lock. Instead, detect the collision at write time and reject the loser. Add a version column:

ticket #42 -> version 7

Read it with the row. On save, make the update conditional on the version still being what you read — a compare-and-swap:

UPDATE tickets
SET priority=?, assignee=?, version = version + 1
WHERE id = 42 AND version = 7

Then check rows-affected:

• 1 row -> you won. Version is now 8.
• 0 rows -> someone else bumped it first (it's 8 already). Your write is REJECTED. Reload, re-apply the user's intent, and retry — or show "this ticket changed, review before saving."

🧠Why it works

The WHERE version = 7 makes check-and-write a single atomic step in the DB. No lock held during think-time. The cost moves from "everyone waits" to "the rare loser retries." That trade — cheap reads, occasional retry — is why OCC underpins HTTP ETags / If-Match, JPA/Hibernate @Version, DynamoDB conditional writes, and most REST APIs that return 409 Conflict.

🪤Gotchas

• A plain timestamp as the version is risky: clock skew + same-millisecond writes can collide. Prefer a monotonic integer or a uuid token.
• OCC guards ONE row's update. A multi-row invariant still needs a transaction around the whole compare-and-swap.
• High-contention hotspots (everyone editing the same row) -> OCC degrades into retry storms; there, pessimistic locking or a queue wins.

🏋️Exercise

Your API does: GET returns {data, version}; PUT applies changes. Design the PUT so a stale client gets 409 instead of clobbering. Which HTTP header carries the version (hint: it already exists)? And on a 409, what are the two sane recovery strategies — when do you auto-retry vs ask the human?