Topic 7.8
Concurrency Control: Optimistic vs Pessimistic Locking & MVCC
In one line
When two requests change the same row at the same time, one update can silently overwrite the other (a lost update). Pessimistic locking prevents it by locking the row first (SELECT ... FOR UPDATE), so others wait. Optimistic locking lets everyone work freely and checks a version number at write time, rejecting stale updates. Underneath, databases like Postgres use MVCC, keeping several versions of rows so readers never block writers.
Think of it like this
Editing a shared recipe card. Pessimistic: you take the card to your desk and nobody else can edit until you return it. Optimistic: everyone copies the card, and when you hand back changes, the librarian checks whether the card changed since you copied it, and if so asks you to redo your edit on the new version.
Words you'll meet
New words in this topic, in plain English. Come back here whenever one feels fuzzy.
- Lost update
- An update overwritten by a concurrent one that started from the same old value.
- Pessimistic locking
- Locking data before changing it so others must wait.
- Optimistic locking
- Checking at write time that data hasn't changed since it was read, usually with a version number.
- SELECT ... FOR UPDATE
- A query that reads rows and locks them until the transaction ends.
- MVCC
- Keeping multiple versions of rows so each transaction sees a consistent snapshot without blocking readers.
- Version column
- A counter incremented on every update, used to detect concurrent changes.
Step by step
01The lost menu edit
Two kitchen managers edit the same dish in the partner portal. Manager A changes the price, Manager B changes the description. Both forms loaded the dish at the same time and save the whole record, so whoever saves second wipes the other's change.
02Optimistic locking with a version
Priya adds a version column. Each save includes the version that was read. The second save updates zero rows, so the portal tells Manager B 'this dish was changed by someone else, reload to see their changes'.
@Entity
public class Dish {
@Id Long id;
int pricePaise;
String description;
@Version // JPA adds "AND version = ?" and increments it on every update
long version;
}
// A stale save throws OptimisticLockException → return 409 Conflict to the portal03Pessimistic locking for a contended row
The wallet balance is updated by many concurrent payments and refunds. Retrying optimistic conflicts would be frequent, so the payment transaction locks the wallet row, checks, and updates. Other transactions for the same wallet wait a few milliseconds.
04MVCC: readers don't wait
While the payment transaction holds the row lock, a report query reading the wallet isn't blocked: it sees the last committed version. Only another writer to the same row waits.
Break it on purpose
Errors are the best teachers. Make each change, read the error, guess what went wrong, then reveal the answer.
Break #1
Holding a lock while the user decides
The checkout page runs SELECT ... FOR UPDATE on the stock rows when the page opens and commits only when the user pays.
Myth vs fact
Myth
Transactions automatically prevent lost updates.
Fact
At Postgres's default Read Committed level, two transactions can still read-modify-write the same row and lose an update. You need atomic updates, row locks, version checks, or a stricter isolation level.
Pro corner
Extra depth for experienced readers. New to this? Skip it for now and come back later.
- ▸
SELECT ... FOR UPDATE SKIP LOCKEDlets many workers take different rows from a queue table without waiting for each other, which is the standard way to build a job queue on Postgres.NOWAITfails immediately instead of waiting.
Remember this
- 1
Lost update: two transactions read the same value, both change it, and the second write replaces the first. Read-modify-write in application code is the classic cause (Topic 5.5).
- 2
Pessimistic locking:
SELECT ... FOR UPDATElocks the rows until the transaction ends. Safe and simple, but others wait, and holding locks across slow work or user think-time causes queues and deadlocks. - 3
Optimistic locking: store a
versioncolumn, read it, and update withWHERE id = ? AND version = ?, incrementing it. Zero rows updated means someone changed it first: reload and retry, or show a conflict. No waiting, but retries under heavy contention. - 4
Atomic updates often remove the need for either:
UPDATE stock SET qty = qty - 1 WHERE id = ? AND qty > 0changes and checks in one statement. - 5
MVCC (multi-version concurrency control): each write creates a new row version, and each transaction reads the versions visible to its snapshot. Readers don't block writers and writers don't block readers, though writers to the same row still conflict.
- 6
Choose by contention: optimistic for rarely-conflicting edits (profiles, menus edited by one admin), pessimistic or atomic updates for hot, contended rows (stock, seats, balances).
Explain it without notes
When is optimistic locking a bad choice?
How does MVCC let readers avoid blocking?
Practice
Add optimistic locking to an entity and handle the conflict in the API.
Show a lost update in two psql sessions and fix it two ways.
Trade-offs
- ↔
Optimistic locking has no waiting and suits low contention, but conflicts surface to users or retries. Pessimistic locking makes conflicts wait instead of fail but risks queues and deadlocks. Atomic statements are fastest when the rule fits in one UPDATE.
Run it in production
You've designed it. Now build, operate, and break the same idea hands-on in the DevOps courses:
Done when you can
I can explain and demonstrate a lost update.
I can implement optimistic locking with a version column.
I use FOR UPDATE only in short transactions.