Magento Mysql 死锁-1205 Lock wait timeout exceeded

追紧刷magento的时候报 这个错误

[root@ajiang ~]# php -f /home/wwwroot/weicotshop/shell/indexer.php reindexall
Product Attributes index process unknown error:
exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction' in /home/wwwroot/weicotshop/lib/Zend/Db/Statement/Pdo.php:248
Stack trace:
#0 /home/wwwroot/weicotshop/lib/Zend/Db/Statement/Pdo.php(248): PDOStatement->execute(Array)
#1 /home/wwwroot/weicotshop/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /home/wwwroot/weicotshop/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /home/wwwroot/weicotshop/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /home/wwwroot/weicotshop/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('DELETE FROM `ca...', Array)
#5 /home/wwwroot/weicotshop/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('DELETE FROM `ca...', Array)
#6 /home/wwwroot/weicotshop/lib/Zend/Db/Adapter/Abstract.php(661): Varien_Db_Adapter_Pdo_Mysql->query('DELETE FROM `ca...')
#7 /home/wwwroot/weicotshop/app/code/core/Mage/Index/Model/Resource/Abstract.php(235): Zend_Db_Adapter_Abstract->delete('cataloginventor...')
#8 /home/wwwroot/weicotshop/app/code/core/Mage/CatalogInventory/Model/Resource/Indexer/Stock.php(245): Mage_Index_Model_Resource_Abstract->clearTemporaryIndexTable()
#9 /home/wwwroot/weicotshop/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_CatalogInventory_Model_Resource_Indexer_Stock->reindexAll()
#10 /home/wwwroot/weicotshop/app/code/core/Mage/Index/Model/Process.php(210): Mage_Index_Model_Indexer_Abstract->reindexAll()
#11 /home/wwwroot/weicotshop/app/code/core/Mage/Index/Model/Process.php(258): Mage_Index_Model_Process->reindexAll()
#12 /home/wwwroot/weicotshop/app/code/core/Mage/Index/Model/Process.php(252): Mage_Index_Model_Process->reindexEverything()
#13 /home/wwwroot/weicotshop/shell/indexer.php(166): Mage_Index_Model_Process->reindexEverything()
#14 /home/wwwroot/weicotshop/shell/indexer.php(212): Mage_Shell_Compiler->weicotshopn()
#15 {main}

用 show full processlist \G 查看状态

mysql> show full processlist \G
*************************** 1. row ***************************
     Id: 13036
   User: jiang
   Host: localhost:xxoo
     db: m_w
Command: Query
   Time: 524198
  State: Sending data
   Info: SELECT `main_table`.`entity_id`, `url_rewrite`.`request_path` FROM `catalog_product_entity` AS `main_table`
 INNER JOIN `catalog_product_website` AS `w` ON main_table.entity_id = w.product_id
 LEFT JOIN `core_url_rewrite` AS `url_rewrite` ON url_rewrite.product_id = main_table.entity_id AND url_rewrite.is_system = 1 AND url_rewrite.category_id IS NULL AND url_rewrite.store_id = 1 AND url_rewrite.id_path LIKE 'product/%'
 INNER JOIN `catalog_product_entity_int` AS `t1_visibility` ON main_table.entity_id=t1_visibility.entity_id AND t1_visibility.store_id=0
 LEFT JOIN `catalog_product_entity_int` AS `t2_visibility` ON t1_visibility.entity_id = t2_visibility.entity_id AND t1_visibility.attribute_id = t2_visibility.attribute_id AND t2_visibility.store_id = 1
 INNER JOIN `catalog_product_entity_int` AS `t1_status` ON main_table.entity_id=t1_status.entity_id AND t1_status.store_id=0
 LEFT JOIN `catalog_product_entity_int` AS `t2_status` ON t1_status.entity_id = t2_status.entity_id AND t1_status.attribute_id = t2_status.attribute_id AND t2_status.store_id = 1 WHERE (w.website_id='1') AND (t1_visibility.attribute_id='102') AND ((IF(t2_visibility.value_id > 0, t2_visibility.value, t1_visibility.value)) IN(3, 2, 4)) AND (t1_status.attribute_id='96') AND ((IF(t2_status.value_id > 0, t2_status.value, t1_status.value)) IN(1))
*************************** 2. row ***************************
     Id: 226052
   User: jiang
   Host: localhost:xxoo
     db: m_w
Command: Query
   Time: 5767
  State: Sending data
   Info: SELECT `main_table`.`entity_id`, `url_rewrite`.`request_path` FROM `catalog_product_entity` AS `main_table`
 INNER JOIN `catalog_product_website` AS `w` ON main_table.entity_id = w.product_id
 LEFT JOIN `core_url_rewrite` AS `url_rewrite` ON url_rewrite.product_id = main_table.entity_id AND url_rewrite.is_system = 1 AND url_rewrite.category_id IS NULL AND url_rewrite.store_id = 1 AND url_rewrite.id_path LIKE 'product/%'
 INNER JOIN `catalog_product_entity_int` AS `t1_visibility` ON main_table.entity_id=t1_visibility.entity_id AND t1_visibility.store_id=0
 LEFT JOIN `catalog_product_entity_int` AS `t2_visibility` ON t1_visibility.entity_id = t2_visibility.entity_id AND t1_visibility.attribute_id = t2_visibility.attribute_id AND t2_visibility.store_id = 1
 INNER JOIN `catalog_product_entity_int` AS `t1_status` ON main_table.entity_id=t1_status.entity_id AND t1_status.store_id=0
 LEFT JOIN `catalog_product_entity_int` AS `t2_status` ON t1_status.entity_id = t2_status.entity_id AND t1_status.attribute_id = t2_status.attribute_id AND t2_status.store_id = 1 WHERE (w.website_id='1') AND (t1_visibility.attribute_id='102') AND ((IF(t2_visibility.value_id > 0, t2_visibility.value, t1_visibility.value)) IN(3, 2, 4)) AND (t1_status.attribute_id='96') AND ((IF(t2_status.value_id > 0, t2_status.value, t1_status.value)) IN(1))
*************************** 3. row ***************************
     Id: 227290
   User: jiang
   Host: localhost:xxoo
     db: m_w
Command: Query
   Time: 1
  State: Sending data
   Info: SELECT `core_url_rewrite`.* FROM `core_url_rewrite` LIMIT 320 OFFSET 770880
*************************** 4. row ***************************
     Id: 227834
   User: jiang
   Host: localhost:xxoo
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show full processlist
*************************** 5. row ***************************
     Id: 227838
   User: jiang
   Host: localhost:xxoo
     db: m_w
Command: Query
   Time: 30
  State: updating
   Info: DELETE FROM `catalogsearch_result` WHERE query_id = 1245
*************************** 6. row ***************************
     Id: 227839
   User: jiang
   Host: localhost:xxoo
     db: m_w
Command: Query
   Time: 22
  State: Updating
   Info: UPDATE `catalogsearch_query` SET `query_text` = 'bulls', `num_results` = '395', `popularity` = '28122', `redirect` = NULL, `synonym_for` = NULL, `store_id` = '1', `display_in_terms` = '1', `is_active` = '1', `is_processed` = '1', `updated_at` = '2015-11-09 09:46:31' WHERE (query_id='466')
6 rows in set (0.00 sec)

mysql>show processlist;
mysql>show engine innodb status ,
//查看
mysql>kill thread_id;
//kill 掉所有锁表的进程
mysql>SELECT * FROM information_schema.INNODB_TRX\G;
//再查看事物表 注意trx_mysql_thread_id 的值

相关脚本

#!/bin/bash
mysql -u root -e "show processlist" | grep -i "Locked" >> locked_log.txt

for line in `cat locked_log.txt | awk '{print $1}'`
do 
   echo "kill $line;" >> kill_thread_id.sql
done
#现在kill_thread_id.sql的内容像这个样子
kill 66402982;
kill 66402983;
kill 66402986;
kill 66402991;
.....
mysql shell 中执行
mysql>source kill_thread_id.sql

#一行版
for id in `mysqladmin processlist | grep -i locked | awk '{print $1}'`
do
   mysqladmin kill ${id}
done

未完待续

转载请注明：(●--●) Hello.My Weicot » Magento Mysql 死锁-1205 Lock wait timeout exceeded