Optimistic vs Pessimistic Locking
Compare SELECT ... FOR UPDATE (pessimistic) and version-column / WHERE updated_at = ? (optimistic) patterns.
Two Concurrency Strategies
- Pessimistic — lock the row when you read it; nobody else can change it
- Optimistic — don't lock; on update, verify the row hasn't changed
Pessimistic: SELECT ... FOR UPDATE
Lock now, write later:
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- other transactions cannot lock or update this row
-- compute new balance...
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;All lessons in this course
- ACID Properties and Anomalies
- Isolation Levels: READ COMMITTED, REPEATABLE READ, SERIALIZABLE
- Deadlocks: Detection and Avoidance
- Optimistic vs Pessimistic Locking