Stop Overselling: Stock Reservation Inside the Order Transaction

Overselling happens when two orders read the same stock count and both decide there is enough. Stop it by decrementing stock inside the order transaction with a conditional update, SET stock = stock - $2 WHERE id = $1 AND stock >= $2, or by locking the row with SELECT ... FOR UPDATE first. If no row updates, the item is out of stock and the order rolls back.

This guide is for teams running their own store or marketplace on PostgreSQL. The examples use PostgreSQL and node-pg, and every database behaviour quoted comes from the PostgreSQL documentation. The same ideas apply to any database with row locks and transactions.

Why does my store sell stock it doesn't have?

Because the check and the write are two separate steps, and another order can slip in between them. This is a race condition: the result depends on timing. With one unit left and two customers checking out at the same moment, a read-then-write flow does this:

Step	Order A	Order B	Stock in the database
1	Reads stock: 1. Enough.		1
2		Reads stock: 1. Enough.	1
3	Writes stock = 0, creates order		0
4		Writes stock = 0, creates order	0, with two orders for one unit

Nothing in that flow throws an error. Both customers get a confirmation, and the problem shows up days later in the warehouse. It gets more likely under exactly the conditions you care about: a sale, a popular product, a flash promotion.

Checking stock in the browser, or in an earlier request, does not help. The check has to happen in the same statement or transaction as the write.

How does a conditional UPDATE prevent overselling?

It makes the check and the write one statement. The database only decrements the row if there is enough stock, and tells you how many rows it changed.

UPDATE product_variants
SET stock = stock - $2
WHERE id = $1
  AND stock >= $2
RETURNING stock, price_minor;

If the statement updates one row, the units are yours. If it updates none, there was not enough stock. PostgreSQL's UPDATE reports "the number of rows updated", and RETURNING returns values "based on each row actually updated" (PostgreSQL: UPDATE).

Why is this safe when two orders run it at once? Under PostgreSQL's default isolation level, Read Committed, the second update waits for the first to commit, then "the search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition" (PostgreSQL: transaction isolation). In the race above, Order B's update waits, sees stock of 0, no longer matches stock >= 1, and updates nothing.

When should you use SELECT ... FOR UPDATE instead?

When you need to read the row, apply rules in application code, and then decide. For example: allow back-orders for some products, cap quantity per customer, or show the exact number left in the error message.

BEGIN;
SELECT stock, allow_backorder
FROM product_variants
WHERE id = $1
FOR UPDATE;

-- Application code decides. If the order can go ahead:
UPDATE product_variants SET stock = stock - $2 WHERE id = $1;
COMMIT;

PostgreSQL's documentation says FOR UPDATE locks the rows "as though for update", which "prevents them from being locked, modified or deleted by other transactions until the current transaction ends" (PostgreSQL: explicit locking). A second order for the same variant waits at its own SELECT ... FOR UPDATE and then reads the updated stock.

Approach	Round trips per line	Holds the lock	Use it when
Conditional UPDATE ... WHERE stock >= $qty	1	From the update until commit	The rule is simply "enough stock or not"
SELECT ... FOR UPDATE, then UPDATE	2	From the select until commit	Application rules decide whether the order can go ahead
Read, then write, no lock	2	Nothing	Never, for stock or money

If you would rather fail fast than wait, add NOWAIT: the statement "reports an error, rather than waiting, if a selected row cannot be locked immediately". Avoid SKIP LOCKED for stock; PostgreSQL notes that skipping locked rows "provides an inconsistent view of the data, so this is not suitable for general purpose work" (PostgreSQL: SELECT).

Should you add a database constraint as well?

Yes, as a floor under your code. A CHECK constraint makes negative stock impossible even if a future code path forgets the condition. PostgreSQL rejects the write: "If a user attempts to store data in a column that would violate a constraint, an error is raised" (PostgreSQL: constraints).

ALTER TABLE product_variants
  ADD CONSTRAINT stock_not_negative CHECK (stock >= 0);

Keep stock per variant, not per product. A t-shirt with 10 units in total can still be sold out in medium, and a per-product count would let you sell mediums you do not have.

What does the full order transaction look like?

Decrement every line, create the order from prices read in the same statements, and commit once. A minimal TypeScript sketch with node-pg:

import { Pool } from 'pg'

const pool = new Pool()

type Line = { variantId: number; qty: number }

export class OutOfStock extends Error {
  constructor(public variantId: number) {
    super('Out of stock: variant ' + variantId)
  }
}

export async function placeOrder(customerId: number, lines: Line[]) {
  // Merge duplicate variants, then sort so every order locks rows in the same order.
  const merged = new Map<number, number>()
  for (const l of lines) merged.set(l.variantId, (merged.get(l.variantId) ?? 0) + l.qty)
  const sorted = [...merged].sort(([a], [b]) => a - b)

  const client = await pool.connect()
  try {
    await client.query('BEGIN')
    const priced: { variantId: number; qty: number; priceMinor: number }[] = []
    for (const [variantId, qty] of sorted) {
      const r = await client.query(
        'UPDATE product_variants SET stock = stock - $2 WHERE id = $1 AND stock >= $2 RETURNING price_minor',
        [variantId, qty],
      )
      if (r.rowCount === 0) throw new OutOfStock(variantId)
      // Price comes from the database, never from the browser.
      priced.push({ variantId, qty, priceMinor: Number(r.rows[0].price_minor) })
    }
    const total = priced.reduce((sum, p) => sum + p.priceMinor * p.qty, 0)
    const order = await client.query(
      'INSERT INTO orders (customer_id, total_minor, status) VALUES ($1, $2, $3) RETURNING id',
      [customerId, total, 'placed'],
    )
    for (const p of priced) {
      await client.query(
        'INSERT INTO order_lines (order_id, variant_id, qty, price_minor) VALUES ($1, $2, $3, $4)',
        [order.rows[0].id, p.variantId, p.qty, p.priceMinor],
      )
    }
    await client.query('COMMIT')
    return order.rows[0].id as number
  } catch (err) {
    await client.query('ROLLBACK')
    throw err
  } finally {
    client.release()
  }
}

Two details carry most of the weight. If any line is out of stock, the whole transaction rolls back, so no earlier line stays decremented. And emails, SMS and analytics events are sent after the commit, never inside the transaction, so a slow provider cannot hold stock rows locked.

Why sort the lines?

To avoid deadlocks. If Order A locks variant 7 and then wants variant 3, while Order B has locked 3 and wants 7, each waits for the other. PostgreSQL detects this and aborts one of them. Its documentation's advice is to acquire locks on multiple objects in a consistent order, and otherwise to retry transactions that abort because of a deadlock (PostgreSQL: explicit locking). Sorting by variant ID gives every order the same lock order. A retry on deadlock errors is still worth having as a safety net.

When should stock be reserved: at checkout, payment or order?

It depends on how customers pay. The trade-off is between overselling risk and stock sitting locked in abandoned checkouts.

When stock is taken	Oversell risk	Cost	Fits
When the order is placed	None, if done in the order transaction	None extra	Cash on delivery, or payment taken before the order is created
When payment starts, with an expiry	None	Stock held for abandoned payments until the hold expires; needs a release job	Redirect payments such as wallets and hosted card pages, where the customer leaves your site
Only after payment is confirmed	Real: the customer may pay for something already gone	Refunds and apologies	Products that rarely sell out

For a hold, record the reservation in its own table with an expires_at, decrement stock in the same transaction, and have a scheduled job return expired reservations to stock with a single UPDATE. If payment is confirmed after the hold expired, check stock again before shipping.

A cache such as Redis can show "only 2 left" quickly on product pages, but the decision to sell must be made by the database transaction. A cache that is a few seconds stale is fine for display and wrong for checkout.

How do you prove the fix works?

With a concurrency test against a real PostgreSQL database, not a mock. Set stock to 1, place two orders at once, and assert that exactly one succeeds.

it('sells the last unit exactly once', async () => {
  await setStock(variantId, 1)
  const results = await Promise.allSettled([
    placeOrder(customerA, [{ variantId, qty: 1 }]),
    placeOrder(customerB, [{ variantId, qty: 1 }]),
  ])
  expect(results.filter((r) => r.status === 'fulfilled')).toHaveLength(1)
  expect(results.filter((r) => r.status === 'rejected')).toHaveLength(1)
  expect(await getStock(variantId)).toBe(0)
})

Run the same test with the check removed, and it should fail at least some of the time. That is how you know the test measures the race and not luck.

How does TheSkinProof prevent overselling?

TheSkinProof is the founder's own venture, a verified-skincare marketplace built and run by RAITHub, not a client project, so read it as engineering evidence rather than a client reference. It decrements per-variant inventory inside the order transaction, so the stock write and the order commit or fail together. Money and stock writes are transaction-guarded with row locks and floors, prices are computed on the server, and post-commit side effects run after the transaction so they never block it. The platform has 217 API endpoints, 5 role-based portals and 750+ automated tests. The full build is on the TheSkinProof case study.

Stock rules get harder in wholesale, where batches and expiry dates decide which units ship first. B2B ecommerce platform development covers that case, including first-expiring, first-out (FEFO) allocation.

Why RAITHub for this

• Stock and money correctness in production. TheSkinProof, the founder's own venture, decrements per-variant stock inside the order transaction, the approach this post describes, across 4 payment rails.
• Concurrency tested, not assumed. Race conditions get tests against a real database, gated in CI.
• Fixed scope, your code. A free 15-minute technical audit, then a written fixed quote. You own the code; an NDA is standard. See the API and backend development service.

When you don't need us

• You sell on a hosted platform. Shopify and similar platforms manage stock for you; configure their inventory settings instead. Shopify's limitations explains when that stops being enough.
• Your backend developer can apply the pattern above. A conditional update, a CHECK constraint and a concurrency test are a small change in a codebase you know.

For the wider build, see custom ecommerce and marketplace development and the ecommerce industry page. If your store is already overselling, book the free 15-minute technical audit with your order flow and database.

Last reviewed: 29 September 2026. PostgreSQL documentation checked on 29 September 2026.

Frequently asked questions

How do I prevent overselling inventory?

Decrement stock inside the same database transaction that creates the order, with a conditional update such as UPDATE ... SET stock = stock - $2 WHERE id = $1 AND stock >= $2. If no row is updated, reject the order and roll back.

Is SELECT FOR UPDATE needed to prevent overselling?

Not always. A conditional UPDATE is enough when the rule is simply "enough stock or not". Use SELECT ... FOR UPDATE when application code has to read the row and decide, for example for back-orders or per-customer limits.

Why does checking stock before the order not work?

Because another order can buy the same unit between your check and your write. The check and the decrement must happen in one statement, or in one transaction with the row locked.

How do I avoid deadlocks when an order has several products?

Lock the rows in a consistent order, for example by sorting order lines by variant ID before updating them, and retry a transaction that PostgreSQL aborts because of a deadlock.

Should I reserve stock when the customer starts paying?

For redirect payments such as mobile wallets or hosted card pages, yes, with an expiry and a job that returns expired holds to stock. For cash on delivery, decrementing when the order is placed is enough.

Can I use Redis to track stock?

For display, yes. The decision to sell should be made by the database transaction, because a cache can be stale by a few seconds, which is enough to oversell during a sale.