![MySQL 临时表插入挂起并锁定其他查询](https://linux22.com/image/759702/MySQL%20%E4%B8%B4%E6%97%B6%E8%A1%A8%E6%8F%92%E5%85%A5%E6%8C%82%E8%B5%B7%E5%B9%B6%E9%94%81%E5%AE%9A%E5%85%B6%E4%BB%96%E6%9F%A5%E8%AF%A2.png)
我有一个 MySQL 5.7.30 作为 Magento 2 商店的存储。在 magento 重新索引过程(将数据转换为各种表以便更快地检索的 PHP 过程)期间,会创建并填充一些临时表。这些查询应该非常快,但是,有些查询会无限期地运行。这会导致其他查询挂起,因为它们正在等待插入正在读取的表的表锁。这会导致整个网站瘫痪,因为没有查询会完成,从而导致 Web 前端出现超时错误。
即使终止了重新索引进程,情况没有改变。插入查询仍在运行,其他查询正在等待表锁 - 直到我在 MySQL 中终止该查询。
以下是此类死锁情况(运行重新索引进程时发生)期间 MySQL 中的进程列表:
mysql> select id, db,command, state, left(info,100) from information_schema.processlist;
+---------+----------+---------+---------------------------------+------------------------------------------------------------------------------------------------------+
| id | db | command | state | left(info,100) |
+---------+----------+---------+---------------------------------+------------------------------------------------------------------------------------------------------+
| 8165312 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_bb745c5e7f2610352c060b36709e54032aaf8c15` SELECT `e`.`entity_id` FROM `cata |
| 8165311 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_f91821f67870f011d3f8a38e9908c3235747584b` SELECT `e`.`entity_id` FROM `cata |
| 8165289 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_2d834f92e9b71a91a5c2520192539cd38560d22c` SELECT `e`.`entity_id` FROM `cata |
| 8165288 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_abca7e583ce39ed454be82b78bdc29b389a4ee74` SELECT `e`.`entity_id` FROM `cata |
| 8165260 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_18b43873915bfb0015033be4ee261cae3e897dee` SELECT `e`.`entity_id` FROM `cata |
| 8165244 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_d661c3a49566eb6a35f5043718ae99047b176662` SELECT `e`.`entity_id` FROM `cata |
| 8165217 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_da8bbd709684db07102be86caeae8712790bb37e` SELECT `e`.`entity_id` FROM `cata |
| 8165261 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_dd977f7939670fc3eb19c6b5c89be77b8bba52b1` SELECT `e`.`entity_id` FROM `cata |
| 8165259 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_28d513e50adac3dd498f9d4087f648cf73ad8e9d` SELECT `e`.`entity_id` FROM `cata |
| 8165199 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_438c79baa1470f9f4e435b50046e5f65c06c58dc` SELECT `e`.`entity_id` FROM `cata |
| 8165153 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_a12ea76415ddb9a0179ed2dbdf48b88b9b3132e6` SELECT `e`.`entity_id` FROM `cata |
| 8165020 | magento2 | Query | Waiting for table metadata lock | RENAME TABLE catalog_product_index_price TO catalog_product_index_price_outdated,catalog_product_ind |
| 8165251 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_32a2d91876b5b4fa38eb6f3773074ad9d4854390` SELECT `e`.`entity_id` FROM `cata |
| 8165247 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_be2127ec0bdd7a56beb8e59fdc5a5a1164ef3bbb` SELECT `e`.`entity_id` FROM `cata |
| 8165017 | magento2 | Sleep | | NULL |
| 8164957 | magento2 | Query | Filling schema table | select id, db,command, state, left(info,100) from information_schema.processlist |
| 8165240 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_1ca9188823f3af39434548117fde49aacd9cf7c1` SELECT `e`.`entity_id` FROM `cata |
| 8165226 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_94f703629874d7a4b20c58a4d863266d1f7f6ba4` SELECT `e`.`entity_id` FROM `cata |
| 8165219 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_0e88fc675d33b150e9cc7718cb70820ca88bc80c` SELECT `e`.`entity_id` FROM `cata |
| 8165210 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_b6f322baae91918f8b91001156a149072e904f41` SELECT `e`.`entity_id` FROM `cata |
| 8165206 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_e62016ea2ea8631932f1dd5643c93d81922c4949` SELECT `e`.`entity_id` FROM `cata |
| 8165194 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_e69208892fe0386e6baff43b2ff244d49fa6eb4c` SELECT `e`.`entity_id` FROM `cata |
| 8165202 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_3a805f9691560f0b892b4da18c0041277ce1b99e` SELECT `e`.`entity_id` FROM `cata |
| 8165188 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_c97916867bdb1e1427abeac76cee57a164038c50` SELECT `e`.`entity_id` FROM `cata |
| 8165179 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_2b4ee30f3f92d09e3843e9b6a65f31751d180770` SELECT `e`.`entity_id` FROM `cata |
| 8165173 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_860d16fe274ea842d23c9bd73c74fef4abf5ebc4` SELECT `e`.`entity_id` FROM `cata |
| 8165167 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_a963adc29cdca3c797b041e777010031d3eb5fc6` SELECT `e`.`entity_id` FROM `cata |
| 8165155 | magento2 | Query | Waiting for table metadata lock | INSERT IGNORE INTO `temp_895b1ef14a24275b53c171d0d2bb5cc94c1ae4b1` SELECT `e`.`entity_id` FROM `cata |
| 8165147 | magento2 | Query | Sending data | INSERT IGNORE INTO `temp_cb95277cd601e038246f880aca3c4f1113f61a70` SELECT `e`.`entity_id` FROM `cata |
| 8165142 | magento2 | Query | Sending data | INSERT IGNORE INTO `temp_dee736e9ad4f2d069f5750cd5411f566775e42cb` SELECT `e`.`entity_id` FROM `cata |
+---------+----------+---------+---------------------------------+------------------------------------------------------------------------------------------------------+
如您所见,目前有 2 个查询正在将数据插入到 temp_XXX 表中。其他查询正在等待锁定。在这种情况下,只有索引器在运行,因为网站处于维护状态。在网站可访问的情况下,还会有其他查询(例如选择产品)等待表锁定。
终止重新索引进程后(通过Ctrl+C
在运行它的终端上),情况如下:
mysql> select id, db,command, state, left(info,100) from information_schema.processlist;
+---------+----------+---------+----------------------+------------------------------------------------------------------------------------------------------+
| id | db | command | state | left(info,100) |
+---------+----------+---------+----------------------+------------------------------------------------------------------------------------------------------+
| 8165147 | magento2 | Query | Sending data | INSERT IGNORE INTO `temp_cb95277cd601e038246f880aca3c4f1113f61a70` SELECT `e`.`entity_id` FROM `cata |
| 8165142 | magento2 | Query | Sending data | INSERT IGNORE INTO `temp_dee736e9ad4f2d069f5750cd5411f566775e42cb` SELECT `e`.`entity_id` FROM `cata |
| 8164957 | magento2 | Query | Filling schema table | select id, db,command, state, left(info,100) from information_schema.processlist |
+---------+----------+---------+----------------------+------------------------------------------------------------------------------------------------------+
可以看到,之前的两条语句仍然在运行。
具体表述如下:
INSERT IGNORE INTO `temp_cb95277cd601e038246f880aca3c4f1113f61a70` SELECT `e`.`entity_id` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index_store3` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=3 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=17036
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id
INNER JOIN `catalog_product_index_eav` AS `layered_nav_f_filter_typ_1` ON `layered_nav_f_filter_typ_1`.`entity_id` = `e`.`entity_id` AND `layered_nav_f_filter_typ_1`.`attribute_id` = '18249' AND `layered_nav_f_filter_typ_1`.`store_id` = '3' AND `layered_nav_f_filter_typ_1`.`value` IN (2894)
INNER JOIN `cataloginventory_stock_status` AS `layered_nav_f_filter_typ_1__stock` ON `layered_nav_f_filter_typ_1`.`source_id` = `layered_nav_f_filter_typ_1__stock`.`product_id` AND `layered_nav_f_filter_typ_1__stock`.`website_id` = 0 AND `layered_nav_f_filter_typ_1__stock`.`stock_id` = 1 AND `layered_nav_f_filter_typ_1__stock`.`stock_status` = 1
INNER JOIN `catalog_product_index_eav` AS `layered_nav_f_filter_kabelmantel` ON `layered_nav_f_filter_kabelmantel`.`entity_id` = `e`.`entity_id` AND `layered_nav_f_filter_kabelmantel`.`attribute_id` = '18253' AND `layered_nav_f_filter_kabelmantel`.`store_id` = '3' AND `layered_nav_f_filter_kabelmantel`.`value` IN (3044)
INNER JOIN `cataloginventory_stock_status` AS `layered_nav_f_filter_kabelmantel__stock` ON `layered_nav_f_filter_kabelmantel`.`source_id` = `layered_nav_f_filter_kabelmantel__stock`.`product_id` AND `layered_nav_f_filter_kabelmantel__stock`.`website_id` = 0 AND `layered_nav_f_filter_kabelmantel__stock`.`stock_id` = 1 AND `layered_nav_f_filter_kabelmantel__stock`.`stock_status` = 1
INNER JOIN `catalog_product_index_eav` AS `layered_nav_f_filter_paarzahl_1` ON `layered_nav_f_filter_paarzahl_1`.`entity_id` = `e`.`entity_id` AND `layered_nav_f_filter_paarzahl_1`.`attribute_id` = '18251' AND `layered_nav_f_filter_paarzahl_1`.`store_id` = '3' AND `layered_nav_f_filter_paarzahl_1`.`value` IN (3009,3004)
INNER JOIN `cataloginventory_stock_status` AS `layered_nav_f_filter_paarzahl_1__stock` ON `layered_nav_f_filter_paarzahl_1`.`source_id` = `layered_nav_f_filter_paarzahl_1__stock`.`product_id` AND `layered_nav_f_filter_paarzahl_1__stock`.`website_id` = 0 AND `layered_nav_f_filter_paarzahl_1__stock`.`stock_id` = 1 AND `layered_nav_f_filter_paarzahl_1__stock`.`stock_status` = 1 WHERE (stock_status_index.stock_status = 1)
此插入的基本选择花费 0.0 毫秒并返回 ~1000 条记录。
当这些查询仍在运行时,其他查询继续挂起,等待表锁。只有在我KILL
为这些插入发出语句后,网站才恢复正常运行。
还请注意,这种情况并非每次都会发生。有时(少数尝试),语句可以顺利完成。
注意:这似乎只发生在股票和价格表的重新索引中。所有表的表引擎都是 InnoDB。
价格表结构:
CREATE TABLE `catalog_product_index_price` (
`entity_id` int(10) unsigned NOT NULL COMMENT 'Entity ID',
`customer_group_id` int(10) unsigned NOT NULL COMMENT 'Customer Group ID',
`website_id` smallint(5) unsigned NOT NULL COMMENT 'Website ID',
`tax_class_id` smallint(5) unsigned DEFAULT 0 COMMENT 'Tax Class ID',
`price` decimal(20,6) DEFAULT NULL COMMENT 'Price',
`final_price` decimal(20,6) DEFAULT NULL COMMENT 'Final Price',
`min_price` decimal(20,6) DEFAULT NULL COMMENT 'Min Price',
`max_price` decimal(20,6) DEFAULT NULL COMMENT 'Max Price',
`tier_price` decimal(20,6) DEFAULT NULL COMMENT 'Tier Price',
PRIMARY KEY (`entity_id`,`customer_group_id`,`website_id`),
KEY `CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID` (`customer_group_id`),
KEY `CATALOG_PRODUCT_INDEX_PRICE_MIN_PRICE` (`min_price`),
KEY `CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE` (`website_id`,`customer_group_id`,`min_price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Product Price Index Table'
表变量包括:
mysql> show variables like '%table%';
+----------------------------------------+----------------------+
| Variable_name | Value |
+----------------------------------------+----------------------+
| aria_encrypt_tables | OFF |
| aria_used_for_temp_tables | ON |
| big_tables | OFF |
| encrypt_tmp_disk_tables | OFF |
| innodb_encrypt_tables | OFF |
| innodb_encrypt_temporary_tables | OFF |
| innodb_file_per_table | ON |
| innodb_ft_aux_table | |
| innodb_ft_server_stopword_table | |
| innodb_ft_user_stopword_table | |
| innodb_table_locks | ON |
| innodb_undo_tablespaces | 0 |
| lower_case_table_names | 0 |
| max_heap_table_size | 268435456 |
| max_tmp_tables | 32 |
| old_alter_table | DEFAULT |
| performance_schema_max_table_handles | -1 |
| performance_schema_max_table_instances | -1 |
| replicate_do_table | |
| replicate_ignore_table | |
| replicate_wild_do_table | |
| replicate_wild_ignore_table | |
| table_definition_cache | 400 |
| table_open_cache | 1024 |
| table_open_cache_instances | 7 |
| tmp_disk_table_size | 18446744073709551615 |
| tmp_memory_table_size | 268435456 |
| tmp_table_size | 268435456 |
| updatable_views_with_limit | YES |
| use_stat_tables | NEVER |
+----------------------------------------+----------------------+
30 rows in set (0.00 sec)
问题:查询从表中读取数据并插入(专门为此进程创建的临时表)如何导致其他读取无限期挂起?启动查询的进程被终止后,此查询如何继续运行?
最重要的是:我该如何解决这种情况?