Align stock item lock and qty update on the primary key (#39417) - #41224

Description (*)

Under concurrent order placement on the legacy CatalogInventory path, InnoDB reports deadlocks (1213) on cataloginventory_stock_item, and the victim is the SELECT ... FOR UPDATE from Stock::lockProductsStock().

StockManagement::registerProductsSale() locks the rows through one index and then updates them through another, inside a single transaction:

• lockProductsStock() resolves item_ids and runs SELECT si.* ... WHERE item_id IN (...) FOR UPDATE, a range on PRIMARY, so locks are taken in item_id order.
• After the per-item validation loop, correctItemsQty() runs UPDATE ... SET qty = CASE product_id ... WHERE product_id IN (...) AND website_id = ?, a range on CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID_STOCK_ID, so it requests locks again in product_id order, through secondary index records first. Its nested beginTransaction()/commit() releases nothing, because the adapter refcounts nesting.

item_id and product_id orderings are independent after imports and deletions, so two overlapping carts can request the same rows in opposite order.

The fix makes correctItemsQty() resolve item_ids with the same non-locking pre-select lockProductsStock() already uses, sort them, and update WHERE item_id IN (...) with CASE item_id. In registerProductsSale() the update then touches only rows the FOR UPDATE already holds on PRIMARY, so the transaction has exactly one lock acquisition order. lockProductsStock() additionally sorts its ids so that order is ascending for every transaction. QtyCounterInterface, the method signature and the $items contract are unchanged.

EXPLAIN on the same five rows (1200-row table):

The deadlock itself needs the reporter's concurrency (100 JMeter users) and did not fire in a two-session interleave on a 1200-row table, so this PR is justified by the access paths, not by a reproduced deadlock. Please re-run the load scenario before closing the issue.

Cost: one extra indexed, non-locking SELECT per correctItemsQty() call, covered by CATALOGINVENTORY_STOCK_ITEM_WEBSITE_ID_PRODUCT_ID.

Fixed Issues (if relevant)

1. Fixes Deadlock Error in Magento 2 During High Concurrency: Serialization failure on cataloginventory_stock_item Table #39417

Manual testing scenarios (*)

1. Place an order for several simple products with legacy CatalogInventory (MSI disabled). Stock quantities decrease by the ordered qty.
2. Cancel the order. Quantities are restored (revert path uses the same method).
3. Enable the MySQL general log during checkout: the qty update is now WHERE item_id IN (...) and EXPLAIN shows PRIMARY.
4. Run the reporter's concurrent placeOrder scenario and compare the 1213 rate.

Contribution checklist (*)

• Pull request has a meaningful description of its purpose
• All commits are accompanied by meaningful commit messages
• All new or changed code is covered with unit/integration tests (if applicable)
• README.md files for modified modules are updated and included in the pull request if any README.md predefined sections require an update
• All automated tests passed successfully (all builds are green)