Optimistic Locking Versus Pessimistic Locking
TradeoffConcurrencyMidSeniorUpdated
Multiple users may edit the same record, but concurrent edits are rare. You're choosing the concurrency-control model.
Short answer: Optimistic locking (read a version or ETag, update only if it still matches). When conflicts are rare, optimistic locking lets writers proceed without blocking, retries only the unlucky few, and surfaces the conflict to the user.
The Two Options
A
Optimistic locking (read a version or ETag, update only if it still matches)
B
Pessimistic locking (hold a row lock for the whole edit)
Drill This: Your Pick First
The Better Pick
Option A
Optimistic locking (read a version or ETag, update only if it still matches)
When conflicts are rare, optimistic locking lets writers proceed without blocking, retries only the unlucky few, and surfaces the conflict to the user. Pessimistic locks serialize every editor and tie up connections for slow, human-paced edits.
When the Other One Wins
Pessimistic locking (hold a row lock for the whole edit)
Hot rows where conflicts are the norm, like decrementing inventory in a flash sale, turn optimistic locking into a retry storm. Short pessimistic locks (SELECT ... FOR UPDATE) are cheaper there.