E-commerce

In almost every e-commerce system this line sits somewhere:

UPDATE products SET stock = stock - 1 WHERE id = ? AND stock > 0

On its own it is a correct statement. The problem is which question it answers.

Two different questions

There are two separate questions in the system and they get confused constantly:

1. How many are in the warehouse? — physical truth, the result of a count
2. How many can I promise? — a commercial decision, a computed value

The industry calls the second one available-to-promise, and it is a different number from the first. There might be 10 in the warehouse, but:

• 3 are held for orders still going through payment
• 2 came back as returns and have not been inspected
• 1 is damaged and unsellable
• 5 are in transit and arrive in three days

The number you can promise is not 10. And that number depends on when you ask — five minutes later it is different.

Reservation: a promise with an expiry instead of a counter

The right model is this: when the customer adds to cart or moves to checkout, you do not decrement a counter, you create a reservation with an expiry.

reservation
  product, quantity, holder (session/order), expires_at

Sellable quantity is no longer a counter but a calculation:

sellable = physical − (unexpired reservations) − (blocked)

This structure has three advantages.

It returns on its own when the timer runs out. Stock held by a customer who abandoned checkout is released fifteen minutes later even if the cancellation job never ran. In the counter model you have to write a cleanup job to compensate, and be sure it is running — on the day it is not, stock leaks.

You know who is holding it. When a counter decrements, no information is left behind: it says 4 instead of 7 and you do not know why. In the reservation model there is an answer to “which three sessions are holding these three units.” For a support team, that is the whole difference.

The race condition lives in one place. Creating a reservation is a single critical section; everything else is a read. In the counter model every flow — cart, checkout, cancellation, return — touches the counter, and each is its own source of races.

Where it gets stuck

The reservation model is not free.

A popular product turns into one row. When a thousand people try to reserve the same product at once, that product’s row becomes a lock point. The fix is to append reservations rather than lock per product — writing rows instead of updating one — and to compute the sellable quantity as a sum. The write contention disappears and reads get more expensive.

Once reads get expensive you add a cache, and once you add a cache you lose consistency. It is acceptable for the “only 3 left” on a product page to be stale. It is not acceptable at add-to-cart. Until you separate those two reads, you will be either slow or wrong.

Partial-order behaviour is a commercial decision, not a technical one. If two of three line items can be reserved, what you do is a product decision: hold, ship partially, or reject the whole order. The system has to support all three, and which one applies must not be hardcoded.

The aviation inverse

What is interesting is that aviation solves this problem from exactly the opposite direction: it sells more tickets than seats and closes the gap at the gate with compensation.

The only reason they can do that is that the compensation is computable in advance — regulation defines what is owed in which case. In e-commerce, the loss suffered by a customer whose order is cancelled is not regulated; the cost lands on reputation and cannot be measured.

You cannot optimise a risk you cannot measure. So we reserve and they oversell. The same problem with a different cost function.

The cart is the most underestimated structure in e-commerce systems. It looks simple: product id, quantity, user.

In reality a cart is a time window — everything inside it goes stale as the world outside changes.

Every field in a cart can go stale

At the moment the customer added the product, all of this was true:

Field	How soon it goes stale
Price	When a promotion ends, possibly within seconds
Stock	When somebody else buys it
Shipping cost	When the address changes, or the basket crosses a threshold
Promotion eligibility	When another product is added
Tax	When the delivery country is chosen

So a cart is not a frozen snapshot; it is a derived thing that has to be recomputed on every display. Systems that keep the cart as a table and write a price into it eventually have to answer the question of when that price gets refreshed — usually in production, via a complaint.

When does the price freeze

There is no technical answer to this; you are choosing a commercial promise.

If it freezes at add-to-cart you have told the customer “the price you saw is yours.” The consequence is that a product sitting in a cart for two weeks sells at the old price, and during a price rise carts turn into option contracts. If you have not put an expiry on that, you will see it on the balance sheet.

If it freezes at checkout the customer can see a different amount from the one they added. This is the correct behaviour but it has to be stated plainly; if you do not say it, someone who saw 100 in the cart and sees 120 at payment has lost trust in you.

The middle option is what most systems do: the price freezes for a short window — typically 15 to 30 minutes — and when it expires the cart is quietly revalued and any change is shown to the user.

Whichever you choose, it has to live in exactly one place in the code. I have seen systems where this decision was implemented two different ways in two different services; the result was price inconsistencies that nobody could reproduce but that happened a few times a month.

Whose cart is it

The second underestimated question is who owns the cart.

An anonymous user’s cart lives in a cookie or a session. What happens when they log in? Does the old cart merge with the new one, overwrite it, or does the user get asked?

All three are defensible. What is not defensible is leaving the decision unmade, because then the behaviour depends on which service ran first and the user sometimes loses their cart.

The same question returns with multiple devices: two open carts, one on the phone and one on the desktop. Without a merge rule, last writer wins and the customer has no idea what happened.

An abandoned cart is not a failure

The large majority of carts never become orders, and that is normal. A cart is a signal of intent, not a commitment.

Accepting that changes two things. First, there is no need to keep cart data in the same place as durable order data — you would be storing data with a very different lifetime under the same guarantees. Second, abandoned carts are not a cleanup problem but a data source: which step lost them is among the most valuable inputs a product team has.

Modelling the cart as a temporary window clarifies both the storage decision and the product decision.