Handling Inventory Sync Across Multiple Stores Without Overselling

Selling the same last item to two customers is one of the most frustrating bugs in retail software. It looks small on paper, but it produces cancelled orders, angry customers, and staff who stop trusting the system. If you work on POS software development for multi-store businesses, inventory sync is one of the problems you'll spend the most time on.

This article covers why overselling happens, the main strategies to prevent it, and the trade-offs you'll face along the way. Code examples use PostgreSQL and Node.js-style pseudocode, but the ideas apply to any stack.

Why Overselling Happens

A single store with one terminal is easy: read stock, subtract, save. Problems begin when several things touch the same number at once:

• Two cashiers in different stores sell the last unit at the same moment.
• An online order and an in-store sale hit the same SKU together.
• A terminal goes offline, keeps selling from stale data, and syncs later.
• A network retry submits the same sale twice.

The classic culprit is the read-modify-write race condition:

Store A reads stock: 1
Store B reads stock: 1
Store A sells 1 and writes: 0
Store B sells 1 and writes: 0   // both sales succeeded, only 1 unit existed

Both writes look valid in isolation. The bug only exists in the interleaving.

Rule 1: Never Store Stock as a Single Overwritten Number

If your schema has just products.quantity and every sale does UPDATE ... SET quantity = <value your app computed>, you will lose updates.

At minimum, make the database do the math atomically:

UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = $1
  AND store_id = $2
  AND quantity >= 1;

If this affects zero rows, the sale must be rejected or handled as backorder. The condition quantity >= 1 and the decrement happen in one atomic statement, so two concurrent sales can't both succeed on the last unit.

Rule 2: Use an Append-Only Ledger

A more robust design records every stock change as an immutable event, and treats the quantity as something derived from those events.

CREATE TABLE inventory_movements (
  id           BIGSERIAL PRIMARY KEY,
  store_id     INT NOT NULL,
  product_id   INT NOT NULL,
  delta        INT NOT NULL,          -- +10 received, -1 sold, -2 damaged
  reason       TEXT NOT NULL,         -- sale, return, transfer, adjustment
  reference_id TEXT NOT NULL,         -- order ID, transfer ID, etc.
  created_at   TIMESTAMPTZ DEFAULT now(),
  UNIQUE (reference_id, product_id, store_id, reason)
);

Benefits:

• Full audit trail. You can answer "why is this number wrong?"
• Safer syncing. Terminals send events, not overwritten totals, so nothing gets clobbered.
• Idempotency. The unique constraint stops the same sale from being counted twice.

For speed, keep a cached balance table updated in the same transaction as the ledger insert, and periodically reconcile it against the sum of movements.

Rule 3: Make Every Write Idempotent

Networks fail. A terminal sends a sale, the server processes it, the response is lost, and the terminal retries. Without protection, stock is deducted twice.

Attach a unique idempotency key (for example, a UUID generated on the terminal when the sale starts) to every request:

async function recordSale(sale) {
  return db.tx(async (t) => {
    const inserted = await t.result(
      `INSERT INTO processed_requests (key) VALUES ($1)
       ON CONFLICT DO NOTHING`,
      [sale.idempotencyKey]
    );

    if (inserted.rowCount === 0) {
      return { status: 'already_processed' };
    }

    for (const line of sale.items) {
      const res = await t.result(
        `UPDATE inventory
         SET quantity = quantity - $1
         WHERE product_id = $2 AND store_id = $3 AND quantity >= $1`,
        [line.qty, line.productId, sale.storeId]
      );
      if (res.rowCount === 0) throw new Error('INSUFFICIENT_STOCK');
    }

    return { status: 'ok' };
  });
}

If the retry arrives, the second insert does nothing and the sale isn't applied again.

Strategy 1: Central Source of Truth (Strong Consistency)

All stores talk to one central database, and every sale checks and decrements stock there.

Pros: Simple to reason about, no overselling if done atomically.
Cons: Every sale depends on connectivity and latency. If the connection drops, the store can't sell, which is unacceptable for many retailers.

This is a good starting point for small chains with reliable internet.

Strategy 2: Reservations for Online and Cross-Store Orders

For online orders, click-and-collect, and inter-store transfers, don't decrement stock immediately. Create a reservation with an expiry.

CREATE TABLE reservations (
  id          UUID PRIMARY KEY,
  product_id  INT NOT NULL,
  store_id    INT NOT NULL,
  quantity    INT NOT NULL,
  expires_at  TIMESTAMPTZ NOT NULL,
  status      TEXT NOT NULL  -- active, confirmed, released, expired
);

Available-to-sell becomes:

available = on_hand - active_reservations

When payment is confirmed, convert the reservation to a sale. If checkout is abandoned, a background job releases expired reservations. This prevents two shoppers from buying the same unit while one is still paying.

Strategy 3: Offline-First Terminals With Safety Buffers

Many stores can't afford to stop selling when the internet drops. In that case, each terminal keeps a local database (SQLite is common), sells against local stock, and syncs later.

The catch is that local stock can be stale, so you need a policy for the risk:

• Safety stock buffer. Show online channels only a portion of stock, such as on-hand minus 2 units, so offline stores have headroom.
• Store-owned stock. Each store sells only its own inventory, so two stores can never conflict over the same physical unit. Cross-store availability is shown as "available at another store" and requires a transfer.
• Allocation for hot items. For high-demand SKUs, allocate stock per channel or per store rather than sharing one pool.
• Accept and reconcile. For low-value items, allow small oversells and resolve them through the ledger, with a defined process (substitute, refund, or transfer).

The right choice depends on your business. A grocery chain can tolerate small discrepancies. A store selling limited-edition items or high-value electronics cannot.

Strategy 4: Event-Driven Sync Between Stores

For larger systems, stores publish inventory events to a message broker (Kafka, RabbitMQ, or a managed queue), and a central service processes them in order.

Key points:

• Ordering matters per SKU. Partition messages by product ID so events for one item are processed sequentially.
• Consumers must be idempotent. Messages can be delivered more than once.
• Handle failures. Use retries with backoff and a dead-letter queue for events that can't be processed.
• Expect eventual consistency. Other stores' views will lag by seconds, so design the UI to reflect that ("Last updated 20 seconds ago").

Locking: Pessimistic vs. Optimistic

When you need to check-and-update multiple rows (for example, a bundle with several components), you may need explicit concurrency control.

Pessimistic locking locks the rows while you work:

BEGIN;
SELECT quantity FROM inventory
WHERE product_id = $1 AND store_id = $2
FOR UPDATE;
-- check and update
COMMIT;

Safe, but it can create contention on popular items.

Optimistic locking uses a version number and retries on conflict:

UPDATE inventory
SET quantity = quantity - 1, version = version + 1
WHERE product_id = $1 AND store_id = $2 AND version = $3;

Better for low-conflict workloads, but you need retry logic.

For most POS workloads, the single atomic UPDATE ... WHERE quantity >= n statement is the simplest and fastest option. Reach for explicit locks only when a business rule spans multiple rows.

Reconciliation: Assume Drift Will Happen

Even with good design, real-world stock drifts because of theft, damage, miscounts, and human error. Build reconciliation into the product:

• Scheduled cycle counts that create adjustment movements in the ledger.
• Alerts when calculated stock goes negative or diverges from counts.
• A report of oversell incidents and their causes, so you can see which strategy is failing.
• Clear audit trails showing who changed what, and when.

Software can prevent digital errors. It can't fully prevent physical ones, so reconciliation is part of the design, not an afterthought.

Testing Concurrency Bugs

Race conditions rarely show up in normal testing. Test them deliberately:

• Fire hundreds of simultaneous requests at a SKU with stock of 1 and assert exactly one succeeds.
• Replay the same request multiple times to verify idempotency.
• Simulate a terminal going offline, selling, and reconnecting.
• Kill the server mid-transaction and confirm no partial deductions.
• Run load tests that mimic sale-day traffic.

A simple test:

const results = await Promise.allSettled(
  Array.from({ length: 50 }, () => recordSale(makeSale({ qty: 1 })))
);
const successes = results.filter(r => r.status === 'fulfilled' && r.value.status === 'ok');
assert.equal(successes.length, 1);

Choosing a Strategy: A Quick Guide

Situation	Recommended approach
Few stores, reliable internet	Central database with atomic updates
Online and click-and-collect orders	Reservations with expiry
Unreliable connectivity	Offline-first terminals plus safety buffers
Many stores, high volume	Event-driven sync with per-SKU ordering
Limited or high-value stock	Strong consistency, no offline overselling

Key Takeaways

• Never overwrite stock with an app-computed value. Let the database do atomic, conditional updates.
• Prefer an append-only ledger of movements over a single mutable number.
• Make every write idempotent, because retries are guaranteed.
• Decide explicitly how much overselling risk your business can tolerate, then pick consistency or availability accordingly.
• Reconcile regularly, and test concurrency on purpose.

Good POS software development isn't about eliminating every edge case. It's about choosing the trade-offs that fit the retailer, making failures visible, and giving staff a clear way to fix them.

---

Before publishing on dev.to:

• Add a personal angle, such as a real oversell bug you hit or a store scenario you've worked on. Original experience helps indexing and reader trust.
• Suggested tags: #postgres, #architecture, #webdev, #database.
• Add a simple diagram of the race condition and the ledger flow, and consider making this part of a series (for example, "Building a POS: Part 3").
• Test the code snippets in your own environment before publishing, since they're simplified illustrations rather than production-ready code.