Overselling in a flash sale: which lock to reach for, and when you need none

A flash sale opens. There are 100 shirts in stock. When it closes, the orders table has 200 paid orders — and the stock column still says 94 remaining.

Both numbers are wrong, and they are wrong in different ways. That second number is the interesting one: stock is not negative, which is what you would expect from simple over-decrementing. It is 94 because the writes were overwriting each other.

No exception was raised. No log line was written. Every individual request did exactly what its code said.

1. The code that does this

It is the version everybody writes first, and it reads as obviously correct:

def buy(conn):
    stock = conn.execute("SELECT stock FROM product WHERE id = 0").fetchone()[0]
    if stock > 0:
        time.sleep(0.001)              # shipping fee, coupon check, fraud score...
        conn.execute("UPDATE product SET stock = %s WHERE id = 0", (stock - 1,))
        conn.execute("INSERT INTO orders (product_id) VALUES (0)")
    conn.commit()

Read the stock, check it is positive, decrement, create the order. Run it with 40 threads all pressing Buy at the same moment, 200 attempts against 100 shirts:

FLASH SALE — 100 shirts in stock, 200 Buy clicks at once

  1. read → check → write  (the first version anyone writes)
     sold 200 orders | stock shows 94 | OVERSOLD BY 100
     0.20 seconds

Every one of the 200 requests read the stock before any of them had written. All 200 saw a positive number, all 200 passed the check, all 200 sold a shirt. And because each one wrote back stock - 1 computed from its own stale read, the last writer won: the final value reflects one decrement, not 200.

The window is one millisecond wide. That is enough. It always is.

2. The fix most people skip past

The instinct at this point is to reach for a lock. Before doing that, look at why the bug exists: the application read a number, then computed a new value from it. The gap between those two steps is the whole problem.

Close the gap by making the database do both in one statement:

def buy(conn):
    n = conn.execute(
        "UPDATE product SET stock = stock - 1 WHERE id = 0 AND stock > 0"
    ).rowcount                          # <- the check and the decrement, together
    if n == 1:
        conn.execute("INSERT INTO orders (product_id) VALUES (0)")
    conn.commit()

  2. one SQL statement: WHERE stock > 0
     sold 100 orders | stock 0 | matches
     0.22 seconds

Exactly 100 sold. No lock, no version column, no retry loop, and no slower than the broken version. rowcount tells you whether you got a shirt: 1 means yes, 0 means someone else took the last one.

This works because a single UPDATE is atomic. PostgreSQL evaluates stock > 0 and writes stock - 1 against the row as it exists at write time — the stale value your earlier SELECT returned never enters the calculation.

A lot of production locking code exists to protect arithmetic the database would have done atomically for free. If your update fits in one statement, you are done here.

3. When one statement is not enough

Now a real cart: three different items, and the order must reserve all three or none. If the third item is out of stock, the first two must not be decremented.

That cannot be expressed as one UPDATE. You need to hold all three rows steady while you decide. This is what SELECT ... FOR UPDATE is for — it takes a row lock as part of the read, so nobody else can touch those rows until you commit:

def checkout(conn, items):
    for pid in items:
        conn.execute("SELECT stock FROM product WHERE id = %s FOR UPDATE", (pid,))
        time.sleep(0.002)              # check stock, price the line, log...
    for pid in items:
        conn.execute("UPDATE product SET stock = stock - 1 WHERE id = %s", (pid,))
        conn.execute("INSERT INTO orders (product_id) VALUES (%s)", (pid,))
    conn.commit()

Correct, and it does prevent overselling. Then eight customers check out at the same time, each with three random items out of eight products:

CART OF 3 ITEMS — 8 customers checking out at once, 15 carts each

           lock order |  deadlocks |  seconds | stock consistent
----------------------+------------+----------+-----------------
  order in the cart   |        220 |   132.30 | ✓

Two minutes and twelve seconds for 360 items, and 220 deadlocks along the way.

4. Why it deadlocks, and the one-line fix

Customer A has a cart of [3, 7]. Customer B has [7, 3]. A locks row 3 and reaches for 7; B locks row 7 and reaches for 3. Neither can move, and neither will ever release what it holds. PostgreSQL detects the cycle and kills one of them.

The carts are the same. Only the order in which the code happened to lock them differed. So make that order impossible to differ:

    for pid in sorted(items):          # <- lock in a globally consistent order
        conn.execute("SELECT stock FROM product WHERE id = %s FOR UPDATE", (pid,))

           lock order |  deadlocks |  seconds | stock consistent
----------------------+------------+----------+-----------------
  order in the cart   |        220 |   132.30 | ✓
  always sorted by id |          0 |     0.89 | ✓

Deadlocks go to zero and checkout runs 149× faster. Any total order works — sort by id, by SKU, by anything — as long as every transaction in the system uses the same one. If two code paths lock the same tables in different orders, you have this bug waiting.

Note this only fires under real concurrency with overlapping carts. It will not show up in local testing, and it will not show up in staging. It shows up on sale day.

5. The case where pessimistic locking is simply unavailable

Now the flow that actually ships: reserve the stock, send the customer to a payment gateway, wait for the gateway to answer, then confirm the order.

Look again at what a row lock is attached to. It lives from SELECT ... FOR UPDATE until COMMIT, and there is no way to hold one outside a transaction. So holding stock across the gateway call means keeping a transaction open — which means keeping a database connection open — for as long as the payment provider takes to answer.

Connections are a small fixed pool. Eight concurrent checkouts, a pool of four, 200 different products so genuine conflicts are rare:

Optimistic is exactly 2× faster at every gateway latency, with zero retries. Not a single conflict occurred in any of these runs.

The 2× is not a coincidence — it is eight customers divided by four connections. Pessimistic occupies a connection for the whole gateway call, so only four checkouts can be in flight. Optimistic hands the connection back before calling the gateway:

# read the version, then RELEASE the connection
conn = pool.acquire()
stock, v = conn.execute("SELECT stock, version FROM product WHERE id = %s", (pid,)).fetchone()
pool.release(conn)

pay(gateway_ms)                        # holding nothing: no lock, no connection

# confirm, but only if nobody changed the row while we were away
conn = pool.acquire()
n = conn.execute(
    "UPDATE product SET stock = %s, version = version + 1 "
    "WHERE id = %s AND version = %s",  # <- the entire optimistic mechanism
    (stock - 1, pid, v),
).rowcount
pool.release(conn)
# n == 0 -> somebody else bought it first: re-read and try again

This is the case the usual advice gets wrong. “Conflicts are rare here, so it does not matter much” leads you to the simpler-looking pessimistic version and halves your throughput. Worse, the symptom is connection pool exhaustion — which looks nothing like a locking problem while you are staring at it during a sale.

And if the gap is a human rather than a gateway — the customer sits on the payment page for two minutes — pessimistic locking is not slow, it is impossible. You cannot hold a database transaction open for two minutes per customer.

6. What optimistic costs when the item is genuinely hot

Optimistic locking has its own failure mode, and it is the exact opposite scenario: everybody fighting over one row. Back to the original flash sale — 200 buyers, one product:

  3. pessimistic: SELECT ... FOR UPDATE
     sold 100 orders | stock 0 | matches
     0.40 seconds

  4. optimistic: version column + retry
     sold 100 orders | stock 0 | matches
     0.53 seconds | 2136 retries

Both correct. But optimistic burned 2136 wasted attempts to place 100 orders — twenty-one thrown-away transactions per shirt sold. Each one read the row, did the work, lost the race, and started over.

That is the structural difference between the two, in one line: pessimistic turns contention into waiting; optimistic turns it into wasted work. Waiting is bounded and fair. Wasted work compounds — the more people are competing, the more of them lose, and losers immediately rejoin the competition.

7. One more option, on PostgreSQL

You do not have to maintain a version column by hand. Raise the isolation level and PostgreSQL does the same detection for you:

conn.isolation_level = psycopg.IsolationLevel.REPEATABLE_READ

  T1 reads stock 100, T2 reads stock 100
  T1 writes 99, COMMIT
  T2 writes -> SerializationFailure: could not serialize access due to concurrent update
  T2 retries: reads 99, writes 98, COMMIT

Same semantics as the version column, no schema change, and — the real benefit — no update path that can silently forget to include AND version = ? and quietly reintroduce the bug. You still need the retry loop; the failure arrives as an exception instead of rowcount == 0.

Worth knowing while you are here: PostgreSQL implements Repeatable Read as snapshot isolation, not with read locks, and its Repeatable Read already prevents phantom reads — stronger than the SQL standard requires at that level. The isolation-level table most tutorials reproduce is the standard's table, not PostgreSQL's.

8. The lock only protects one layer

Everything above quietly assumed the order goes straight from the buyer into the database. It does not. In any real system the path looks closer to:

browser -> load balancer -> N app servers -> queue -> M workers -> database

Every arrow there is a place the same logical order can become two. The browser retries a request that timed out. The load balancer routes the retry to a different app server, so an in-process mutex protects nothing. And the queue — SQS, RabbitMQ, Kafka — guarantees at-least-once delivery. When a worker dies mid-handler, or an ack is lost, the message is delivered again. That is the queue working correctly, not a bug.

So: keep the pessimistic lock from §3, written perfectly, and let 25 of 100 messages be redelivered.

QUEUE REDELIVERY — stock 200, 100 real orders, 8 workers

  A. SELECT ... FOR UPDATE only  (the row lock is entirely correct)
     queue delivered 125 messages (25 redelivered)
     -> created 125 order rows   (should be 100)
     -> stock left 75            (should be 100)
     -> 25 shirts gone that nobody bought, 25 customers charged twice

The lock did its job. Both deliveries of the same order acquired it, one after the other, each read a consistent stock value, each decremented exactly once. There was no race and no lost update. The inventory is simply gone.

This is the distinction that matters, and it is easy to miss because both things get called “locking”: a row lock serializes access to a row. It does not make an operation happen once. Overselling protection needs the first. Redelivery protection needs the second. They are different guarantees, and no isolation level gives you the second one.

The second guarantee lives at a different layer — on a business key the client generates and that travels with the request through every hop:

CREATE TABLE orders (order_id text PRIMARY KEY, product_id int NOT NULL)

def handle(conn, order_id):
    n = conn.execute(
        "INSERT INTO orders (order_id, product_id) VALUES (%s, 0) "
        "ON CONFLICT (order_id) DO NOTHING",     # <- the guard, at the business key
        (order_id,),
    ).rowcount
    if n == 0:
        conn.commit()                            # already processed: do NOT decrement again
        return
    conn.execute("UPDATE product SET stock = stock - 1 WHERE id = 0 AND stock > 0")
    conn.commit()

  B. guard at the business key: INSERT ... ON CONFLICT DO NOTHING
     queue delivered 125 messages (25 redelivered)
     -> created 100 order rows   correct
     -> stock left 100           correct

Note the insert comes first and the decrement second, in the same transaction. The unique constraint is what decides whether this delivery is the first one; the decrement only runs if it was. Reverse the order and you are back to checking-then-acting, which is the bug from §1 wearing different clothes.

Each layer has its own hazard, and its own mechanism:

The idempotency key does not replace anything in §2 through §7 — it guards a different failure. Two different customers racing for the last shirt is a concurrency problem and needs the DB mechanisms. The same customer arriving twice is an identity problem and needs the unique key. A system that ships only one of the two is not half-safe; it is fully exposed to the other half.

9. The decision, in order

Ask these in sequence and you will not need to guess about conflict probability:

• Can the same request arrive twice? Through a retry, a queue, or a double-click — yes, it can. Put a unique business key on the write path before you think about locks at all (§8). No lock and no isolation level substitutes for it.
• Does the update fit in one SQL statement? Then write it that way and use no locking at all. UPDATE ... WHERE stock > 0 plus rowcount solves single-item stock completely (§2).
• Does the work happen inside one transaction, start to finish? If it spans requests, waits on a human, or calls an external service — pessimistic is unavailable, because it would hold a connection the whole time (§5). Use optimistic.
• Inside one transaction, touching several rows? Use FOR UPDATE — and lock in a sorted, globally consistent order, or you will find the 149× penalty on sale day (§3, §4).
• Inside one transaction, everyone fighting over one row? Pessimistic. Bounded waiting beats 2136 discarded attempts (§6).
• On PostgreSQL, consider Repeatable Read instead of a hand-rolled version column (§7).

The usual rule — optimistic when conflicts are rare, pessimistic when they are common — is not wrong so much as premature. It answers the last question on this list. The first two decide the outcome far more often, and neither of them is about how likely a collision is.

Measured on PostgreSQL 17.11 (Alpine, Docker) via psycopg 3.2.13. Absolute throughput is laptop throughput and not meaningful on its own — the ratios and the failure shapes are the point, and those reproduce.