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')
