Optimistic Locking
debt(d9/e5/b5/t7)
Closest to 'silent in production until users hit it' (d9). The detection_hints field explicitly states 'automated: no'. The common mistake of not handling the 0-rows-updated case causes silent data loss — no compiler, linter, or SAST tool flags a missing rows-affected check. The failure surfaces only when concurrent users overwrite each other's data in production.
Closest to 'touches multiple files / significant refactor in one component' (e5). The quick_fix describes adding a version column, updating every UPDATE statement to include WHERE version = N, and adding retry/exception logic throughout the application wherever that entity is written. This is not a single-line swap — it requires schema migration plus changes to all write paths for the affected entity, potentially spanning multiple files.
Closest to 'persistent productivity tax' (b5). Applies to web, cli, and queue-worker contexts. Once optimistic locking is adopted for an entity, every future developer writing update logic for that entity must remember to increment the version, check rows-affected, and implement retry logic. It is not codebase-wide by default but imposes an ongoing tax on all write paths for locked entities.
Closest to 'serious trap' (t7). The misconception is explicit: developers assume optimistic locking *prevents* conflicts. In reality it only *detects* them at commit time and requires application-level retry logic — conflicts still happen. Additionally, the 0-rows-updated case (silent data loss) and the degraded performance under high contention are non-obvious behaviors that contradict the intuition that 'locking' means no concurrent writes succeed.
TL;DR
Explanation
Pattern: read record with version number, modify, update WHERE version = read_version, check rows affected = 1. If 0 rows updated: conflict — retry or error. The version column increments on every update. No locks held between read and write — high throughput. Best for: low-contention scenarios (most updates succeed without conflict). Implementations: Doctrine ORM @Version annotation, JPA @Version, Hibernate. Database: UPDATE ... WHERE id = ? AND version = ?. Redis: WATCH/MULTI/EXEC (optimistic transaction). Contrast with pessimistic locking (SELECT FOR UPDATE — locks row immediately).
Common Misconception
Why It Matters
Common Mistakes
- Not incrementing the version on every update — defeats the mechanism.
- Not handling the 0-rows-updated case — silent data loss.
- Using optimistic locking for high-contention scenarios — many retries degrade performance.
Code Examples
// No conflict detection:
SELECT qty FROM inventory WHERE id = 1;
-- Another process also reads qty = 10
UPDATE inventory SET qty = 9 WHERE id = 1; -- Both processes set 9, losing one decrement-- Read:
SELECT qty, version FROM inventory WHERE id = 1;
-- (qty=10, version=5)
-- Write with version check:
UPDATE inventory
SET qty = 9, version = version + 1
WHERE id = 1 AND version = 5;
-- 0 rows updated? Another process got there first — retry or error