Open
Description
Preconditions and environment
- Version 2.4.7 & Below
- https://github.com/magento/magento2/blob/2.4-develop/app/code/Magento/Catalog/Cron/DeleteOutdatedPriceValues.php was triggered by a cron every minute
- The above throw a delete query to DB which will take few seconds to delete provided the table contains rows in millions
- It took 18s in maria db cluster on commerce cloud with 5 million records
- It lead to blocking update query on the catalog_product_entity table and end up in timeout
- Basically it scans few 100K records to delete zero records
- Reason is missing of composite index with store id and attribute id
Steps to reproduce
- Price in Admin should set at Global level
- Ensure 5 million record in catalog_product_entity_decimal table and No rows should match with store_id!=0 for attribute_id=229
- In parallel send a lot of updates to catalog_product_entity
- Ensure the cron is running and throw the delete to DB
- OR Run a sample delete query
delete from catalog_product_entity_decimal where attribute_id=229 and store_id!=0
(Assumption attribute_id=229 is price) - Note: Its not so easy to reproduce in production
Expected result
Delete query should scan only the matching rows in this case it should be zero
Delete query should few milli seconds to execute
Delete query should not lock catalog_product_entity table
Actual result
Delete query scanning few 100K rows
Delete query takes more than 18 secs to execute
Delete query locks catalog_product_entity table and in parallel update query on catalog_product_entity end up in lock wait timeout.
Additional information
No response
Release note
No response
Triage and priority
- Severity: S0 - Affects critical data or functionality and leaves users without workaround.
- Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
- Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
- Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
- Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.
Metadata
Metadata
Assignees
Labels
Gate 3 Passed. Manual verification of the issue completed. Issue is confirmedMay be fixed according to the position in the backlog.Indicates original Magento version for the Issue report.The issue has been reproduced on latest 2.4-develop branchIssue related to Developer Experience and needs help with Triage to Confirm or Reject it