Optimistic locking.
also: optimistic concurrency control · pessimistic locking · SELECT FOR UPDATE · version check · compare-and-set · lost update · lost updates · lost-update problemRead a row's version, then update only if the version is unchanged, retrying on conflict; pessimistic locking locks the row first.
the two-minute refresher
- Pessimistic: SELECT … FOR UPDATE locks the row until commit; others wait.
- Optimistic: UPDATE … SET version = version + 1 WHERE id = ? AND version = ?; zero rows updated means someone else won.
- Optimistic suits low contention and long edits (a user filling a form).
- Pessimistic suits high contention and short transactions.
- It prevents the lost update: two read-modify-writes where one silently overwrites the other.