Optimistic vs pessimistic locking — what’s the difference?

Answer

Optimistic locking assumes conflicts are rare: you update with a version/timestamp check and retry on conflict. Pessimistic locking locks rows upfront (e.g., `SELECT ... FOR UPDATE`) to prevent others from changing them.

SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;