解决mysql线上发生锁表问题Waiting for table metadata lock(原创)

最新推荐文章于 2025-06-18 14:04:48 发布
最新推荐文章于 2025-06-18 14:04:48 发布
阅读量1.5k 收藏 5
点赞数 3
CC 4.0 BY-SA版权
分类专栏： mysql 文章标签： 锁表 Waiting for table metadata lock
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/mikewuhao/article/details/98625882
本文详细解析了MySQL中因在线添加索引导致的锁表问题，通过复现问题场景，展示了如何使用show processlist和innodb_trx命令定位并解决锁表问题，深入探讨了MDL锁的工作原理及其对业务的影响。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

解决mysql线上发生锁表问题(原创)

问题描述

最近,有同事用navicat打开mysql数据库,给一张表在线添加索引后, 该表一直卡主，打不开, 总在提示"正在加载"。

问题复现

初步断定应该表被锁了。复现当时的情况：
session1：对user表进行修改操作，事务不要commit。
在这里插入图片描述
session2：对user表添加索引。
在这里插入图片描述
这时打开user表，复现了当时情况，一直卡主，打不开, 总在提示"正在加载"。
在这里插入图片描述

排查过程

1 通过show processlist命令，显示用户正在运行的线程情况，此命令可帮助查看当前有问题的查询语句。查看 “stats”字段,出现了"Waiting for table metadata lock"提示表被锁住了。“info”字段显示的是正在执行的sql。
在这里插入图片描述
2 这时候需要查看未提交的事务
select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx命令，找到有问题的线程id。
在这里插入图片描述
3 kill掉trx_mysql_thread_id，将有问题的id或在session 1上commit或者rollback。
再次通过show processlist命令，发现一切正常，user表可以正常打开。

分析原因

如果当前正在执行的sql，是长事务或事务未提交, 同时又进行DDL(CREATE、DROP、ALTER 等)操作。会发生锁表现象。
详情可参考msql的官方文档
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-limitations.html

延伸思考

参考转自:https://cloud.tencent.com/developer/article/1369229
MDL简述
为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁（metadata lock），来保护表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。
对于引入MDL，其主要解决了2个问题，一个是事务隔离问题，比如在可重复隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；另外一个是数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现象。所以在对表进行上述操作时，如果表上有活动事务（未提交或回滚），请求写入的会话会等待在Metadata lock wait 。
支持事务的InnoDB引擎表和不支持事务的MyISAM引擎表，都会出现Metadata Lock Wait等待现象。一旦出现Metadata Lock Wait等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。
常见MDL锁场景
①当前有执行DML操作时执行DDL操作
② 当前有对表的长时间查询或使用mysqldump/mysqlpump时，使用alter会被堵住
③ 显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，DDL会被堵住
④ 表上有失败的查询事务，比如查询不存在的列，语句失败返回，但是事务没有提交，此时DDL仍然会被堵住
myisam、innodb对事务的支持
Myisam是不支持事务的，innodb是支持事务的，这个概念其实没有任何问题，但是这里只的都是对于数据的事务性操作的支持，通过如下简单的实验可以很清楚的理解（关于事务的相关概念和解释就不再赘述了，只是想区别一下mysiam不支持事务，但是主动开始事务中对Myisam的操作仍然会产生MDL锁）

MySQL 元数据锁及问题排查（Metadata Locks MDL）
Vincent的博客
03-05 4389
"元数据"是用来描述数据对象定义的，而元数据锁（Metadata Lock MDL）即是加在这些定义上。通常我们认为非锁定一致性读（简单select）是不加锁的，这个是基于表内数据层面，其依然会对表的元数据加锁，保证读取数据期间表结构不会变更。
Waiting for table metadata lock
youhebuke225的博客
06-20 1221
如果有一个线程持有MDL写锁（或正在等待MDL写锁），并且有很多其他线程在等待MDL读锁（因为它们只是执行查询），那么这可能会导致性能问题，因为所有等待MDL读锁的线程都会被阻塞，直到MDL写锁被释放。时，这通常意味着有一个线程（通常是一个查询）正在等待获取一个表的元数据锁（MDL，Metadata Lock）。MDL锁是MySQL用来确保表的结构在查询过程中不会被其他线程修改的机制。）时，它会请求一个MDL写锁。其他需要读取表结构的线程（如进行。当一个线程需要修改表结构（如使用。
【转】【MySql】Waiting for table metadata lock原因分析
weixin_33889245的博客
11-14 697
MySQL在进行alter table等DDL操作时，有时会出现Waiting for table metadata lock的等待场景。而且，一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态，后续对TableA的任何操作（包括读）都无法进行，因为他们也会在Opening tables的阶段进入到Waiting for tab...
排查MDL锁：“Waiting for table metadata lock“
最新发布
数据库小学生
06-18 245
在performance_schema.set_instruments中MDL锁记录对应的instruments为wait/lock/metadata/sql/mdl，对应的consumers为performance_schema.metadata_locks。之前查到thread_id=37持有锁，我们可以通过如下查询查看thread_id=37的线程在做什么，查询结果显示，thread_id=37的线程在更新表的数据。查看SQL_TEXT字段可以看到具体的SQL文本。数据库版本：5.7.36单机。
MySQL Metadata lock深入
qiuyepiaoling的专栏
06-23 756
原文http://www.sqlparty.com/mysql-metadata-lock%E6%B7%B1%E5%85%A5/ MySQL 5.5.3版本中引入了Metadata lock。在5.5.3版本之前，MySQL事务对于表结构元数据(Metadata)的锁定是语句(statement)粒度的，即语句执行完成后，不管事务是否可以完成，其表结构就可以被其他会话更新掉！而引入Met
【mysql】不可不知的Metadata Lock
weixin_33860722的博客
09-20 321
一、问题发生 说一个现象，当收到服务器报警之后，数据库服务器CPU使用超过90%，通过 show processlist 一看，满屏都是 Waiting for table metadata lock 状态的连接。第一反应就是kill掉这些连接，奈何连接实在太多，实在kill不过来，于是重启服务，重启果真能解决90%的问题，但如果不找到问题原因，问题也肯定会再次出现。 类似于下图（没找到Wai...
MYSQL 持续踩坑之-metadata lock
qq_22351805的博客
02-03 1793
本人博客原地址：MYSQL 持续踩坑之-metadata lock 创作时间： 2019.12.07 16:54:42 起因 最近生产的MySQL数据库出现锁表的情况，通过show processlist;发现下图状态（模拟）waiting for table metadata lock 在metadata lock之前，先大致回顾下MySQL的锁 从锁的粒度和范围，大致分三类：全局锁，表锁，行锁。 共享锁S，与排它锁X均为行锁。innodb支持对更粗粒度（数据库级，表级，页级）加意向锁。MYSQL意
MySQL出现Waiting for table metadata lock的原因方法
12-16
MySQL在进行alter table等DDL操作时，有时会出现Waiting for table metadata lock的等待场景。而且，一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态，后续对TableA的任何操作（包括读）...
Waiting for table metadata lock mysql出现此错误解决办法
欧神的博客
09-11 7208
出现这种情况的说明,我是需要去修改表的结构,但是一修改,就直接卡死,然后就会报 Waiting for table metadata lock 这个错. 实际上,这个错不止是修改表结构会出现,你对这张表的任务锁表操作,都将不能实现. 解决方法 一 : 首先排除所有的长事物,慢查询等阻塞ddl,这类操作,会阻塞后续的操作 使用 show processlist 可以看到 一个列表 ,在这个列表里面...
metadata lock的解决方案
weixin_34163741的博客
09-03 1164
文章https://yq.aliyun.com/articles/175039?spm=5176.100239.0.0.jNFOsC 提出了会发生Metadata-lock场景以及如何在数据库运维方面避免产生的建议，但是其实5.6/5.7版本已经提供了一种直接定位该问题的方法：启用performance_schema功能，5.7更是针对Metadata-...
MySQL Metadata Lock详解
weixin_34111790的博客
03-16 216
  Metadata Lock 的作用： 　　要直接说出Metadata Lock 的作用、以我目前的文字功底是不行的、好在我可以通过一个例子来说明。 　　假设session 1 在正在执行如下的SQL语句 select * from t ; 　　session 2 想要执行  drop table t; 　　drop table t ; 这句SQL是不能得到马上执行的、因为session...
MySQL的MDL （元数据锁) Waiting for table metadata lock
shunnianlv的博客
05-08 376
问题： 对表t1进行查询操作一直显示sending data 中，导致后续对t1表的所有操作select、insert、update、delete全部阻塞！！ MDL：Metadata Locking，元数据锁 MySQL uses metadata locking to manage concurrent access to database objects and to ensure dat...
mysql锁表，Waiting for table metadata lock
deoppressoliber的博客
04-02 246
https://www.cnblogs.com/digdeep/p/4892953.html show processlist; select * from information_schema.innodb_trx kill sid
Waiting for table metadata lock(mysql lock)
zjfgf的专栏
08-27 135
Waiting for table metadata lock. why? Reason: 8.10.4. Metadata Locking Within Transactions To ensure transaction serializability, the server must not permit one session to perform ...
（mysql）Waiting for table metadata lock
zhangjunli的博客
01-07 5413
Waiting for table metadata lock
数据库故障Waiting for table metadata lock
weixin_54104864的博客
12-19 881
调整隔离级别参数lower_isolation_level,将数据库隔离级别调低,使一些读操作可以忽略锁,从而减少锁表概率。编辑Deployment,移除pod模板中的标签,如app=myapp。命令查看是否有锁表现象发生,分析找出发生锁表的session进程。所以通过组合使用上述方法,可以完全删除或停止Namespace和Deployment中的Pod。对于主从数据库,可以kill主库sessionId后使请求被转移到从库上,避开主库上的锁。注意提前检查需要KILL的连接,避免影响正处理重要任务的连接。
排查Waiting for table metadata lock问题
六月闻君
11-01 800
MySQL的系统库表有数据维护对应的信息，就在information_schema库中的INNODB_TRX表，包含事务中是否存在锁，事务开启时间，事务执行的语句等等。查看执行完成，但未commit的sql语句 events_statements_current。trx_mysql_thread_id 字段的MySQL的sid 对应3个查询语句。MySQL8版本，一个表41万条数据，创建索引时间长，未成功。查询到有3条select 查询在锁状态。再检查，没有锁定的用户select。
mysql数据库表新增字段一直Waiting for table metadata lock
02-20
### 解决 MySQL 数据库中添加新字段时遇到的元数据锁等待问题 当尝试对表结构进行修改（如 `ALTER TABLE` 操作）时，如果其他会话正在访问同一张表，则可能会遇到 `Waiting for table metadata lock` 的情况。这种情况下，新的 DDL 请求会被阻塞直到现有的读写操作完成。 #### 识别并终止长时间运行的查询或事务 对于那些持有元数据锁定超过预期时间的情况，可以通过监控当前活动进程列表来查找可能引起冲突的操作： ```sql SHOW FULL PROCESSLIST; ``` 一旦发现可疑条目，特别是处于 `Sleep` 或者执行状态较久的任务，可以考虑安全地中止它们以解除潜在的竞争条件： ```sql KILL QUERY thread_id; -- 只杀死特定查询而不影响整个连接 -- 或者 KILL CONNECTION thread_id; -- 终止整个客户端会话 ``` 这里需要注意的是，在采取行动前应当确认目标确实造成了不必要的延迟，并评估中断的影响[^1]。 #### 缩短锁等待超时设置 为了防止应用程序因长时间未能获得所需资源而陷入无限期挂起的状态，默认配置下的 `lock_wait_timeout` 参数被设定得非常大（即一年）。可以根据实际需求适当降低此参数值以便更快地触发错误提示而非无休止地等待： ```sql SET GLOBAL lock_wait_timeout = 1800; -- 设置全局变量为半小时 ``` 不过这样做仅能改变后续请求的行为模式；对于已经发生的堵塞现象并无即时帮助[^4]。 #### 避免长事务的存在 确保所有涉及多步更新逻辑的地方都能尽快提交更改，减少并发期间内保持活跃事务的可能性。这不仅有助于缓解由共享锁升级带来的压力，也能间接改善整体性能表现。具体做法包括但不限于优化 SQL 语句效率、批量处理小批次的数据变更等措施[^5]。 #### 使用在线 DDL 功能 自 MySQL 5.6 版本以后引入了 Online DDL 支持，使得某些类型的架构调整能够在不影响正常业务流程的前提下顺利完成。例如增加列这样的简单变动通常都可以无缝实施，前提是所使用的存储引擎支持此类特性并且遵循官方文档给出的最佳实践建议[^2]。 ```sql ALTER TABLE your_table ADD COLUMN new_column INT NOT NULL DEFAULT 0 ALGORITHM=INPLACE, LOCK=NONE; ``` 以上命令试图采用最高效的方式执行指定转换工作，同时尽可能维持对外服务不中断。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值