Optimistic and pessimistic locking

In short: With pessimistic locking you lock data before changing it, with optimistic locking you only check on save whether someone changed something in between.

In more detail: Both methods prevent “lost updates”, where two people edit the same record and the second save overwrites the first.

In Depth

• Pessimistic: SELECT ... FOR UPDATE locks the row until the transaction ends. Safe under high conflict, but others must wait, and deadlocks loom.
• Optimistic: The row has a version number (or timestamp). On save: UPDATE ... SET ..., version = version + 1 WHERE id = ? AND version = ?. If 0 rows changed, someone else saved and you must reload or merge.
• Web: The user reads a form and saves minutes later. Long locks are impractical, so optimistic is common (ETag and If-Match in HTTP).
• Choice: Rare conflicts → optimistic, frequent conflicts on the same data (stock, account balance) → pessimistic or atomic updates (SET stock = stock - 1).
• Context: See MVCC and isolation levels.

See also: transactions and ACID