SQL Concurrency and Isolation
Several requests can try to change the same data at once. A query that works in a single-user test may lose updates or oversell stock under concurrency. Express the invariant in the database operation and check its outcome.
Understand the idea
This conditional update decrements stock only when stock is positive. It avoids a separate application read followed by an unconditional write of an old value. In SQLite, changes() reports the preceding statement's changed-row count. Other drivers provide affected-row information through their own APIs. More complex reservations may need additional locking, retries, and idempotency design.
Where to try it
Run this example in the SQL playground. The lesson link loads the shop tables and sample rows before this query. Select Run when you are ready. Every run starts fresh, so your changes do not affect the website or a previous run.
SQLite sample data · MySQL / MariaDB sample data · See the sample tables
A worked example
UPDATE products
SET stock = stock - 1
WHERE product_id = 2 AND stock > 0;
SELECT changes() AS changed_rows;
SELECT stock FROM products WHERE product_id = 2;
Read the result
One row changes, and Desk lamp stock becomes 7. A zero-row outcome means the condition did not match and should not be reported as a successful reservation.
Try one change
Try the same guard on product 3. Verify that no row changes and explain what message the application should show.