Flash-sale overselling usually starts with a simple race condition:
Two requests check the same inventory, both see one unit remaining, and both attempt to purchase it.
The solution is not another application-level check. The critical operation must be protected by a transaction and row-level locking.
The Core Pattern
With GBase Database (GBase 8c), the inventory update can be handled within a single transaction:
BEGIN;
SELECT stock
FROM items
WHERE id = :sku
FOR UPDATE;
UPDATE items
SET stock = stock - 1
WHERE id = :sku
AND stock > 0;
INSERT INTO orders(order_id, sku, qty)
VALUES (:oid, :sku, 1);
COMMIT;
FOR UPDATE locks the inventory row while the transaction is in progress. Concurrent requests targeting the same SKU must wait rather than independently reading and modifying the same stock value.
The stock > 0 condition provides an additional safeguard: once inventory reaches zero, another purchase cannot reduce it below zero.
The Real Performance Challenge
Row-level locking protects correctness, but it also creates contention around hot SKUs.
During a flash sale, thousands of requests may compete for the same inventory row. The goal isn't to eliminate locking—it's to keep the critical section as short as possible.
Good practices include:
- Keep the transaction focused on inventory and order operations
- Avoid external API calls inside the transaction
- Avoid unnecessary queries while holding the lock
- Commit as quickly as the business logic allows
In other words:
Lock only what you need, and hold it only as long as necessary.
Distribution Design Matters Too
For distributed workloads, data placement can affect transaction latency.
With GBase Database (GBase 8c), distribution-key design should consider high-contention data. Keeping the inventory row and related transactional data within an appropriate distribution boundary can help reduce unnecessary cross-node coordination.
This is especially important for flash-sale workloads where a small number of SKUs can become extremely hot.
The Takeaway
Overselling isn't simply an application bug. It is a concurrency-control problem.
The reliable pattern is straightforward:
Transaction + row-level lock + conditional update + short critical section.
For high-concurrency inventory systems, correctness comes first. Then optimize the transaction path so that thousands of buyers don't turn one inventory row into a system-wide bottleneck.
