I have a checkout flow that reads a product’s stock and reduces it by one if the value is positive. Each checkout runs inside a PostgreSQL transaction using the default Read Committed isolation level.
This is the simplified sequence. The application decides whether to issue the update after reading the query result:
BEGIN;
-- The application checks whether this value is positive.
SELECT stock
FROM products
WHERE id = 42;
-- Issued only if the earlier SELECT returned stock greater than zero.
UPDATE products
SET stock = stock - 1
WHERE id = 42;
COMMIT;With stock set to 1, two simultaneous requests sometimes both succeed, leaving stock at -1. I thought wrapping the read and update in a transaction would prevent that.
What guarantee am I missing?
Does a normal SELECT leave another checkout free to read the same value before either request updates it? I’m trying to decide between a conditional update, explicit row locking, and a stricter isolation level.
What is a reliable way to handle concurrent inventory updates so that exactly one request succeeds and the other gets an out-of-stock result? I’d also like to know which approach requires the application to retry a transaction.
