Lock the row before you touch it
Day 151 of 155
👉 Full deep-dive with code examples
The Fitting Room Analogy
A shop has one fitting room.
You take the garment in and the attendant hangs the "occupied" sign, so the next customer waits at the door rather than walking in on you.
Nobody has to sort out an awkward collision afterwards, because the collision was ruled out up front.
The cost is the queue: while you are deciding, everyone else stands still.
That is pessimistic locking. You claim the row before you touch it, and you pay for that certainty in waiting.
Lock At Read Time, Not Write Time
Two transactions want the same row. Two ways to keep them honest:
- Optimistic: let both read, then catch the clash when the second writes.
- Pessimistic: lock the row as you read it, so the second reader waits.
It assumes a collision is likely, so it prevents the fight instead of refereeing it afterwards.
SELECT ... FOR UPDATE
BEGIN;
SELECT stock FROM products
WHERE id = 42
FOR UPDATE; -- row locked from here
UPDATE products SET stock = stock - 1
WHERE id = 42 AND stock > 0; -- guard, or you sell stock you do not have
COMMIT; -- lock released
At the common read-committed level, another transaction running that same query blocks until the COMMIT, then reads the decremented stock rather than the stale value.
The lock lives and dies with the transaction: commit or roll back, and it is gone.
What It Costs You
Waiting. And deadlocks: A holds row 1 and wants row 2 while B holds row 2 and wants row 1.
Databases usually detect that and abort one, so your code retries.
Keep them short. A lock held while a user decides, or while a slow API answers, turns that row into a queue for everybody.
In One Sentence
Pessimistic locking takes a lock on the row as you read it, so other transactions wait their turn instead of racing you to the write.
🔗 Enjoying these? Follow for daily ELI5 explanations!
Making complex tech concepts simple, one day at a time.
