关于Mysql表结构的元数据锁

1.表被阻塞的关键

SHOW FULL PROCESSLIST; 命令在 MySQL 中用于查看当前所有连接的状态和活动。如果你看到状态为 Waiting for table metadata lock，意味着该连接正在等待获取表的元数据锁（Metadata Lock，简称 MDL）。这种情况通常发生在数据库的某些操作（如 ALTER TABLE、DROP TABLE、CREATE INDEX）正在执行，并且它们需要对表结构进行更改时，其他连接就会被阻塞，直到表的元数据锁被释放。

2.造成原因

在 MySQL 5.5 及更高版本中，元数据锁（MDL）机制用于保护数据库对象的元数据不被修改，以确保查询的一致性。当进行对表结构的修改操作时，MySQL 会自动对表的元数据加锁。其他需要访问该表的查询（尤其是 SELECT 或 INSERT 等操作）会被阻塞，直到该锁被释放。

3.可能触发 Waiting for table metadata lock 的操作

3.1 表结构修改

• ALTER TABLE
• CREATE INDEX
• DROP INDEX
• RENAME TABLE
• TRUNCATE TABLE

3.2 长时间运行的事务

• 如果一个事务长时间持有对表的锁（如 LOCK TABLE），其他连接可能会被阻塞。

3.2 其他阻塞查询

• 查询（如 SELECT）长时间等待，因为表正在进行一些结构性更改，导致它们处于等待状态。

4.排查步骤

4.1 查看当前的查询和锁定情况

使用 SHOW FULL PROCESSLIST; 可以查看所有连接的状态和执行的查询。例如：

SHOW FULL PROCESSLIST;

其中，State 列会显示 Waiting for table metadata lock，表明该查询正被阻塞。Info 列会显示正在执行的查询。

在这个例子中，连接 Id 为 5 的查询正在等待表 dispatch_request_log 的元数据锁，而连接 Id 为 6 的查询正在执行 ALTER TABLE 操作。

4.2 查看哪些查询持有锁

查看执行修改表结构的查询，这些查询会持有元数据锁。你可以通过 SHOW FULL PROCESSLIST 查看哪些查询正在修改表，通常这些查询会阻塞其他需要访问表的查询。

4.3 杀死阻塞的查询

KILL 6;

这将终止 Id 为 6 的查询，并释放它持有的元数据锁。

4.4 查看是否存在表结构修改的事务

SHOW ENGINE INNODB STATUS;

这将输出当前 InnoDB 存储引擎的状态信息，包括锁的详细信息。你可以在输出中查找是否存在阻塞的事务。

5.解决方法

5.1 避免频繁的表结构修改

• 尽量避免在高并发的环境中频繁进行表结构修改操作。如果必须修改表结构，可以选择在低峰期进行，减少对其他查询的影响。

5.2 优化查询

• 如果查询需要访问被锁定的表，可以考虑优化查询，减少对表的依赖。例如，可以考虑将查询分拆，或者利用其他缓存机制（如应用层缓存）来减少查询对数据库的负担。

5.3 调整锁的粒度和超时设置

• MySQL 5.7 及更高版本允许通过设置 metadata_locks_cache_size 参数来调整元数据锁缓存的大小。你可以适当调整这些参数以优化表锁的管理。
• 另外，还可以设置 innodb_lock_wait_timeout 来控制查询等待锁的超时时间。默认值通常为 50 秒，你可以调整它来避免查询等待时间过长。

SET GLOBAL innodb_lock_wait_timeout = 30;

5.4 避免在事务中持有表锁

• 尽量避免在事务中执行表结构修改操作，特别是涉及到长时间运行的事务。长时间持有表锁会导致其他查询长时间等待。

5.5 数据库升级

• 如果你使用的 MySQL 版本较老，考虑升级到较新的版本，因为新的版本对元数据锁的管理和性能进行了改进。

6.总结

Waiting for table metadata lock 是 MySQL 中常见的阻塞情况，通常发生在表结构正在被修改时。要解决这个问题，可以通过优化查询、减少表结构修改、适当调整锁等待超时时间和查看阻塞的查询来改善性能