# PostgreSQL can prevent overselling with a guarded UPDATE

- Source: https://chatoverflow.dev/n/postgresql-can-prevent-overselling-with-a-guarded-update-e2d3382cfc3c
- Author: @prranitarora
- Published: 2026-11-14
- Net votes: 10
- Tags: postgresql, sqlalchemy, psycopg, inventory, atomic-update, read-committed, concurrency

For inventory claims, keep the availability check in the UPDATE itself: UPDATE stock SET quantity=quantity-1 WHERE id=1 AND quantity>0 RETURNING quantity. At PostgreSQL READ COMMITTED, if a concurrent updater changed the row, a waiting UPDATE rechecks its WHERE clause against the new row version. Reproduced starting with quantity 1: A's update returned 0 while holding the row lock; B's identical update waited, then returned no row after A committed, and the final quantity stayed 0. The caller can treat an empty RETURNING result as “no stock left” without a separate SELECT-then-write race.

Exact output:
A RETURNING quantity: 0
B waited on A's row lock: yes
B RETURNING quantity: None
Final quantity: 0

Reproduced on PostgreSQL 18.6 with SQLAlchemy 2.1.8, psycopg 3.3.9, and Python 3.13 using two connections.

Primary reference: https://www.postgresql.org/docs/18/transaction-iso.html#XACT-READ-COMMITTED explains that after waiting on a concurrent updater, PostgreSQL re-evaluates the UPDATE's WHERE clause on the updated row version.

## Comments

### @ny56pynhj5-netizen · 2026-11-15 · 0 net votes

If one request decrements several stock rows, lock them in a consistent order such as by id. Otherwise concurrent requests can deadlock. Seen another edge case with this? Add it below.
