Waiting for table metadata lock 问题一例
1,317 total views, 1 views today
今天，开发人员反应MySQL测试库，查询ecs_ugo_goods表长时间不出结果，类似于卡死。在卡死之前，他在ecs_ugo_goods表里新增个字段 。
数据库版本5.6.24 。登录测试库，通过show processlist命令查看连接的状态，存在大量“Waiting for table metadata lock”锁等待。等待的线程都执行了和ecs_ugo_goods 表相关的SQL。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
| 465816 | mysql_master | SL010L-SALESDB:23487 | 17ugo | Sleep | 1358894 | | NULL | | 467480 | mysql_master | SL010L-SALESDB:25991 | 17ugo | Query | 155 | Waiting for table metadata lock | SELECT goods_name,shop_price FROM ecs_ugo_goods WHERE is_on_sale = '1' ORDER BY goods_id DESC LIMIT | | 467481 | mysql_master | SL010L-SALESDB:25992 | 17ugo | Query | 174 | Waiting for table metadata lock | select count(1) from ecs_ugo_goods g,ecs_ugo_goods_ex ex LEFT JOIN app_goods_stock st on ex. | | 467482 | mysql_master | SL010L-SALESDB:25993 | 17ugo | Sleep | 27 | | NULL | | 467483 | mysql_master | SL010L-SALESDB:25994 | 17ugo | Sleep | 27 | | NULL | | 467485 | mysql_master | SL010L-SALESDB:25995 | 17ugo | Sleep | 0 | | NULL | | 467486 | mysql_master | SL010L-SALESDB:25996 | 17ugo | Query | 93 | Waiting for table metadata lock | SELECT o.*, IF(o.product_id > 0, p.product_number, g.goods_number) AS storage, o.goods_attr,p.goods_ | | 467487 | mysql_master | SL010L-SALESDB:25997 | 17ugo | Sleep | 1 | | NULL | | 467488 | mysql_master | SL010L-SALESDB:25998 | 17ugo | Sleep | 27 | | NULL | | 467489 | root | localhost | 17ugo | Query | 0 | init | show processlist | | 467491 | mysql_master | SL010L-SALESDB:25999 | 17ugo | Sleep | 27 | | NULL | +--------+--------------+----------------------+-----------+---------+----------+---------------------------------+------------------------------------------------------------------------------------------------------+ |
查看InnoDB的事务表，发现确实存在正在执行的事务，并且事务开始时间是 2016-12-28 17:15:45 ，这么久没有执行完（应该是未提交），必然有问题，果断杀掉。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G *************************** 1. row *************************** trx_id: 5339553875 trx_state: RUNNING trx_started: 2016-12-28 17:15:45 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 0 trx_mysql_thread_id: 465816 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 0 trx_lock_memory_bytes: 360 trx_rows_locked: 0 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: READ COMMITTED trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 9344 trx_is_read_only: 0 trx_autocommit_non_locking: 0 |
根据trx_mysql_thread_id杀掉这个线程
|
1 |
mysql> kill 465816; |
再次查看InnoDB事务表，开发人员执行增加字段的alter table操作终于出现了，可见是上面那个未完成的事务阻塞了alter table操作。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G *************************** 1. row *************************** trx_id: 5355153325 trx_state: RUNNING trx_started: 2017-01-13 10:47:11 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 0 trx_mysql_thread_id: 467437 trx_query: alter table 17ugo.`ecs_ugo_goods` add limit_detail_id bigint(10) trx_operation_state: NULL trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 0 trx_lock_memory_bytes: 360 trx_rows_locked: 0 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: READ COMMITTED trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 trx_is_read_only: 0 trx_autocommit_non_locking: 0 |
很快alter table操作就执行完了，再次查看InnoDB事务表INNODB_TRX，已经没有事务。通过show processlist命令查看连接的状态，“Waiting for table metadata lock”锁等待都消失了。至此，问题解决。
另外，通过show engine innodb status\G 命令也可以发现异常。最后两行是问题事务，状态ACTIVE，并且已经执行了135840 sec，而在它之前的事务状态是not started，杀掉状态为ACTIVE的事务即可。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
*** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ ..... ---TRANSACTION 5241071501, not started MySQL thread id 1317, OS thread handle 0x7fab702f0700, query id 159584965 SL010L-SALESDB 10.2.5.71 liulaixi cleaning up ---TRANSACTION 5241038334, not started MySQL thread id 1318, OS thread handle 0x7fac6aa70700, query id 159584961 SL010L-SALESDB 10.2.5.71 liulaixi cleaning up ---TRANSACTION 5238579806, not started MySQL thread id 161, OS thread handle 0x7fac72a49700, query id 157631204 SL010L-SALESDB 10.2.5.71 liulaixi cleaning up ---TRANSACTION 0, not started MySQL thread id 160, OS thread handle 0x7fac6af39700, query id 52121718 SL010L-SALESDB 10.2.5.71 liulaixi cleaning up ---TRANSACTION 5339553875, ACTIVE 1358740 sec MySQL thread id 465816, OS thread handle 0x7fac6ac8b700, query id 1584749577 SL010L-SALESDB 10.2.5.71 mysql_master cleaning up |