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;
Advanced answer
Deep dive
Both approaches prevent lost updates under concurrency, but they fail differently.
Optimistic locking
No lock up front.
On update, you assert “I’m updating the version I read”.
If another transaction updated first, your update affects 0 rows → you retry or abort.
Example:
UPDATE accounts
SET balance = balance - 10, version = version + 1
WHERE id = 1 AND version = 7;
Best when:
conflicts are rare,
you want high throughput,
you can retry safely.
Pessimistic locking
Lock the row(s) first (e.g., `SELECT ... FOR UPDATE`).
Others block (or fail fast) until you commit/rollback.