Optimistic locking
A version check that rejects your save when someone changed the record since you loaded it, instead of overwriting their work.
See it
What it is
Optimistic locking lets everyone read without holding a database lock, then checks at write time that nobody changed the row first. Store a version number, read version 7 with the record, and update with WHERE id = ? AND version = 7 while incrementing it to 8. If zero rows change, your copy was stale and the save must not silently overwrite the winner.
Reach for it when collisions are uncommon but lost edits would hurt: settings pages, issue descriptions, CMS records, or APIs where a user may keep an edit form open. HTTP uses the same idea with an ETag in the response and If-Match on the update request, and the server answers 412 when the ETag no longer matches. ORMs ship it too: add a lock_version column in Rails and a stale save raises ActiveRecord::StaleObjectError, and JPA does the same through a field marked @Version.
Gotcha: detecting the conflict is only half the feature. Decide whether to show both versions, ask the user to reload, merge field-level changes, or retry an operation that is safe to repeat. Every write path must also increment the version atomically; a bulk update that skips it opens the lost-update hole again.
Ask AI for it
Add optimistic locking to this PostgreSQL update flow. Add a NOT NULL integer version column with default 1, return it when reading the row, and update with UPDATE ... SET ..., version = version + 1 WHERE id = $1 AND version = $2 RETURNING *. Treat zero returned rows as a conflict: respond 412 Precondition Failed when the request sent If-Match and the version moved, or 409 Conflict when the version arrived in the body, and return the current record either way. Expose the version as an ETag and require If-Match on REST updates. Make every mutation path bump the version, and add concurrent tests proving a stale editor cannot overwrite the winning edit.