cancel
Showing results for 
Search instead for 
Did you mean: 

Magento 1.9.0.1 misscalculates inventory when under heavy load

Magento 1.9.0.1 misscalculates inventory when under heavy load

We are experiencing issues with stock management (basic Magento stock management, no plugins or modifications), when our server is under heavy load. It appears as there is no proper locking of cataloginventory_stock_item table when reindexing after product purchase (order created).

 

We can see that there are two MySQL queries being executed for one cataloginventory_stock_item update after product purchase, one is something like:

 

UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN 22 THEN qty-2 ELSE qty END WHERE (product_id IN (22)) AND (stock_id = 1)

 

and second one is :

 

UPDATE `cataloginventory_stock_item` SET `product_id` = '22', `stock_id` = '1', `qty` = '109', `min_qty` = '0', `use_config_min_qty` = '1', `is_qty_decimal` = '0', `backorders` = '0', `use_config_backorders` = '1', `min_sale_qty` = '1', `use_config_min_sale_qty` = '1', `max_sale_qty` = '10', `use_config_max_sale_qty` = '0', `is_in_stock` = '1', `low_stock_date` = '2015-04-16 13:23:00', `notify_stock_qty` = NULL, `use_config_notify_stock_qty` = '1', `manage_stock` = '0', `use_config_manage_stock` = '1', `stock_status_changed_auto` = '0', `use_config_qty_increments` = '1', `qty_increments` = '0', `use_config_enable_qty_inc` = '1', `enable_qty_increments` = '0', `is_decimal_divided` = '0' WHERE (item_id='103')

 

The problem here is that the second update does not lock cataloginventory_stock_item table (as the first one do with 'FOR UPDATE'), and stock gets really messed up. Long story short, we have managed to sell almost 130% of the inventory without realizing. We found out that the second update is executed by

$item->save();

from reindexQuoteInventory method in app/code/core/Mage/CatalogInventory/Model/Observer.php

 

but $item in fact contains model of cataloginventory_stock_item from a while back, so if any mysql update is made by other PHP thread beetween SELECT and this UPDATE, this update will not remove items from stock properly, and we are selling more items than we have.

 

Below are update queries that increase number of stock products. Any help or suggestions will be much appreciated, are we doing anything wrong here or is it Magento bug?

 

 

UPDATE `cataloginventory_stock_item` SET `product_id` = '22', `stock_id` = '1', `qty` = '113', `min_qty` = '0', `use_config_min_qty` = '1', `is_qty_decimal` = '0', `backorders` = '0', `use_config_backorders` = '1', `min_sale_qty` = '1', `use_config_min_sale_qty` = '1', `max_sale_qty` = '10', `use_config_max_sale_qty` = '0', `is_in_stock` = '1', `low_stock_date` = '2015-04-16 13:22:53', `notify_stock_qty` = NULL, `use_config_notify_stock_qty` = '1', `manage_stock` = '0', `use_config_manage_stock` = '1', `stock_status_changed_auto` = '0', `use_config_qty_increments` = '1', `qty_increments` = '0', `use_config_enable_qty_inc` = '1', `enable_qty_increments` = '0', `is_decimal_divided` = '0' WHERE (item_id='103')

UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN 22 THEN qty-4 ELSE qty END WHERE (product_id IN (22)) AND (stock_id = 1)

UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN 22 THEN qty-2 ELSE qty END WHERE (product_id IN (22)) AND (stock_id = 1)

UPDATE `cataloginventory_stock_item` SET `product_id` = '22', `stock_id` = '1', `qty` = '109', `min_qty` = '0', `use_config_min_qty` = '1', `is_qty_decimal` = '0', `backorders` = '0', `use_config_backorders` = '1', `min_sale_qty` = '1', `use_config_min_sale_qty` = '1', `max_sale_qty` = '10', `use_config_max_sale_qty` = '0', `is_in_stock` = '1', `low_stock_date` = '2015-04-16 13:23:00', `notify_stock_qty` = NULL, `use_config_notify_stock_qty` = '1', `manage_stock` = '0', `use_config_manage_stock` = '1', `stock_status_changed_auto` = '0', `use_config_qty_increments` = '1', `qty_increments` = '0', `use_config_enable_qty_inc` = '1', `enable_qty_increments` = '0', `is_decimal_divided` = '0' WHERE (item_id='103')

UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN 22 THEN qty-6 ELSE qty END WHERE (product_id IN (22)) AND (stock_id = 1)

UPDATE `cataloginventory_stock_item` SET `product_id` = '22', `stock_id` = '1', `qty` = '107', `min_qty` = '0', `use_config_min_qty` = '1', `is_qty_decimal` = '0', `backorders` = '0', `use_config_backorders` = '1', `min_sale_qty` = '1', `use_config_min_sale_qty` = '1', `max_sale_qty` = '10', `use_config_max_sale_qty` = '0', `is_in_stock` = '1', `low_stock_date` = '2015-04-16 13:23:05', `notify_stock_qty` = NULL, `use_config_notify_stock_qty` = '1', `manage_stock` = '0', `use_config_manage_stock` = '1', `stock_status_changed_auto` = '0', `use_config_qty_increments` = '1', `qty_increments` = '0', `use_config_enable_qty_inc` = '1', `enable_qty_increments` = '0', `is_decimal_divided` = '0' WHERE (item_id='103')

UPDATE `cataloginventory_stock_item` SET `product_id` = '22', `stock_id` = '1', `qty` = '103', `min_qty` = '0', `use_config_min_qty` = '1', `is_qty_decimal` = '0', `backorders` = '0', `use_config_backorders` = '1', `min_sale_qty` = '1', `use_config_min_sale_qty` = '1', `max_sale_qty` = '10', `use_config_max_sale_qty` = '0', `is_in_stock` = '1', `low_stock_date` = '2015-04-16 13:23:06', `notify_stock_qty` = NULL, `use_config_notify_stock_qty` = '1', `manage_stock` = '0', `use_config_manage_stock` = '1', `stock_status_changed_auto` = '0', `use_config_qty_increments` = '1', `qty_increments` = '0', `use_config_enable_qty_inc` = '1', `enable_qty_increments` = '0', `is_decimal_divided` = '0' WHERE (item_id='103')

UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN 22 THEN qty-1 ELSE qty END WHERE (product_id IN (22)) AND (stock_id = 1)

UPDATE `cataloginventory_stock_item` SET `product_id` = '22', `stock_id` = '1', `qty` = '102', `min_qty` = '0', `use_config_min_qty` = '1', `is_qty_decimal` = '0', `backorders` = '0', `use_config_backorders` = '1', `min_sale_qty` = '1', `use_config_min_sale_qty` = '1', `max_sale_qty` = '10', `use_config_max_sale_qty` = '0', `is_in_stock` = '1', `low_stock_date` = '2015-04-16 13:23:10', `notify_stock_qty` = NULL, `use_config_notify_stock_qty` = '1', `manage_stock` = '0', `use_config_manage_stock` = '1', `stock_status_changed_auto` = '0', `use_config_qty_increments` = '1', `qty_increments` = '0', `use_config_enable_qty_inc` = '1', `enable_qty_increments` = '0', `is_decimal_divided` = '0' WHERE (item_id='103')
3 REPLIES 3

Re: Magento 1.9.0.1 misscalculates inventory when under heavy load

unfortunately we're experiencing the same problem on a CE 1.8.1.0 installation. We haven't digged into this as deep as you, but I strongly assume the cause is the same: heavy load - stock gets messed up, as orders are not decreasing qty correctly.

Did you resolve the issue somehow?

Re: Magento 1.9.0.1 misscalculates inventory when under heavy load

We have submitted a bug report, but got no response so far:

 

http://www.magentocommerce.com/bug-tracking/issue/index/id/743

 

Our temporary solution is to disable "Notify for quantity below" by setting it to 0.

Re: Magento 1.9.0.1 misscalculates inventory when under heavy load

Did you ever get any response or fix this problem? Multiple clients have reported this problem, luckily only going into -1 stock but it's still annoying to have to explain.