also: optimistic concurrency control · pessimistic locking · SELECT FOR UPDATE · version check · compare-and-set · lost update · lost updates · lost-update problem
Read 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.