Optimistic Locking

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

References

• ↗ https://en.wikipedia.org/wiki/Optimistic_concurrency_control

Tags

Related Terms