Optimistic vs. Pessimistic Locking in PostgreSQL & Node.js: Preventing Inventory Overselling

You launch a sale. There are 10 units of a hot item in stock. Fifteen people click "Buy" within the same second. When the dust settles, your database says:

stock = -2
orders confirmed = 12

Two customers are getting an apology email. Support is unhappy, and finance wants to know how the stock count went negative.

This is the lost update problem, and it's one of the classic concurrency bugs in backend code. It doesn't show up in development (one user, one request at a time), it passes unit tests, and then it appears the first time real concurrent traffic hits the same row.

In this post we'll reproduce the bug, then fix it three ways with PostgreSQL and Node.js:

1. Atomic conditional updates (often all you need)
2. Pessimistic locking (SELECT ... FOR UPDATE)
3. Optimistic locking (version checks with retries)

We'll compare them honestly, because the right choice depends on your contention level.

---

The bug: read, check, write

Here's the code almost everyone writes first:

// ❌ Race condition
async function purchase(productId: number, qty: number) {
  const { rows } = await pool.query('SELECT stock FROM products WHERE id = $1', [productId]);
  const stock = rows[0].stock;

  if (stock < qty) throw new OutOfStockError();

  await pool.query('UPDATE products SET stock = $1 WHERE id = $2', [stock - qty, productId]);
  await pool.query('INSERT INTO orders (product_id, qty) VALUES ($1, $2)', [productId, qty]);
}

Now two requests run at the same time with stock = 1:

Request A                          Request B
SELECT stock  → 1
                                   SELECT stock  → 1
check 1 >= 1  ✓
                                   check 1 >= 1  ✓
UPDATE stock = 0
                                   UPDATE stock = 0     ← overwrites A; both think they got the last unit
INSERT order                       INSERT order         ← two orders, one unit

Both requests read the same value, both pass the check, and the second write silently clobbers the first. The gap between the read and the write is the race window. Every fix below is a different way of closing it.

Prove it before you fix it

Make this a test you keep forever. If you can't reproduce the bug, you can't trust the fix:

// oversell.test.ts
import { it, expect, beforeEach } from 'vitest';

beforeEach(async () => {
  await pool.query('TRUNCATE orders RESTART IDENTITY');
  await pool.query("UPDATE products SET stock = 10 WHERE id = 1");
});

it('never sells more than the available stock', async () => {
  // 50 buyers fight over 10 units
  const results = await Promise.allSettled(
    Array.from({ length: 50 }, () => purchase(1, 1)),
  );

  const sold = results.filter((r) => r.status === 'fulfilled').length;
  const { rows: [{ stock }] } = await pool.query('SELECT stock FROM products WHERE id = 1');
  const { rows: [{ count }] } = await pool.query('SELECT count(*)::int AS count FROM orders');

  expect(sold).toBe(10);
  expect(count).toBe(10);
  expect(stock).toBe(0);
});

Run it against the naive version and it will fail. Use a pool larger than 1 so the requests genuinely interleave. (Timing-dependent tests can occasionally pass by luck, so run it several times, or raise the buyer count.)

---

Fix 0: Let the database do it atomically

Before reaching for locks, consider the simplest fix: move the check into the write.

// ✅ Check and decrement in one atomic statement
async function purchase(productId: number, qty: number) {
  return withTransaction(async (tx) => {
    const res = await tx.query(
      `UPDATE products
          SET stock = stock - $1
        WHERE id = $2 AND stock >= $1`,
      [qty, productId],
    );

    if (res.rowCount === 0) throw new OutOfStockError(); // not enough stock (or no such product)

    await tx.query('INSERT INTO orders (product_id, qty) VALUES ($1, $2)', [productId, qty]);
  });
}

Why this works: a single UPDATE takes a row-level lock on the row it modifies. If a second transaction tries to update the same row, it waits for the first to commit or roll back. Under PostgreSQL's default READ COMMITTED isolation level, once the lock is released, the waiting statement re-checks its WHERE clause against the newly committed row. So request B re-evaluates stock >= 1 against the value A left behind (0) and updates nothing.

Request A                          Request B
UPDATE ... WHERE stock >= 1  → locks row, stock 1 → 0
                                   UPDATE ... WHERE stock >= 1  → waits on A's lock
COMMIT                             → re-checks: stock is 0, no match → rowCount = 0 → OutOfStock

Two things make this solid:

• The decrement is relative (stock = stock - $1), not an absolute value computed in the app. That's what avoids the lost update.
• The guard is part of the same statement, so there's no window between check and write.

Add a final safety net at the schema level so a future bug can never produce negative stock:

ALTER TABLE products ADD CONSTRAINT stock_non_negative CHECK (stock >= 0);

For many inventory systems, this is the whole answer. Locks, versions, and retries are for the cases where the logic between read and write is too complex to fit into a single UPDATE: pricing rules, multi-step validation, reservations that depend on other rows.

---

Fix 1: Pessimistic locking (SELECT ... FOR UPDATE)

Pessimistic locking assumes conflicts are likely, so it locks the row up front, before reading. Anyone else who wants the row waits.

async function purchase(productId: number, qty: number) {
  return withTransaction(async (tx) => {
    // Lock the row; concurrent transactions block here until we commit/rollback
    const { rows } = await tx.query(
      'SELECT stock, price_cents FROM products WHERE id = $1 FOR UPDATE',
      [productId],
    );
    if (rows.length === 0) throw new NotFoundError();

    const { stock, price_cents } = rows[0];

    // Arbitrary application logic is now safe: nobody can change this row under us
    if (stock < qty) throw new OutOfStockError();
    const total = computeTotalWithPromotions(price_cents, qty);

    await tx.query('UPDATE products SET stock = stock - $1 WHERE id = $2', [qty, productId]);
    await tx.query(
      'INSERT INTO orders (product_id, qty, total_cents) VALUES ($1, $2, $3)',
      [productId, qty, total],
    );
  });
}

withTransaction is the helper from earlier posts in this series: it acquires a client, issues BEGIN, runs your function, commits or rolls back, and always releases the connection.

Now the read, the check, and the write are one critical section. This is the right tool when the logic between read and write can't be expressed as a single SQL statement.

Pessimistic locking variants worth knowing

-- Fail immediately if someone else holds the lock, rather than waiting
SELECT ... FROM products WHERE id = $1 FOR UPDATE NOWAIT;      -- error 55P03 (lock_not_available)

-- Skip locked rows (great for job queues and "claim the next available item")
SELECT ... FROM tickets WHERE status = 'free' LIMIT 1 FOR UPDATE SKIP LOCKED;

You can also bound waiting with SET LOCAL lock_timeout = '2s' inside the transaction, so a stuck lock turns into a fast error instead of a pile-up.

The costs of pessimistic locking

1. Contention serializes your throughput. Every buyer of the same product queues behind the lock holder. For a flash-sale SKU, that's one-at-a-time processing.
2. Long transactions are dangerous. The lock is held until COMMIT. If you call a payment API, send an email, or do any slow work while holding it, everything else waits, and you'll also pin a connection from your pool the whole time (see our post on pool starvation). Keep locked transactions short, and never do network I/O inside them.
3. Deadlocks. If two transactions lock rows in opposite orders, Postgres detects the cycle and aborts one with error 40P01.

Avoiding deadlocks in multi-item orders

A cart with several products locks several rows. If request A locks product 1 then 2, while request B locks 2 then 1, they deadlock. The fix is simple and effective: always acquire locks in a consistent order.

async function purchaseCart(items: { productId: number; qty: number }[]) {
  // Sort so every transaction locks rows in the same order
  const ordered = [...items].sort((a, b) => a.productId - b.productId);

  return withTransaction(async (tx) => {
    for (const { productId, qty } of ordered) {
      const res = await tx.query(
        'UPDATE products SET stock = stock - $1 WHERE id = $2 AND stock >= $1',
        [qty, productId],
      );
      if (res.rowCount === 0) throw new OutOfStockError(productId); // rolls back ALL items
    }
    await tx.query('INSERT INTO orders ...');
  });
}

Notice this uses the atomic pattern from Fix 0 plus sorted ordering, and the transaction guarantees all-or-nothing: if the third item is out of stock, the first two decrements are rolled back.

Even with consistent ordering, wrap your transactions in a retry for 40P01 (deadlock detected) and 40001 (serialization failure). They're rare, but they're expected, not exceptional.

---

Fix 2: Optimistic locking (version column + retry)

Optimistic locking assumes conflicts are rare, so it doesn't lock anything while you think. Instead it detects a conflict at write time and makes you try again.

Add a version column:

ALTER TABLE products ADD COLUMN version INTEGER NOT NULL DEFAULT 0;

Read the row and its version without locking. When writing, include the version you saw in the WHERE clause and bump it:

async function purchaseOptimistic(productId: number, qty: number) {
  const { rows } = await pool.query(
    'SELECT stock, version FROM products WHERE id = $1',
    [productId],
  );
  const { stock, version } = rows[0];

  if (stock < qty) throw new OutOfStockError();

  const res = await pool.query(
    `UPDATE products
        SET stock = $1, version = version + 1
      WHERE id = $2 AND version = $3`,
    [stock - qty, productId, version],
  );

  if (res.rowCount === 0) throw new ConflictError(); // someone else changed the row since we read it
  // ...create order (in the same transaction in real code)
}

If another request modified the row between our read and write, its version bump means our WHERE version = $3 matches nothing, rowCount is 0, and we know our view of the world was stale. The caller then retries from the top with fresh data.

The retry loop is not optional

async function withRetry<T>(fn: () => Promise<T>, maxAttempts = 5): Promise<T> {
  for (let attempt = 1; ; attempt++) {
    try {
      return await fn();
    } catch (err: any) {
      const retryable =
        err instanceof ConflictError ||
        err.code === '40001' ||   // serialization_failure
        err.code === '40P01';     // deadlock_detected
      if (!retryable || attempt >= maxAttempts) throw err;

      // Exponential backoff with full jitter, so colliding requests spread out
      const delay = Math.random() * Math.min(200, 10 * 2 ** attempt);
      await new Promise((r) => setTimeout(r, delay));
    }
  }
}

await withRetry(() => purchaseOptimistic(1, 1));

Cap the attempts, and add jitter. Without jitter, colliding requests retry in lockstep and collide again.

When optimistic locking shines (and when it doesn't)

Good fit:

• Low contention: collisions are occasional (many different products, each rarely bought concurrently)
• Long "think time" between read and write, where holding a lock would be unacceptable. For example, a user editing a record in a UI: send the version with the form and reject a stale save (the HTTP version of this is ETag / If-Match)
• You don't want to hold database locks or connections while waiting on slow things

Poor fit:

• Hot rows. If 500 people want the same last 10 units, most attempts fail and retry, burning CPU and database round trips on work that gets thrown away. Throughput can collapse under heavy contention.
• Anything where retrying isn't safe unless you've made the surrounding side effects idempotent (don't charge a card inside a retried block)

---

Comparing the approaches

	Atomic conditional UPDATE	Pessimistic (FOR UPDATE)	Optimistic (version + retry)
Locks held	Only during the statement	From the read until commit	None while reading
Handles complex logic between read and write	No (must fit in SQL)	Yes	Yes
Behavior under contention	Queues briefly, stays correct	Queues (serialized)	Retries (can thrash)
Failure mode	Out-of-stock (rowCount 0)	Waiting, timeouts, deadlocks	Conflicts, retry storms
Retry logic needed	No	Only for deadlock/timeout	Yes
Best for	Counters, stock decrements	High contention, multi-step logic	Low contention, long think time
Complexity	Lowest	Medium	Highest

A practical decision guide:

1. Can the rule be expressed as UPDATE ... SET x = x - n WHERE x >= n? Do that. It's the simplest and handles contention well.
2. Is the logic complex and contention likely (flash sales, popular SKUs)? Pessimistic, with short transactions and ordered locking.
3. Is contention rare, or is there a long gap between read and write (user edits, multi-step approvals)? Optimistic.

---

A word on isolation levels

PostgreSQL defaults to READ COMMITTED, and everything above is designed to be correct there. You can also raise the isolation level:

await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');

At REPEATABLE READ and SERIALIZABLE, PostgreSQL detects conflicting concurrent updates and aborts one transaction with 40001 rather than letting it proceed on stale data. That gives you correctness without hand-placed locks, but you must catch 40001 and retry the whole transaction. It's the same retry discipline as optimistic locking, enforced by the database. The trade-off is more aborted transactions under contention, so test with realistic concurrency before adopting it broadly.

---

When one row is too hot: scaling past a single counter

If a single product row is the center of a stampede, even the best-behaved locking serializes on it. Options when that becomes your bottleneck:

• Reservations with expiry. Decrement stock into a reserved count when a user enters checkout, with a reserved_until timestamp. Release expired reservations with a background job. Also keeps the lock window short and avoids selling stock to people who never finish paying.
• An append-only inventory ledger. Record +N / -N movements and derive stock by summing, with periodic roll-ups. Inserts don't contend on one row, and you get an audit trail for free.
• Sharded counters. Split stock for a hot SKU across several rows (for example, 10 buckets) and have each purchase pick one bucket. It's more complex to read the total and to handle "bucket empty, try another."
• Serialize in a queue. Route all purchases for a hot SKU through a single consumer (for example, a queue partitioned by product ID). The queue gives you ordering and backpressure, and the database sees no contention.
• Advisory locks. pg_advisory_xact_lock(key) lets you lock a logical resource that isn't a single row. Useful, but it's still a lock, with the same hold-time and deadlock considerations.

Reach for these only when measurements say the simple approaches aren't enough.

---

Don't forget idempotency

Network retries and double-clicks mean the same purchase may arrive twice. Locking prevents overselling, but not double-ordering by one customer. Add an idempotency key with a unique constraint:

ALTER TABLE orders ADD COLUMN idempotency_key TEXT;
CREATE UNIQUE INDEX orders_idem_idx ON orders (idempotency_key);

const res = await tx.query(
  `INSERT INTO orders (product_id, qty, idempotency_key)
   VALUES ($1, $2, $3)
   ON CONFLICT (idempotency_key) DO NOTHING`,
  [productId, qty, key],
);
if (res.rowCount === 0) {
  // Already processed. Return the existing order instead of decrementing stock again.
}

Insert the order (and check the conflict) before decrementing stock, inside the same transaction, so a duplicate request rolls back cleanly without touching inventory.

---

Checklist

• ✅ Use relative updates (stock = stock - n), never write back a value computed from an earlier read
• ✅ Put the guard in the WHERE clause (AND stock >= n) and check rowCount
• ✅ Add CHECK (stock >= 0) as a last line of defense
• ✅ Keep locking transactions short, with no HTTP calls or slow work inside
• ✅ Lock multiple rows in a consistent order
• ✅ Retry on 40001 and 40P01, with a cap, backoff, and jitter
• ✅ Make order creation idempotent
• ✅ Keep a concurrency test (N buyers, M units) in CI

Key takeaways

1. Read-then-write races oversell inventory. The gap between the check and the update is the bug.
2. The simplest fix is usually an atomic conditional UPDATE, with the check and the decrement in one statement.
3. Pessimistic locking (FOR UPDATE) serializes access up front. Use it for complex logic under high contention, but keep transactions short and lock in a consistent order.
4. Optimistic locking (version column) detects conflicts at write time. It's great under low contention, and it needs a bounded retry loop with jitter.
5. Pick based on contention and logic complexity, not preference. Hot rows punish optimistic locking, and rare conflicts make pessimistic locking needless overhead.
6. Test with real concurrency. A test that fires 50 buyers at 10 units will catch what code review won't.

How do you handle hot SKUs in your system? Share your approach in the comments.

---

Further reading: the PostgreSQL docs on explicit locking and transaction isolation.