Race Conditions in Application Code

~12 min · transactions, concurrency

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The classic race

"Read inventory; if > 0, decrement and confirm sale." Two requests run this in parallel. Both read inventory = 1. Both decide it's safe. Both decrement. Inventory is now -1, you sold something you don't have. The bug isn't in any single line — it's in the gap between the read and the write.

Three fixes, each appropriate sometimes

Atomic UPDATE — push the check and decrement into one statement: UPDATE products SET inventory = inventory - 1 WHERE id = ? AND inventory > 0. If 0 rows updated, the sale failed.
SELECT FOR UPDATE — lock the row when you read it; do the conditional logic in app code; UPDATE; COMMIT.
SERIALIZABLE isolation — let PostgreSQL detect the conflict and ask you to retry.

The most common one

Atomic UPDATE is usually the cleanest answer. It's one round trip, no explicit lock, no retry. Reach for SELECT FOR UPDATE when the conditional logic is too complex to fit into a single SQL statement.

Code

Atomic UPDATE — the inventory pattern·sql

UPDATE products
SET inventory = inventory - 1
WHERE id = :product_id AND inventory > 0
RETURNING inventory;
-- If RETURNING returns a row: sale succeeded.
-- If no row returned: sold out, the UPDATE matched nothing.

SELECT FOR UPDATE — when logic is complex·sql

BEGIN;
SELECT inventory, max_per_customer, customer_purchase_count(?, ?)
FROM   products
WHERE  id = ?
FOR UPDATE;
-- ... complex business logic in app ...
UPDATE products SET inventory = inventory - 1 WHERE id = ?;
COMMIT;

SERIALIZABLE — let Postgres referee·python

def safe_purchase(c, product_id, customer_id):
    for attempt in range(5):
        try:
            with c.transaction(isolation_level="serializable"):
                inv = c.execute("SELECT inventory FROM products WHERE id = %s", (product_id,)).fetchone()[0]
                if inv > 0:
                    c.execute("UPDATE products SET inventory = inventory - 1 WHERE id = %s", (product_id,))
            return True
        except psycopg.errors.SerializationFailure:
            continue
    return False

External links

PostgreSQL Concurrency Control

Exercise

Find a 'read then write' pattern in your code that could race. Rewrite it as an atomic UPDATE with the check in the WHERE clause. Verify by running it from two concurrent clients.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousIdempotency in Database Operations Next →Transaction Patterns for Web Apps

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.