Context
I am creating a e-commerce website. We sell products in a really limited amount (+/- 10 available items per product).
Problem
How to deal with stock updates when a user makes a purchase?
Example
We have 5 items for a product.
The user checks out his cart with 4 items, fills in his details and goes through the purchase process. He arrives on the third party payment website. At this point, the payement has not been sent yet so we don't descrease the quantity purchased from the available stock.
Now let's assume that during that time, an other user had the 3 items for the same product and goes to the purchase process. We have a situation where, if they both pay for these items, we will sell more than available stock.
Questions
How could we prevent that? I am thinking of having a 'pending purchase' DB table, with the orders, that would timeout after 10 minutes(?), or the user could cancel it himself. But what would be the appropriate moment to insert the pending purchase into that table? When the user clicks 'checkout'? When he click 'confirm purchase' and goes to the payment process? And what is the appropriate moment to decrease the stock?
I can't really figure out a good workflow that would prevent users from actually being able to checkout if the quantity is not available anymore. We want to prevent as much as possible that the user would encounter an error during the checkout process because the quantity/product that he wants the purchase is not available anymore.
