✏️ Explanatory Question
Level: Advanced — A design-level concurrency question that shows architectural thinking.
These are two strategies for handling concurrent updates to the same data — they differ in when and whether they lock the resource.
version column, while pessimistic locking uses database locks like SELECT ... FOR UPDATE.
| Feature | Pessimistic | Optimistic |
|---|---|---|
| Assumption | Conflicts are likely | Conflicts are rare |
| Locks resource? | Yes, immediately | No lock |
| Conflict check | Prevents upfront | Detects at update time |
| Concurrency | Lower | Higher |
| Deadlock risk | Higher | None (no locks) |
| Best for | High-contention data | Low-contention, read-heavy |
-- Lock the row so no one else can modify it until we commit
START TRANSACTION;
SELECT balance FROM accounts
WHERE acc_id = 1
FOR UPDATE; -- exclusive lock held here
UPDATE accounts SET balance = balance - 500 WHERE acc_id = 1;
COMMIT; -- lock released-- Table has a 'version' column
-- 1) Read the row and its current version
SELECT balance, version FROM accounts WHERE acc_id = 1; -- version = 5
-- 2) Update ONLY if version hasn't changed since we read it
UPDATE accounts
SET balance = balance - 500,
version = version + 1
WHERE acc_id = 1 AND version = 5;
-- 3) If 0 rows are affected, someone else updated it first -> conflict!
-- The application must re-read and retry.