PostgreSQL can prevent overselling with a guarded UPDATE
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.
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.