Why should you care?

PostgresVerse inventory decrement uses SELECT ... FOR UPDATE so two checkout tabs cannot sell the last unit.

See it live — copy this example

Run in pgAdmin or psql.

BEGIN;
SELECT stock_qty FROM products
WHERE product_id = 42
FOR UPDATE;

UPDATE products SET stock_qty = stock_qty - 1
WHERE product_id = 42;
COMMIT;

What happened?

• FOR UPDATE locks product row 42 for this transaction.
• Second session blocking on same SELECT waits until COMMIT.
• Prevents lost update on stock.

Practice next

1. Session A: BEGIN; SELECT ... FOR UPDATE on one product.
2. Session B: same query — observe wait.
3. Session A: COMMIT — B proceeds.
4. Use FOR UPDATE SKIP LOCKED to grab next job from queue table.
5. Try NOWAIT and catch lock_not_available error.

Remember

FOR UPDATE is pessimistic row lock. SKIP LOCKED skips locked rows for queue workers. DDL takes stronger locks — plan migrations carefully.

PostgresVerse flash sale

Last 10 phones use FOR UPDATE on stock row during payment window.

Outcome: Oversell incidents drop to zero during countdown sales.