2

I'm writing to you with a problem to which I haven't been able to find a solution anywhere.

The error occurs on execution of dailyCatalogUpdate cronjob. The execution time on the server was already prolonged.

In the report I get this message:

a:4:{i:0;s:91:"SQLSTATE[HY000]: General error: 1205 Lock wait timeout
exceeded; try restarting transaction";i:1;s:1090:"#0
/html/magento/lib/Varien/Db/Statement/Pdo/Mysql.php(110):
Zend_Db_Statement_Pdo->_execute(Array)
#1 /html/magento/lib/Zend/Db/Statement.php(300):
Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /html/magento/lib/Zend/Db/Adapter/Abstract.php(479):
Zend_Db_Statement->execute(Array)
#3 /html/magento/lib/Zend/Db/Adapter/Pdo/Abstract.php(238):
Zend_Db_Adapter_Abstract->query('INSERT IGNORE I...', Array)
#4 /html/magento/lib/Varien/Db/Adapter/Pdo/Mysql.php(419):
Zend_Db_Adapter_Pdo_Abstract->query('INSERT IGNORE I...', Array)
#5 /html/magento/app/code/core/Mage/CatalogRule/Model/Resource/Rule.php(258):
Varien_Db_Adapter_Pdo_Mysql->query('INSERT IGNORE I...')
#6 /html/magento/app/code/core/Mage/CatalogRule/Model/Resource/Rule.php(423):
Mage_CatalogRule_Model_Resource_Rule->removeCatalogPricesForDateRange(1359504000,
1359676800, NULL)
#7 /html/magento/app/code/core/Mage/CatalogRule/Model/Observer.php(209):
Mage_CatalogRule_Model_Resource_Rule->applyAllRulesForDateRange()
#8 /html/magento/cron.php(48):
Mage_CatalogRule_Model_Observer->dailyCatalogUpdate('* */3 * * *')
#9 {main}";s:11:"script_name";s:23:"/html/magento/index.php";s:4:"skin";s:5:"admin";}

I am working on Magento 1.7.0.2.. Could you give me any clue on how to fix this?

An update on my question - a response from the server:

-----------------------
LATEST DETECTED DEADLOCK
------------------------
130207 17:59:05
*** (1) TRANSACTION:
TRANSACTION 377446C, ACTIVE 2 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 3469 lock struct(s), heap size 407992, 365715 row lock(s)
MySQL thread id 80810, OS thread handle 0x40266950, query id 7636961 172.20.136.58 p179731 Sending data
UPDATE `sales_flat_quote` AS `t1`
INNER JOIN (SELECT `t2`.`quote_id` AS `entity_id` FROM `sales_flat_quote_item` AS `t2`
INNER JOIN `catalogrule_product_price` AS `t3` WHERE (t2.product_id = t3.product_id) GROUP BY `quote_id`) AS `t2` ON t1.entity_id = t2.entity_id
SET `t1`.`trigger_recollect` = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2458 page no 789 n bits 112 index `PRIMARY` of table `usr_p179731_2`.`sales_flat_quote` trx id 377446C lock_mode X locks rec but not gap waiting


*** (2) TRANSACTION:
TRANSACTION 37744D8, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 3
MySQL thread id 80913, OS thread handle 0x47ae5950, query id 7637295 172.20.136.58 p179731 Updating
UPDATE `sales_flat_quote_item` SET `quote_id` = '62589', `created_at` = '2013-02-07     17:00:14', `updated_at` = '2013-02-07 17:00:16', `product_id` = '3265', `store_id` = '7', `parent_item_id` = NULL, `is_virtual` = '0', `sku` = '1035603', `name` = 'Butaca Catania', `description` = NULL, `applied_rule_ids` = NULL, `additional_data` = NULL, `free_shipping` = '0', `is_qty_decimal` = '0', `no_discount` = '0', `weight` = '7', `qty` = '2', `price` = '57.99', `base_price` = '57.99', `custom_price` = NULL, `discount_percent` = '0', `discount_amount` = '0', `base_discount_amount` = '0', `tax_percent` = '19', `tax_amount` = '22.03', `base_tax_amount` = '22.03', `row_total` = '115.97', `base_row_total` = '115.97', `row_total_with_discount` = '0', `row_weight` = '14', `product_type` = 'configurable', `base_tax_before_discount` = NULL, `tax_before_discount` = NULL, `original_custom_price` = NULL, `redirect_url` = NULL, `bas
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2458 page no 789 n bits 112 index `PRIMARY` of table `usr_p179731_2`.`sales_flat_quote` trx id 37744D8 lock_mode X locks rec but not gap
Record lock, heap no 39 PHYSICAL RECORD: n_fields 58; compact format; info bits 0


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2461 page no 2650 n bits 104 index `PRIMARY` of table `usr_p179731_2`.`sales_flat_quote_item` trx id 37744D8 lock_mode X locks rec but not gap waiting
Record lock, heap no 32 PHYSICAL RECORD: n_fields 55; compact format; info bits 0

Where should I go from here?

Improve this question
4
  • Your message tells you, that you're running in a lock wait timeout. Is there any other process runnning at the same time, which may lock the tables you're updating?
    – Tobias
    Commented Feb 1, 2013 at 7:59
  • How can I check this?
    – knospe
    Commented Feb 1, 2013 at 8:03
  • 1
    There's a good post at stackoverflow how to debug this. => stackoverflow.com/questions/6000336/…
    – Tobias
    Commented Feb 1, 2013 at 8:08
  • Was your question answered? Then please mark a solution.
    – Fabian Blechschmidt
    Commented Feb 28, 2013 at 17:45

3 Answers 3

Reset to default
2

The most important thing to do here is to find out exactly what's causing the lock wait timeout. They can be difficult to diagnose, but with the right toolset you can pinpoint what it is and patch it.

MySQL

First, you'll want to find out what version of MySQL you're on. You should be on the latest version of 5.5. MySQL 5.5 has some improvements for lock handling. If your host supports it, use Percona's build. They are a great company, contribute to MySQL, and have an excellent toolset. Their build of MySQL has many improvements over the standard build. MySQL 5.6 just came out, but the have_innodb method has been deprecated so the installer won't work. You could technically get Magento installed, but it hasn't been thoroughly tested with MySQL 5.6 yet.

Percona Toolkit

Install the percona toolkit. The tool you'll want to use is pt-deadlock-logger1. It will help you identify queries that are causing deadlocks. While running, it will log deadlocks to a table that you set up.

mytop

Install mytop2. It is similar to the top command, but gives you info on MySQL. It's very handy for real-time analysis of what's going on.

MySQL Slow Query Logs

Enable your slow query logs in MySQL. You can set the threshold low, around 1s. This will log any queries that take a long time to run.

New Relic

Get a trial of New Relic Pro. This tool is so important to our daily activity, I can't even express how much it has helped. It is very easy to set up, and once you have it running you can trace web transactions (methods) to a SQL statement.

Using these tools, you can gather enough information to find out exactly what's going on. Once you know, then you can make code improvements and solve your problem. Sometimes they are as simple as rewriting an ORM call to optimize a query, and other times you'll need to figure out how to rewrite an entire module. Be sure to look at all your custom observers and make sure there isn't anything that would generate a long running lock.

References:

  1. http://www.percona.com/doc/percona-toolkit/2.1/pt-deadlock-logger.html
Improve this answer
0

Have you disabling indexing when your job starts? This error appears if you are trying to do an update/save, and indexing is doing its thing.

Improve this answer
0

I would suggest doing as dedmeet suggested and ensuring that you have no other crons still running when you begin your 3am job.

The default timeout for MySQL is 50 seconds. You could try and increase that to a larger figure like 300 seconds to see if it will resolve your issue but I would suggest you first make sure no other cron jobs are overlapping with the dailyCatalogUpdate job.

The syntax for /etc/my.cnf or /etc/mysqld/my.cnf is:

innodb_lock_wait_timeout = 300
Improve this answer
1
  • 1
    Just as a caveat (I know you're not suggesting it), but blindly increasing the lock_wait_timeout can cause more problems in the long run due to processes consuming resources whilst endlessly waiting on locks - rather than giving up earlier on. I wouldn't suggest much more than 180 seconds.
    – Ben Lessani
    Commented Feb 4, 2013 at 21:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.