Locking and SELECT FOR UPDATE

Understanding how a database works in isolation on a single terminal is simple... no wait, I already said that! Sometimes "Atomic Updates" (e.g. stock = stock - 1) aren't enough.

Let's imagine you have to check whether the user has enough balance to buy 50€ worth of stuff:

1. SELECT balance FROM users WHERE id = 1;
2. JS: If balance is < 50, block them!
3. Otherwise proceed with checkout...
4. Run the deduction! (And here you find a user who, while validating the check at step 2, fired off another parallel one that burned through their account and now you're going below zero!).

FOR UPDATE Explicit Locking

Adding FOR UPDATE at the end of a SELECT magically transforms your innocent read into a sealed Lock against other clients until the next COMMIT. No one else will be able to do UPDATE (or concurrent reads-for-update) on those specific selected rows until you decide what to do with them!

BEGIN;

-- If id=3 had already been locked by T1, T2 will sit in perpetual loading on this "read"
-- frozen in place, before receiving the raw data, until T1 runs COMMIT freeing everyone!
SELECT balance
FROM users
WHERE id = 3
FOR UPDATE;

-- at this point our Node JS instance lives with the guarantee, knowing for certain that NO ONE ELSE in the world has
-- manipulated (nor been able to read with intent to alter) that balance while we complete the application logic

UPDATE users SET balance = balance - 50 WHERE id = 3;

COMMIT;

There's even FOR SHARE which lets other readers keep reading (if on a base lock) without blocking state reads, but blocking any possible subsequent update until you commit. For modifications though, always use the brute force of the exclusive locking clause:

Try it yourself