compare and set
An update that changes a row only if it still holds the value the program expects, and reports whether it did.
In more detail
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock >= 1 either succeeds for one row or affects zero rows, which tells the application the value changed or ran out. Treat zero affected rows as a conflict to report or retry, not as success.
Example
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock >= 1;Related terms
- lost update — A concurrency bug where two sessions read the same value, both write a result based on it, and the later write silently erases the earlier one.
- affected row count — The number of rows an INSERT, UPDATE or DELETE actually changed, reported back to the program that ran it.
- UPDATE — The SQL statement that changes values in existing rows, choosing the rows with WHERE and the new values with SET.
- 409 Conflict — The HTTP status meaning the request conflicts with the current state of the resource.
- optimistic concurrency control — A way to prevent lost updates by reading a version, preparing a change, and making the write succeed only if that version is still current.
Taught in
- Isolation Levels And The Lost Update — PostgreSQL, Indexing & Relational Data Modeling