Skip to content

Lock wait timeout on catalog_product_entity table due to DeleteOutdatedPriceValues cron #39112

Open
@senthilengg

Description

@senthilengg

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

  1. Price in Admin should set at Global level
  2. Ensure 5 million record in catalog_product_entity_decimal table and No rows should match with store_id!=0 for attribute_id=229
  3. In parallel send a lot of updates to catalog_product_entity
  4. Ensure the cron is running and throw the delete to DB
  5. 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)
  6. 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

No one assigned

    Labels

    Area: FrameworkComponent: DBIssue: ConfirmedGate 3 Passed. Manual verification of the issue completed. Issue is confirmedPriority: P3May be fixed according to the position in the backlog.Progress: ready for devReported on 2.4.7Indicates original Magento version for the Issue report.Reproduced on 2.4.xThe issue has been reproduced on latest 2.4-develop branchTriage: Dev.ExperienceIssue related to Developer Experience and needs help with Triage to Confirm or Reject it

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions