【MySQL】DDL因Waiting for table metadata lock卡住
2022/8/31 2:24:01
本文主要是介绍【MySQL】DDL因Waiting for table metadata lock卡住,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
在数据库空闲时间,对表做碎片整理:
alter table my_abc engine=innodb;
发现会话被阻塞,显示状态是:
Waiting for table metadata lock
手动断开alter操作后,通过show processlist查看:
> show processlist; +--------+--------+---------------------+-----------+---------+-------+----------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +--------+--------+---------------------+-----------+---------+-------+----------+------------------+----------+ | 489669 | pig | 119.119.3.221:60226 | pig | Sleep | 4 | | NULL | 0.000 | | 489673 | xxx | 119.119.3.172:41394 | abced_xxx | Sleep | 120 | | NULL | 0.000 | | 498227 | xxx | 119.119.3.172:48916 | abced_xxx | Sleep | 28303 | | NULL | 0.000 | | 502773 | pig | 119.119.3.172:47752 | pig | Sleep | 26139 | | NULL | 0.000 | | 519545 | root | localhost | NULL | Sleep | 15994 | | NULL | 0.000 | +--------+--------+---------------------+-----------+---------+-------+----------+------------------+----------+
可以看到会话都处于sleep状态。但是一旦执行alter操作,就会卡在那里,等待元数据锁。
出现Waiting for table metadata lock这种情况多半是和事务有关,要么是一个长事务在运行,要么是事务没有提交造成的
先查看下是否开启了自动提交:
> show global variables like '%autocommit%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | autocommit | ON | | wsrep_retry_autocommit | 1 | +------------------------+-------+ 2 rows in set (0.001 sec)
接下来,在查看一下是否有事务正在运行:
> select * from information_schema.innodb_trx \G; *************************** 1. row *************************** trx_id: 0 trx_state: RUNNING trx_started: 2022-08-29 10:10:37 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 0 trx_mysql_thread_id: 498227 trx_query: NULL trx_operation_state: trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 0 trx_lock_memory_bytes: 1128 trx_rows_locked: 0 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_is_read_only: 0 trx_autocommit_non_locking: 0 1 row in set (0.001 sec) ERROR: No query specified
从这里看到,的确有一个事务在运行。从上班到下班还没结束。
这里就要找开发同学问问应用到底是在干什么了,解决了这个问题,顺便还解决了最近业务反应有些功能慢的问题。
此外,information_schema.innodb_trx中不会记录执行失败的事务,但是在这个执行失败的事务回滚前,它依然持有metadata lock,所以DDL操作依然会被阻塞。这个时候可以通过查找performance_schema.events_statements_current表来找到相关的语句和会话信息,将其杀死。为了减少metadata lock带来的危害,设置一个合理的lock_wait_timeout比较重要,这个值默认是365天,可以根据自身业务来设置,避免长时间的metadata lock等待。
总结一下:
·长时间运行的事务很危险,需要多关注。
·要关注autocommit是否开启
·使用事务需要小心,记得 commit,捕获异常 rollback
·做DDL操作前先检查一遍innodb_trx
·设置合理的lock_wait_timeout
这篇关于【MySQL】DDL因Waiting for table metadata lock卡住的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-04部署MySQL集群项目实战:新手入门教程
- 2024-11-04如何部署MySQL集群资料:新手入门指南
- 2024-11-02MySQL集群项目实战:新手入门指南
- 2024-11-02初学者指南:部署MySQL集群资料
- 2024-11-01部署MySQL集群教程:新手入门指南
- 2024-11-01如何部署MySQL集群:新手入门教程
- 2024-11-01部署MySQL集群学习:新手入门教程
- 2024-11-01部署MySQL集群入门:新手必读指南
- 2024-10-23BinLog入门:新手必读的MySQL二进制日志指南
- 2024-10-23Binlog入门:MySQL数据库的日志管理指南