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;
}
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))
);
I reset the table and ran this 10 times. Orders created per run:
1 19 19 20 20 19 20 20 19 20
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
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
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
}
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();
}
}
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)
);
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"
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 checkrowCount. -
Fix 2:
SELECT … FOR UPDATEinside 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)