What does SELECT ... FOR UPDATE do in PostgreSQL?
It locks the selected rows until the transaction ends, so other transactions cannot update, delete or lock them meanwhile. Use it to read a value and change it safely, like a balance or a seat count.
Explain it simply
Booking the last seat on an IRCTC train: two people may see "1 seat left" at the same moment. Without a lock both can book it. FOR UPDATE is like telling the clerk "hold this seat chart, I am not done". Anyone else who tries to change or lock those rows waits until you commit or roll back.
Plain readers are not blocked, because MVCC lets them read the old version.
Example
BEGIN;
SELECT seats_left FROM trains WHERE id = 12627 FOR UPDATE;
-- row 12627 is now locked for other writers
UPDATE trains SET seats_left = seats_left - 1 WHERE id = 12627;
COMMIT; -- lock releasedVariants: FOR NO KEY UPDATE (weaker, does not block foreign key checks), FOR SHARE (others may read-lock but not write), and NOWAIT to fail immediately instead of waiting.
Often you can skip the lock entirely with an atomic statement:
UPDATE trains SET seats_left = seats_left - 1
WHERE id = 12627 AND seats_left > 0;What to say in the interview
"FOR UPDATE takes row-level locks that last until the transaction ends, preventing lost updates in read-then-write logic. A single atomic UPDATE with a condition is often simpler. I keep the locking transaction short to avoid queues."
Common mistakes
- Holding FOR UPDATE locks while doing slow work outside the database.