Use optimistic concurrency control
Optimistic concurrency assumes conflicts are rare — no locks, just version checking on commit. Learn the version column pattern.
- 1
Optimistic Concurrency Control (OCC): read data without locking, modify locally, on commit check if the data changed since read. Implementation: add a version column (integer or timestamp). On UPDATE, check WHERE version = read_version and increment version. If 0 rows updated, someone else modified it — retry or fail.
- 2 SQL Formatter →
Format the UPDATE statement with the version check: UPDATE table SET col = ?, version = version + 1 WHERE id = ? AND version = ?. If the affected row count is 0, the record was modified by another transaction — handle the conflict.
- 3 Hash Calculator →
For distributed systems, use a hash of the record content instead of a version number. This detects any change without a dedicated version column. On update, compare the current hash with the read-time hash. This is useful when you cannot modify the schema.
Frequently asked questions
- Optimistic vs pessimistic locking?
- Optimistic: no locks, check on commit — best when conflicts are rare (read-heavy, web apps). Pessimistic: lock on read — best when conflicts are common (write-heavy, financial). Optimistic has better concurrency but requires conflict handling. Pessimistic is simpler but can cause lock contention and deadlocks.
- What if the conflict rate is high?
- Switch to pessimistic locking (SELECT ... FOR UPDATE) or retry with backoff. High conflict rate means optimistic concurrency is thrashing — too many retries. Also consider: coarser locking (lock the parent instead of individual rows), queueing writes, or partitioning to reduce contention.
Workflows that solve this
Learn more
Want to understand why?