Database Lock Wait Timeout Error on Cron Job catalogrule_apply_all

[Steve]

For the past month or so the nightly cron job catalogrule_apply_all has been failing with the following error showing up in the error logs:

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction, query was: RENAME TABLE catalogrule_product TO catalogrule_producta6946100,catalogrule_product__temp11892612 TO catalogrule_product,catalogrule_product_price TO catalogrule_product_price90125c0a,catalogrule_product_price__temp97a2dda6 TO catalogrule_product_price,catalogrule_group_website TO catalogrule_group_websiteb0d46c8f,catalogrule_group_website__tempded0d19d TO catalogrule_group_website

Occasionally when this error happens the catalog rule sale pricing on our site will stop working on the site. A manual index is needed to correct it. The error happens every day but the pricing only stops working once in awhile.

Any ideas on where to start debugging this? I've looked at various different things and can't seem to figure out what is causing this.

Using Magento enterprise edition 2.4.3-p1

[johnwebdes40a5]

Hello @Steve 

The "Database Lock Wait Timeout Error" on the catalogrule_apply_all cron job in Magento 2 can occur when the cron job is unable to acquire a database lock within the specified timeout period. This error can be caused by various factors, such as heavy database load, slow queries, or misconfigured server settings.

Please follow the steps below to resolve this error:

• Increase the database lock wait timeout:
  • You can increase the timeout period for the database lock wait by modifying the app/etc/env.php file in your Magento 2 installation. Locate the db section of the file and add the following line:
    • 'initStatements'=> "SET @@session.wait_timeout = 600;"
  • This sets the lock wait timeout to 10 minutes. You can adjust the timeout value as per your requirements.
• Optimize your database:
  • You can optimize your database by removing unused tables, indexes, and data. This can help reduce the load on your database and improve the performance of the cron job.
• Check for slow queries:
  • You can use a tool such as MySQL's slow query log to identify slow queries that may be causing the database lock wait timeout error. Once you have identified the slow queries, you can optimize them or consider upgrading your server hardware
•  Consider using a dedicated database server:
  • If your Magento 2 installation is hosted on a shared server, you may want to consider using a dedicated database server to improve the performance of your cron job.
• Contact your hosting provider:
  • If the above steps do not resolve the issue, you may need to contact your hosting provider to investigate the issue further. They may be able to provide additional assistance or suggest alternative solutions.

If you find our reply helpful, please give us kudos.

A Leading Magento Development Agency That Delivers Powerful Results, Innovation, and Secure Digital Transformation.

WebDesk Solution Support Team

Get a Free Quote | | Adobe Commerce Partner | Hire Us | Call Us 877.536.3789