Designing an Inventory Management System: Stock Reservations, Consistency Under Concurrent Writes, and Oversell Prevention at Scale

Every e-commerce system eventually faces the same problem: two buyers click “Add to Cart” on the last unit at the same moment, and one of them gets an order confirmation for stock that no longer exists. You have oversold.

The naive fix is a simple WHERE quantity > 0 check before decrement. That works at a few hundred orders per day. At a few thousand concurrent sessions during a flash sale, it falls apart immediately. Inventory management at scale is a concurrency problem disguised as a data model problem, and the two have to be solved together.

This article covers the full design: how to model SKUs and warehouses, how to implement soft and hard reservations with TTLs, how to enforce consistency under concurrent writes using optimistic locking and CAS operations, how to prevent oversell without sacrificing availability, and how to handle distributed inventory across multiple fulfillment locations.

The Data Model

Start with the core entities. A catalog item (a product) is separate from a stock-keeping unit (a SKU). A product is “Blue Hoodie”; a SKU is “Blue Hoodie, Size M, Color Heather Gray”. The SKU is the unit of inventory.

interface Product {
  productId: string;
  name: string;
  description: string;
}

interface Sku {
  skuId: string;
  productId: string;
  attributes: Record<string, string>; // { size: "M", color: "Heather Gray" }
  barcode?: string;
}

interface Warehouse {
  warehouseId: string;
  name: string;
  region: string;
  priority: number; // fulfillment priority, lower = preferred
}

interface StockLevel {
  skuId: string;
  warehouseId: string;
  onHand: number;         // physical units in the warehouse
  reserved: number;       // units held by active reservations
  available: number;      // computed: onHand - reserved
  version: number;        // optimistic lock version
  updatedAt: Date;
}

available is always derived, never stored independently, to prevent divergence. Every write that touches onHand or reserved must recompute it. Some teams store available as a computed column in Postgres; others compute it in the application layer. Both work, but the computed column approach eliminates a class of bugs where application code forgets to recalculate.

The version field is the key to optimistic locking, covered below.

Soft vs Hard Reservations

A reservation is a promise to hold stock for a specific buyer. There are two kinds.

A soft reservation holds stock while the buyer is in the checkout flow. It is temporary: if the buyer abandons the cart, the TTL expires and the stock is released back to available. Soft reservations happen frequently and must be cheap to create and cheap to expire.

A hard reservation is created when an order is confirmed and payment is authorized. It persists until the order is shipped (at which point onHand decreases) or cancelled (at which point the reservation is released). Hard reservations represent real business commitments.

type ReservationStatus = "soft" | "hard" | "fulfilled" | "released";

interface Reservation {
  reservationId: string;
  skuId: string;
  warehouseId: string;
  orderId?: string;         // null for soft reservations
  sessionId?: string;       // null for hard reservations
  quantity: number;
  status: ReservationStatus;
  expiresAt: Date;          // TTL for soft reservations; far-future for hard
  createdAt: Date;
  updatedAt: Date;
}

The TTL on soft reservations is the most important parameter you will tune. Too short (30 seconds) and legitimate buyers lose their cart mid-checkout. Too long (60 minutes) and you block available stock during periods of high browse-but-no-buy behavior. Fifteen minutes is a reasonable starting point; adjust based on your observed checkout completion time distribution.

A background job runs every minute to expire soft reservations:

async function expireSoftReservations(db: DatabaseClient): Promise<void> {
  const expired = await db.query<Reservation>(
    `UPDATE reservations
     SET status = 'released', updated_at = NOW()
     WHERE status = 'soft'
       AND expires_at < NOW()
     RETURNING *`
  );

  for (const reservation of expired.rows) {
    await releaseReservation(db, reservation);
  }
}

async function releaseReservation(
  db: DatabaseClient,
  reservation: Reservation
): Promise<void> {
  await db.query(
    `UPDATE stock_levels
     SET reserved = reserved - $1,
         available = on_hand - (reserved - $1),
         version = version + 1,
         updated_at = NOW()
     WHERE sku_id = $2
       AND warehouse_id = $3`,
    [reservation.quantity, reservation.skuId, reservation.warehouseId]
  );
}

Run this expiry job on a schedule, but also trigger it lazily during the reserve path. If a buyer attempts to reserve stock and the available count is zero, check for expired soft reservations before returning an out-of-stock response. That lazy expiry closes the gap between scheduled runs.

Consistency Under Concurrent Writes

This is where most inventory systems either over-engineer (distributed locks for every operation) or under-engineer (no concurrency control at all).

Optimistic Locking

The correct baseline for most systems is optimistic locking with version numbers. The idea: read the current row, compute your update, write the update back with a condition that the version has not changed since you read it. If the version changed, someone else wrote first; retry.

async function createSoftReservation(
  db: DatabaseClient,
  skuId: string,
  warehouseId: string,
  quantity: number,
  sessionId: string,
  ttlMinutes: number
): Promise<Reservation | null> {
  const maxAttempts = 3;

  for (let attempt = 0; attempt < maxAttempts; attempt++) {
    const stock = await db.queryOne<StockLevel>(
      `SELECT * FROM stock_levels WHERE sku_id = $1 AND warehouse_id = $2`,
      [skuId, warehouseId]
    );

    if (!stock || stock.available < quantity) {
      return null; // out of stock
    }

    const expiresAt = new Date(Date.now() + ttlMinutes * 60 * 1000);

    const updated = await db.query(
      `UPDATE stock_levels
       SET reserved = reserved + $1,
           available = on_hand - (reserved + $1),
           version = version + 1,
           updated_at = NOW()
       WHERE sku_id = $2
         AND warehouse_id = $3
         AND version = $4
         AND (on_hand - reserved) >= $1`,
      [quantity, skuId, warehouseId, stock.version]
    );

    if (updated.rowCount === 0) {
      // Version changed or stock dropped between read and write. Retry.
      await sleep(10 * (attempt + 1)); // simple linear backoff
      continue;
    }

    const reservation = await db.queryOne<Reservation>(
      `INSERT INTO reservations
         (reservation_id, sku_id, warehouse_id, session_id, quantity, status, expires_at, created_at, updated_at)
       VALUES ($1, $2, $3, $4, $5, 'soft', $6, NOW(), NOW())
       RETURNING *`,
      [generateId(), skuId, warehouseId, sessionId, quantity, expiresAt]
    );

    return reservation;
  }

  return null; // exhausted retries
}

The critical clause is AND version = $4 AND (on_hand - reserved) >= $1 in the UPDATE. If either condition fails, rowCount is zero and you retry. The availability check is inside the atomic UPDATE, not just the preceding SELECT. This is the oversell prevention guarantee: you cannot decrement available below zero because the database rejects any update where the precondition is false.

CAS Operations and Database-Level Guarantees

Optimistic locking with version numbers is a form of Compare-And-Swap (CAS): “update this row only if the value I read is still current.” Most relational databases give you this with a WHERE clause on the UPDATE statement.

For Redis-backed inventory (common for high-throughput scenarios where the authoritative count lives in Redis), the equivalent is a Lua script:

-- Redis Lua script: atomic check-and-reserve
local key = KEYS[1]
local quantity = tonumber(ARGV[1])

local available = tonumber(redis.call('GET', key))
if available == nil or available < quantity then
  return 0  -- insufficient stock
end

redis.call('DECRBY', key, quantity)
return 1  -- reserved successfully

const luaScript = `
  local key = KEYS[1]
  local quantity = tonumber(ARGV[1])
  local available = tonumber(redis.call('GET', key))
  if available == nil or available < quantity then
    return 0
  end
  redis.call('DECRBY', key, quantity)
  return 1
`;

async function reserveInRedis(
  redis: RedisClient,
  skuId: string,
  quantity: number
): Promise<boolean> {
  const result = await redis.eval(luaScript, 1, `inventory:${skuId}`, quantity);
  return result === 1;
}

Lua scripts execute atomically on a single Redis shard. No other command can interleave between the GET and the DECRBY, which is the guarantee you need for concurrent reservation requests on the same SKU.

Serializable Isolation

For the most critical writes (hard reservations tied to payment authorization), use serializable transaction isolation. Most teams run at READ COMMITTED by default, which allows phantom reads and write skew. At SERIALIZABLE, Postgres uses Serializable Snapshot Isolation (SSI), which detects and aborts transactions that would produce anomalous results.

async function confirmReservation(
  db: DatabaseClient,
  reservationId: string,
  orderId: string
): Promise<void> {
  await db.transaction(async (txn) => {
    await txn.query("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");

    const reservation = await txn.queryOne<Reservation>(
      `SELECT * FROM reservations WHERE reservation_id = $1 FOR UPDATE`,
      [reservationId]
    );

    if (!reservation || reservation.status !== "soft") {
      throw new Error("Reservation not found or already confirmed");
    }

    await txn.query(
      `UPDATE reservations
       SET status = 'hard',
           order_id = $1,
           expires_at = NOW() + INTERVAL '30 days',
           updated_at = NOW()
       WHERE reservation_id = $2`,
      [orderId, reservationId]
    );
  });
}

The FOR UPDATE lock on the SELECT prevents two concurrent confirmation attempts on the same reservation from both succeeding. One gets the lock, the other waits and then sees status !== 'soft' when it runs.

Oversell Prevention Strategies

Oversell prevention is not one mechanism; it is a layered approach.

Layer 1: Availability check in the UPDATE predicate. As shown above, the AND (on_hand - reserved) >= quantity clause in the stock_levels UPDATE is the primary guard. It is atomic and requires no separate lock acquisition.

Layer 2: Database constraints. Add a CHECK constraint so the column can never go negative at the database level:

ALTER TABLE stock_levels
  ADD CONSTRAINT available_non_negative
  CHECK ((on_hand - reserved) >= 0);

ALTER TABLE stock_levels
  ADD CONSTRAINT reserved_non_negative
  CHECK (reserved >= 0);

These constraints are a last-resort protection. If a bug bypasses the application logic, the database rejects the write. You will get an error instead of corrupted data.

Layer 3: Idempotency on reservation creation. If the reservation service retries after a network timeout, it must not create duplicate reservations. Use a client-generated idempotency key and a unique constraint:

ALTER TABLE reservations
  ADD CONSTRAINT unique_session_sku_soft
  UNIQUE (session_id, sku_id, status)
  WHERE status = 'soft';

This partial unique index ensures a session can hold at most one soft reservation per SKU. If the INSERT retries, it fails with a unique violation, and the application returns the existing reservation.

Layer 4: Reconciliation job. Run a daily reconciliation that cross-checks stock_levels.on_hand against physical warehouse counts and stock_levels.reserved against active reservations. Discrepancies trigger alerts. Oversells that slip through any of the above layers show up here before they compound.

Distributed Inventory Across Warehouses

Single-warehouse inventory is a tractable concurrency problem. Multi-warehouse inventory adds a routing layer: which warehouse fills the order?

The simplest model is a priority-ordered list of warehouses per SKU. When a reservation request arrives, query warehouses in priority order and reserve from the first one with sufficient available stock.

async function reserveFromBestWarehouse(
  db: DatabaseClient,
  skuId: string,
  quantity: number,
  sessionId: string
): Promise<Reservation | null> {
  const warehouses = await db.query<{ warehouseId: string }>(
    `SELECT w.warehouse_id
     FROM stock_levels sl
     JOIN warehouses w ON sl.warehouse_id = w.warehouse_id
     WHERE sl.sku_id = $1
       AND (sl.on_hand - sl.reserved) >= $2
     ORDER BY w.priority ASC, (sl.on_hand - sl.reserved) DESC
     LIMIT 5`,
    [skuId, quantity]
  );

  for (const { warehouseId } of warehouses.rows) {
    const reservation = await createSoftReservation(
      db,
      skuId,
      warehouseId,
      quantity,
      sessionId,
      15
    );
    if (reservation) return reservation;
    // Failed (race condition on this warehouse). Try the next.
  }

  return null;
}

The SELECT is advisory, not authoritative. A concurrent request may drain the warehouse between the SELECT and the UPDATE. That is fine: the optimistic locking in createSoftReservation handles it by trying the next warehouse on retry.

For systems that span geographic regions, consider maintaining a separate availability index in a fast store (Redis or a read replica) per region. The authoritative stock_levels table remains the source of truth, but reads for the checkout availability check go to the regional index. This keeps the checkout path fast without sacrificing correctness on writes.

Event-Driven Stock Updates

Inventory changes through more than reservations: stock arrives (purchase orders), gets adjusted for damages, moves between warehouses, and decrements when orders ship. Model each change as an event rather than a direct row mutation. This gives you an audit trail and a clean integration surface for downstream consumers: the catalog service showing “In Stock”, the analytics pipeline, the reorder notification service.

type StockEventType =
  | "receipt"           // stock arrived at warehouse
  | "reservation"       // soft reserve
  | "reservation_confirmed" // soft -> hard
  | "reservation_released"  // reservation expired or cancelled
  | "shipment"          // order shipped, on_hand decremented
  | "adjustment"        // manual correction
  | "transfer_out"      // moved to another warehouse
  | "transfer_in";      // received from another warehouse

interface StockEvent {
  eventId: string;
  skuId: string;
  warehouseId: string;
  type: StockEventType;
  quantityDelta: number;  // positive = increase, negative = decrease
  referenceId: string;    // orderId, poId, adjustmentId
  occurredAt: Date;
  metadata?: Record<string, unknown>;
}

Write events and update stock_levels in the same transaction. The events table becomes the replay source if stock_levels ever needs to be rebuilt, and it feeds downstream consumers via CDC or a message queue.

async function recordShipment(
  db: DatabaseClient,
  skuId: string,
  warehouseId: string,
  orderId: string,
  quantity: number
): Promise<void> {
  await db.transaction(async (txn) => {
    // Release the hard reservation
    const reservation = await txn.queryOne<Reservation>(
      `UPDATE reservations
       SET status = 'fulfilled', updated_at = NOW()
       WHERE sku_id = $1
         AND warehouse_id = $2
         AND order_id = $3
         AND status = 'hard'
       RETURNING *`,
      [skuId, warehouseId, orderId]
    );

    if (!reservation) throw new Error("No hard reservation found for shipment");

    // Decrement on_hand and reserved together
    await txn.query(
      `UPDATE stock_levels
       SET on_hand = on_hand - $1,
           reserved = reserved - $1,
           available = (on_hand - $1) - (reserved - $1),
           version = version + 1,
           updated_at = NOW()
       WHERE sku_id = $2
         AND warehouse_id = $3`,
      [quantity, skuId, warehouseId]
    );

    // Append the event
    await txn.query(
      `INSERT INTO stock_events
         (event_id, sku_id, warehouse_id, type, quantity_delta, reference_id, occurred_at)
       VALUES ($1, $2, $3, 'shipment', $4, $5, NOW())`,
      [generateId(), skuId, warehouseId, -quantity, orderId]
    );
  });
}

Consistency vs Availability Tradeoffs

The hardest design decision in inventory systems is how much consistency you actually need. Strong consistency (every read sees the latest write) is expensive: it means serializable isolation, lock contention at peak load, and degraded throughput during traffic spikes. Eventual consistency is cheaper but introduces windows where two buyers see the same “1 unit available” count and both successfully reserve.

Strategy	Consistency	Throughput	Oversell Risk	Best For
Serializable isolation	Strong	Low	None	Payment-adjacent writes, small catalogs
Optimistic locking (READ COMMITTED)	Read-committed	High	Very low	Most SKU reservation writes
Redis Lua + async DB sync	Eventual	Very high	Low (window: sync lag)	Flash sales, limited-quantity drops
Oversell buffer	Eventual	Very high	Managed (intentional buffer)	High-volume commodities
Per-SKU advisory lock	Strong (serialized)	Medium	None	Low-volume, high-value items

The oversell buffer strategy is worth naming explicitly. For high-volume, low-margin commodities, some teams intentionally set available = on_hand - buffer where buffer is a small positive number (2-5 units). This means the system shows out-of-stock before the physical stock is actually exhausted, creating a safety margin that absorbs race conditions. The buffer size is a business decision: too large and you strand revenue, too small and you still oversell.

For a flash sale on limited-edition items (100 units, 50,000 concurrent sessions), the right architecture is a Redis counter with a Lua-based decrement fronting the authoritative Postgres row. Reservations go into a queue; the queue consumer writes hard reservations to Postgres asynchronously. The Redis counter is the gate; Postgres is the record. The two stay in sync through the queue. This decouples the high-concurrency reservation path from the relational write path.

Production Considerations

Index on the hot path. The query WHERE sku_id = $1 AND warehouse_id = $2 needs a composite index on (sku_id, warehouse_id) on both stock_levels and reservations. Missing indexes under load cause sequential scans and lock queue buildup.

Warm up the connection pool before flash sales. Connection pool exhaustion under sudden load causes cascading timeouts and retry storms that surface as oversells. Pre-warm to your expected peak connection count before the sale window opens.

Track reservation conversion rate. The ratio of soft reservations that convert to hard reservations is a leading indicator of checkout friction. A drop during a sale often means buyers are hitting “out of stock” after reserving: your TTL is too short or your expiry job is too aggressive.

Monitor for negative available counts. Even with CHECK constraints, query for available < 0 in your reconciliation job. If you see it, something bypassed the constraint and you want to find it before it compounds.

Warehouse routing must account for shipping cost. Priority ordering by warehouse is not only about availability; it is about which warehouse minimizes transit time and cost to the buyer’s address. Feed geographic routing signals into the ordering, or you will consistently fulfill from the wrong warehouse and erode margins.

The architecture layers are: data model > reservation lifecycle > concurrency control > multi-warehouse routing > event stream > consistency policy. Start with optimistic locking at READ COMMITTED. Add Redis fronting when individual SKU write throughput consistently saturates what Postgres can handle without lock contention. That upgrade point is measurable: watch for lock wait time on stock_levels during peak load.