How can an atomic update prevent overselling?
Practice this questionA reservation service reads stock, checks availability, and then writes a new quantity. Concurrent requests can both pass the check. How would you protect the stock update?
Short explanation:
A conditional atomic update makes the database check availability and reduce stock as one write operation. This avoids the application-level read/check/write gap. It protects the stock row, but related reservation records and repeated client submissions still need their own transaction and idempotency design.
Example and explanation:
-- Validate that quantity is a positive integer before this query.
UPDATE products
SET stock = stock - $1
WHERE id = $2 AND stock >= $1
RETURNING id, stock;-
Bind quantity and product ID as parameters; this update checks and changes stock atomically.
-
Zero returned rows means missing product or insufficient stock; map the response as required.
-
If a reservation record must also be created, do both in one transaction with an idempotency key.
-
Add tenant/ownership filtering where required; an atomic query does not replace authorization.
-
Check the affected-row count; zero rows is a business outcome to handle, not a successful reservation.
Interview point:
- State the exact guarantee the query provides and avoid claiming it implements the entire checkout flow by itself.