Preventing overselling: a practical guide for developers

Imagine it's Black Friday: your inventory dashboard shows one item left in stock, but two customers are both completing checkout at exactly the same time. Without the right safeguards, you'll ship nothing to one of them—or worse, create a backorder nightmare you didn't plan for. Preventing overselling: a practical guide for developers means treating inventory as a critical state machine, not a simple counter. We'll walk through the battle-tested patterns that keep your stock accurate and your customers happy.

Why overselling happens (and why it matters)

Most developers think inventory is straightforward: check the count, subtract one, done. But the moment you introduce concurrency—multiple requests hitting your checkout simultaneously—that logic breaks down. A race condition between two read-check-write cycles can both see the same remaining count and both proceed.

In e-commerce, overselling doesn't just frustrate customers; it erodes trust and multiplies support costs. In ride-hailing or SaaS, it means double-booking resources. In travel, it creates legal compliance issues. The financial cost is real: a single oversold item might spawn refunds, expedited shipping, vendor penalties, or reputational damage.

The remedy isn't a simple band-aid; it's a layered strategy combining reservation logic, atomic operations, and honest communication with the customer.

Reserve stock at checkout with an expiry

The first line of defense is a reservation system. Rather than immediately decrementing inventory when a customer clicks "Buy," we create a temporary hold. This hold exists for a limited time—typically 10–30 minutes—and gives the customer a window to complete payment without losing the item.

Consider an e-commerce scenario: a customer adds a limited-edition sneaker to their cart. We create a reservation record that says "Customer ABC holds 1 unit until 14:45 UTC." If they don't pay by then, we release it. If they do pay, we convert the reservation into a final sale.

Here's a typical schema:

interface StockReservation {
  id: string;
  productId: string;
  customerId: string;
  quantity: number;
  reservedAt: Date;
  expiresAt: Date;  // e.g., reservedAt + 15 minutes
  status: 'active' | 'confirmed' | 'released';
  orderId?: string; // populated on conversion
}

In PostgreSQL, this might look like:

CREATE TABLE stock_reservations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id UUID NOT NULL,
  customer_id UUID NOT NULL,
  quantity INT NOT NULL CHECK (quantity > 0),
  reserved_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at TIMESTAMPTZ NOT NULL,
  status TEXT NOT NULL DEFAULT 'active',
  order_id UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE INDEX idx_reservations_expiry ON stock_reservations(expires_at)
WHERE status = 'active';

When creating a reservation, check available stock (accounting for existing active reservations), then insert the reservation record. On the application side, you'll run a background job every few minutes to clean up expired reservations and release those holds back to available inventory.

Use atomic decrements or row locks

Once payment is confirmed and a reservation is about to be converted, you must decrement the actual inventory count in a way that is atomic—that is, indivisible and collision-proof. Two concurrent requests must not both succeed if only one unit remains.

The most reliable approach is an atomic decrement operation paired with a constraint. In SQL, this typically means:

// Atomic decrement with row-level lock
UPDATE inventory 
SET quantity = quantity - ?
WHERE product_id = ? 
  AND quantity >= ?
RETURNING quantity;

The quantity >= ? clause is critical: it ensures the update only succeeds if enough stock exists after the decrement. If the update affects 0 rows, the decrement failed—you're out of stock.

In practice, wrap this in a transaction with explicit locking:

async function convertReservationToSale(
  reservationId: string,
  quantity: number
): Promise<boolean> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');

    // Lock the inventory row
    const lockResult = await client.query(
      `SELECT quantity FROM inventory 
       WHERE product_id = (
         SELECT product_id FROM stock_reservations 
         WHERE id = $1
       ) FOR UPDATE`,
      [reservationId]
    );

    if (!lockResult.rows.length) {
      await client.query('ROLLBACK');
      return false;
    }

    const currentQuantity = lockResult.rows[0].quantity;
    if (currentQuantity < quantity) {
      await client.query('ROLLBACK');
      return false;
    }

    // Atomic decrement
    await client.query(
      `UPDATE inventory SET quantity = quantity - $1 
       WHERE product_id = (
         SELECT product_id FROM stock_reservations 
         WHERE id = $2
       )`,
      [quantity, reservationId]
    );

    // Mark reservation as confirmed
    await client.query(
      `UPDATE stock_reservations 
       SET status = 'confirmed', order_id = $1 
       WHERE id = $2`,
      [orderId, reservationId]
    );

    await client.query('COMMIT');
    return true;
  } catch (error) {
    await client.query('ROLLBACK');
    console.error('Reservation conversion failed:', error);
    return false;
  } finally {
    client.release();
  }
}

The key elements here are the FOR UPDATE lock (which prevents other transactions from modifying that row), the SERIALIZABLE isolation level (which eliminates phantom reads), and the atomic update with a guard clause. If concurrent requests both attempt this, one will block, the other will succeed, and the loser will see quantity insufficient.

Release reservations on timeout or failure

Reservations that expire must be released promptly so the inventory becomes available again. Otherwise, your dashboard will show stock as available, but the database still holds phantom reservations, and you'll oversell.

Implement a background job that runs every few minutes:

async function releaseExpiredReservations(): Promise<number> {
  const result = await pool.query(
    `UPDATE stock_reservations
     SET status = 'released'
     WHERE status = 'active' 
       AND expires_at < NOW()
     RETURNING id`
  );
  
  console.log(`Released ${result.rowCount} expired reservations`);
  return result.rowCount;
}

// Run every 5 minutes
setInterval(releaseExpiredReservations, 5 * 60 * 1000);

Also release reservations explicitly if payment fails. When your payment gateway returns a decline, immediately mark that reservation as released:

async function handlePaymentFailure(reservationId: string): Promise<void> {
  await pool.query(
    `UPDATE stock_reservations 
     SET status = 'released' 
     WHERE id = $1`,
    [reservationId]
  );
  
  // Notify customer, log event, etc.
}

In a distributed system with multiple services, consider using a message queue (Kafka, RabbitMQ) to emit a ReservationExpired event that other services can consume. This decouples the release logic and makes your system more resilient.

Show 'low stock' honestly

Users should see an honest picture of availability. If you have 1 unit left but 3 active reservations, you're not selling anymore—tell them that.

Calculate available stock by subtracting active reservations from physical inventory:

async function getAvailableStock(productId: string): Promise<number> {
  const result = await pool.query(
    `SELECT 
       (i.quantity - COALESCE(r.reserved, 0)) as available
     FROM inventory i
     LEFT JOIN (
       SELECT product_id, SUM(quantity) as reserved
       FROM stock_reservations
       WHERE status = 'active' AND expires_at > NOW()
       GROUP BY product_id
     ) r ON i.product_id = r.product_id
     WHERE i.product_id = $1`,
    [productId]
  );

  if (!result.rows.length) return 0;
  return Math.max(0, result.rows[0].available);
}

On the frontend, show this clearly:

• If available > 10: "In stock"
• If available 3–10: "Only 5 left – order soon"
• If available 1–2: "Last 2 available"
• If available = 0: "Out of stock" (disable checkout)

Some teams also show a reservation timer: "Your reservation expires in 8 minutes" once checkout begins. This manages expectations and encourages faster payment.

Common mistakes

• Forgetting the expiry cleanup job. Reservations that aren't cleaned up become invisible inventory debt. Set a calendar reminder to verify your cleanup is actually running in production.
• Using application-level locking instead of database locks. A Redis lock or mutex in your app doesn't survive crashes or multi-instance deployments. Always rely on the database for the final decrement.
• Reserving at the wrong time. Reserve at checkout initiation, not at "add to cart." Adding to cart is too early; customers abandon carts constantly. Reserve when they've committed to paying.
• Not accounting for reservations in stock queries. If your dashboard or API doesn't subtract active reservations from available count, it will mislead customers and your team.
• Setting reservation expiry too short. If a customer's bank is slow, a 5-minute window isn't enough. 15–30 minutes is more forgiving. Too long, and you lock up inventory unnecessarily.

Key takeaways

• Prevent overselling by creating temporary reservations at checkout, not by immediately decrementing stock.
• Use atomic, database-level decrements with guard clauses to ensure one item can't be sold twice.
• Release expired reservations in a background job running every few minutes so inventory doesn't get stuck.
• Calculate and display available stock by subtracting active reservations from physical inventory, and be transparent about low-stock situations.
• Always rely on the database for the final increment or decrement operation; application-level locking won't survive concurrent requests or deployment restarts.
• Test your reservation logic with load testing and chaos injection to catch race conditions before production.