One stock pool, four sales channels: a reservation ledger instead of a computed available

The issue of overselling in multi-channel e-commerce is not due to a data problem, but rather a concurrency problem that can be resolved through better implementation. Instead of attempting to calculate available stock with complex arithmetic, the solution lies in reserving stock itself using a reservation ledger. This approach involves creating a table called "stock_movement" to track movements of stock between physical sites and channels.

By modeling the pool as movements rather than a fixed number, the system can more accurately manage stock levels.

A unique index is created on the "stock_movement" table for reservations, ensuring that retried webhook requests are harmless. When a reservation is made, it is treated as a guarded write, meaning the check and the write must be combined into one statement to prevent race conditions. The system inserts a reservation record, and if the requested units are available, the order is accepted; otherwise, it is rejected.

This method eliminates the window of time where another channel could take the same units, as the reservation itself is the atomic operation.

Postgres evaluates the where clause against a snapshot taken during the insert, locking the row explicitly and ensuring a serializable transaction for multi-SKU orders. For multi-line orders, it is recommended to lock the rows in a deterministic order to avoid deadlocks. Reservations must have a time-to-live (TTL) to prevent leaks, such as payment failures, abandoned checkouts, marketplace orders that never sync, or cancelled lines.

A sweep job updates reserved stock to be released when the reservation expires, either manually or by running the sweep job. This approach provides transparency in stock levels and ensures that overselling is avoided.