DEV Community

Cover image for 1 Ticket, 20 Orders: The SQL Race Condition Hiding in Your Code
Muiduzzaman Lipu for Refactorly

Posted on

1 Ticket, 20 Orders: The SQL Race Condition Hiding in Your Code

One concert ticket left. Twenty people click "Buy" at the same time. My code sold it twenty times, and the stock still said 0, so nothing looked wrong.

Here's the bug, the measurements, and two ways to fix it. Everything below ran on PostgreSQL 17 with Node.js 22 and node-postgres (pg), on a local throwaway database.

Video version:

The code: looks correct, right?

async function buy(customer) {
  const { rows } = await db.query("SELECT stock FROM products WHERE id = 1");
  if (rows[0].stock > 0) {
    await db.query("UPDATE products SET stock = $1 WHERE id = 1", [rows[0].stock - 1]);
    await db.query("INSERT INTO orders (product_id, customer) VALUES (1, $1)", [customer]);
    return true;
  }
  return false;
}
Enter fullscreen mode Exit fullscreen mode

Read the stock. If it's above zero, write stock - 1 and create an order. With one request at a time, this works perfectly, and your tests pass.

20 buyers at the same time

// products: (id 1, 'Concert ticket', stock 1)
const results = await Promise.all(
  Array.from({ length: 20 }, (_, i) => buy(i + 1))
);
Enter fullscreen mode Exit fullscreen mode

I reset the table and ran this 10 times. Orders created per run:

1  19  19  20  20  19  20  20  19  20
Enter fullscreen mode Exit fullscreen mode

9 of 10 runs oversold the single ticket 19 or 20 times. (The first run was 1 only because the connection pool was still opening connections one by one, which accidentally serialized the requests.) And after every run:

SELECT stock FROM products WHERE id = 1;   -- 0
Enter fullscreen mode Exit fullscreen mode

The stock looks perfectly fine. You'd only notice when 20 people show up with tickets.

Why it happens: read → check → write

Buyer A: SELECT stock   → 1
Buyer B: SELECT stock   → 1
Buyer A: 1 > 0 ✅  UPDATE stock = 0, create order
Buyer B: 1 > 0 ✅  UPDATE stock = 0, create order
Enter fullscreen mode Exit fullscreen mode

Both buyers read 1 before either one wrote. Both pass the check, both write 0, both get an order. This is a lost update (or "check-then-act") race condition. A plain BEGIN … COMMIT around it doesn't fix it either: under PostgreSQL's default READ COMMITTED isolation, both transactions can still read 1. (I tried it: wrapped in BEGIN/COMMIT, it created 20 orders in 5 out of 5 runs.)

Fix 1: let the database do the check

async function buy(customer) {
  const r = await db.query(
    "UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0"
  );
  if (r.rowCount === 1) {
    await db.query("INSERT INTO orders (product_id, customer) VALUES (1, $1)", [customer]);
    return true;
  }
  return false; // sold out
}
Enter fullscreen mode Exit fullscreen mode

The check (stock > 0) and the write happen in one statement. While one buyer's UPDATE holds the row lock, the others wait; when they continue, PostgreSQL re-checks the WHERE against the new value (0), so they match nothing. Check rowCount: 1 means you got the ticket, 0 means sold out.

(For real code, put the UPDATE and the INSERT in one transaction so an order can't fail halfway.)

Fix 2: lock the row with SELECT … FOR UPDATE

When you need more logic between the read and the write (prices, limits, coupons), lock the row first:

async function buy(customer) {
  const c = await db.connect();
  try {
    await c.query("BEGIN");
    const { rows } = await c.query(
      "SELECT stock FROM products WHERE id = 1 FOR UPDATE"
    ); // other buyers WAIT here until we commit
    if (rows[0].stock > 0) {
      await c.query("UPDATE products SET stock = stock - 1 WHERE id = 1");
      await c.query("INSERT INTO orders (product_id, customer) VALUES (1, $1)", [customer]);
      await c.query("COMMIT");
      return true;
    }
    await c.query("ROLLBACK");
    return false;
  } finally {
    c.release();
  }
}
Enter fullscreen mode Exit fullscreen mode

FOR UPDATE locks the row until the transaction ends. Every other buyer blocks at that SELECT, and when they get through, they read the new stock: 0.

Results: same test, fixed

20 concurrent buyers, stock = 1 Orders (3 runs each)
read, then write 1, 20, 19 ❌
atomic UPDATE … WHERE stock > 0 1, 1, 1 ✅
SELECT … FOR UPDATE 1, 1, 1 ✅

Safety net: a CHECK constraint

CREATE TABLE products (
  id    int PRIMARY KEY,
  name  text,
  stock int CHECK (stock >= 0)
);
Enter fullscreen mode Exit fullscreen mode

I re-ran the racy read-then-write version, this time decrementing with stock = stock - 1. One buyer succeeded, and every other one got:

new row for relation "products" violates check constraint "products_stock_check"
Enter fullscreen mode Exit fullscreen mode

An error is much better than a silent oversell. The constraint protects you from code paths you forgot about.

Recap

  • Read → check → write in app code is a race. It passes tests and breaks under real traffic.
  • Fix 1: UPDATE … SET stock = stock - 1 WHERE … AND stock > 0, then check rowCount.
  • Fix 2: SELECT … FOR UPDATE inside a transaction.
  • Safety net: CHECK (stock >= 0).

The same bug hides in bookings, coupon redemptions, account balances, rate limits and "username available?" checks. Where else have you seen it? 👇


I post one tested coding tip every day on YouTube at Refactorly. The 30-second version of this one is here.

Top comments (0)