Inventory oversold problem during high traffic on Magento website

Question

When we have an inventoried item that sells through very quickly, we can run into problems having Magento manage inventory. Ex: If an product assigned with 200 units, it sold through so quickly that we had multiple orders come in at the same exact second. Each time an order comes in at the same exact second, Magento only decrements the inventory for ONE of those orders, and therefore we oversell.

The above statement might be not accurate reason of this but it seems Magento lock tables during each order transaction and incase of any deadlock (due to other requests on same tables) it rollback whole transaction and inventory calculation logic fails. We found this happen during product launches when there is heavy traffic, the inventory stock is not syncing up fast enough and we are ending up having more orders in system than we have the inventory for.

In short, stock inventory reverting, leading to products being oversold.

Detail of Magento Version and server:

• Magento Version:  Adobe Commerce ver. 2.4.5-p2
• Database server: It's a mult-master replication using Galera setup and having multiple databases nodes (3+)
• Application server: More then 20 Web Application nodes

Answer 1

Here are some points which address this problem

Reason for deadlocks :

If your index mode is Update by Schedule, Magento does a particular reindex (only for indexes that were invalidated) when cron jobs indexer_reindex_all_invalid and indexer_update_all_views are running. Particular reindex is faster than full reindex but also takes some time and creates an unnecessary overload on DB.

Magento has two partial index modes:

Update on Save — Reindex is done during product save to enable this mode on production when increasing the product saving time. Update by Schedule — This reindex the index once your is run.

Also You need to monitor the Index Management page to make sure that all indexes are valid. Magento executes reindex jobs every minute and if all indexes are valid it will take no more than a few seconds to Complete.

Find The Causes Index Invalidation :

Your indexes get invalidated because of the attribute values changing, you also need to know which values exactly. For this you can install the Install the Cron Manager extension that can help you to monitor your cron jobs. it will give the full index configuration detail

After install this run full index command

php bin/magento indexer:reindex

Then try to increase the History lifetime to go into the Stores > Configuration > Advanced > System > Cron (Scheduled Tasks) by 1440 min = 24 hours. It will allow you to find out what's going on the website within 24 hours.

Keep an eye on the indexer_update_all_views job and indexes in the Index Management section.

2. Improve server and database performance:

• Make sure your database is optimized for increased traffic. If you have enough resources, caching methods are used.

3. Use Full page cache storage:

• Use full page storage such as Varnish or Redis to reduce the load on your server and improve response time with high traffic times.

4. Consider pre-ordering or re-ordering:

• If you frequently encounter inventory oversales, consider placing pre-orders or back-orders for high-demand items. This way, customers can still order and you can handle it once the product is there.

5. Use a queue management system:

• Implements queue management policies to manage order processing. This can help prevent server overload and ensure that orders are processed in a controlled manner.