Optimistic Locking

Optimistic locking assumes conflicts are rare: it lets multiple transactions read and edit a row freely, then checks at write time (often via a version number or timestamp column) whether the row changed since it was read — rejecting the write if it did.

No actual database lock is held while the user is editing — that's the "optimistic" part. A typical pattern: read a row along with its version column, and on UPDATE add WHERE version = :original_version; if zero rows are affected, someone else updated it first, and the application must reload and retry.

It scales well for applications with long user-think-time between read and write (like a web form), where holding a real database lock the whole time would be wasteful.

UPDATE accounts
SET balance = 500, version = version + 1
WHERE id = 1 AND version = 3;
-- 0 rows affected means someone else updated it first