Short answer
Pessimistic locking reserves a resource immediately and prevents others from reading or changing it until the transaction ends. Optimistic locking allows concurrent edits, checks the record's version when saving, and rejects a transaction if the version changed.
Explanation
Pessimistic locking (such as SELECT ... FOR UPDATE) is useful when conflicts are highly likely (high contention), such as booking the last seat on a plane or buying limited stock, because it avoids wasted processing. Optimistic locking is ideal when conflicts are rare and reads greatly outnumber writes: update the record with WHERE version = @currentVersion and increment the counter. If the row has already changed, the operation affects zero rows, so ask the user to retry without expensive database locks.
Code example
// Optimistic lock using the version number in SQL:
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 42 AND version = 5;
// If the result is 0 rows, another thread changed the inventory before you!Common mistakes
- Using pessimistic locks for long transactions involving interactive user input, potentially locking database tables indefinitely.