Locks and atomic updates
Also called SELECT FOR UPDATE, Optimistic locking
Three ways out of a race. An atomic update does the maths inside one statement: SET stock = stock - 1. A lock makes others wait: SELECT ... FOR UPDATE. A version check lets everyone try and makes the loser start again. Try them in that order.
Asked to fix a race, an agent often adds a lock inside the program, which works perfectly until you run two copies of the server. The fix has to live where the shared data lives. And the simplest fix is often just moving the arithmetic into the UPDATE.
“Replace the read and write with one statement: UPDATE products SET stock = stock - 1 WHERE id = $1 AND stock > 0, and check the row count. Zero rows means sold out. Don't add an in-process mutex, because we run more than one server.”