Avoid race conditions in e-commerce scenarios

Question

My client has an e-commerce site that sells electronics, and there were situations when the product was sold many times more than they had in the inventory. This is because if two users buy a product at a time when there is only one product left in the warehouse, one session does not complete the registration of the product as sold before the start of another session (therefore, it continues as usual, thinking that there is one on the left), although There is a check at the beginning of the process. This, obviously, will cost money (fee for return, refund, etc.) And inconvenience to consumers.

So I was wondering if I could fix it anyway? I was thinking about creating a “marker” at the beginning of the process, that is, it will check the inventory and, if it is sold, it will mark the product as such, thereby preventing other sessions from buying it. But this also creates an additional problem: if something happens on the client’s side, which will lead to their cancellation of the average process (loss of power, etc.), then even if the product is marked as sold, it is not actually sold from the moment of placing the order the process is not over. If this continues, there will be an excess of products. Secondly, it is also possible that the session will check for WHILE inventory when another session marks it as sold, so the first session will continue, although the second session has already bought it. This brings us back to the original problem.

I look at table locks at the end of the database, but I'm not sure if this is the best idea. Any suggestions would be much appreciated!

Thanks dyip

Answer 1

You may have fault tolerance, so that the order will be placed when the user checks, but the user's credit card is not actually charged until the time of their order. Then, in cases where the inventory is over, you can simply send these customers and tell them: "Sorry, we sold X, you will not be charged, etc."

You can give them some discount on your next order as compensation, but this process will avoid any fees with refunds, refunds, etc., since they are not actually charged for an order that was not in stock.

Of course, it seems that this will change the order processing of orders on the site, and it will require someone to enter information into the system after each order. In addition, you will need a card validation confirmation to know that it is valid before placing an order to prevent fraud - although presumably the site is already doing this?

Answer 2

I suggest you do the following:

• Do not reduce the availability of product stock when adding to cart; You do not want to miss any possible sale in the future;
• When the user decides to start the checkout process, and then on the back panel you can mark all the products in the shopping cart as reserved by adding them to a separate database table.

• If user B starts the verification process after some time, you can check the availability of each product in the basket again, for example:

  productAvailability - quantityReservedForThisProduct >= quantityRequested 

  If the specified condition is false, it means that someone else has already expressed a desire to buy it. Therefore, you can inform your user about the current status.

  In a nutshell, if someone else has started the verification process, he automatically backs up these products.

• Then, when the user's first payment is successful, we will update both the tables with reserved products and those that store actual products and reduce the availability of stocks.

• In addition, we do not want to be in a situation where the user has reserved products (i.e., started the verification process), but never bought them. For this reason, you can enter the TTL mechanism. Thus, at certain intervals, you can delete verification processes that exceed a fixed period of time.

  For example, if a user has started the verification process, but after, say, 10 minutes, he has not completed it yet, then remove it from the database and release the reserved products.

In my mind, I have not tested this approach;