Avoid race conditions in eCommerce scenarios

Question

Hello,

My client has a ecommerce website that sells electronics, and there has been situations where a product is sold more times than they have in their inventory. This is because if two users buy a product at the same time when there is only one product left in stock, one session does not finish registering the product as sold out before the other session starts(so it continues as normal, thinking there is one left) even though there is a check in place at the beginning of the process. This would obviously cost money (chargeback fees, refunds, etc.) and inconvenience for the consumers.

So I was wondering if there is anyway to fix this? I thought of creating a "marker" at the beginning of the process, i.e. it would check for inventory and if sold out, it would label the product as such thus preventing other sessions from buying it. But this also creates more problem: if something happens on the customer's side that would cause them to cancel mid-process (lost of power, etc.), then even though the product is marked as sold, it did not actually sell since the checkout process did not finish. If this keeps happening, there would be an overstock of products. Secondly, it is also possible for a session to check for inventory WHILE another session is marking it as sold, thus the first session would continue even though the second session already bought it. This leads us back to the original problem.

I am looking at table lockings in the database-end, but I am not sure if thats the best idea. Any suggestions would be highly appreciated!

Thanks, dyip

Answer 1

You could have a failsafe such that an order is placed when the user checks out, but the user's credit card is not actually charged until their order ships. Then, for cases where the inventory has run out, you could just email those customers and tell them "Sorry, we have sold out of X, you will not be charged, etc".

You could give them some kind of discount on their next order as compensation, but this process would avoid any chargeback fees, refunds, etc as they are never actually charged for the order that was not in stock.

Of course, it sounds like this would change how orders are handled by the site, and it would necessitate having someone enter information into the system after each order is prepared. Also, you would need some validation of the card to know it is valid before placing the order, to prevent fraud - although presumably the site is doing this already?

Answer 2

What database are you using and what table storage engine for your inventory?