MySQL RR隔离下USE DB堵塞深度剖析与解决方案

在MySQL数据库中，遇到USE DB（切换数据库）操作被阻塞的情况是一种常见的生产环境中可能遇到的问题。这种情况通常发生在高并发或者特定隔离级别下，如RR（Repeatable Read，可重复读）和GitD（Generalized Invisible Transaction Duration，通用不可见事务持续时间）被关闭时。当用户试图使用USE命令切换到某个数据库时，可能会遇到"Waiting for table metadata lock"的错误，导致无法正常访问数据库或获取表信息。 在案例中，用户的MySQL环境为5.6.16，使用了RR隔离级别，且GitD功能被禁用。问题的具体表现为：无法执行USE DB命令，SHOW TABLE STATUS查询失败，以及schema.processlist中大量线程处于等待table metadata lock状态。在尝试解决故障的过程中，简单的线程清理并不能解决问题，最终通过强制结束未及时提交的事务，才得以恢复正常。 该故障的根源在于，当CREATE TABLE AS SELECT B语句正在发送数据（STATE为sendingdata），DROP TABLE A请求在等待table metadata lock，以及SELECT和SHOW TABLE STATUS等涉及元数据操作的SQL语句也处于相同的等待状态时，MDL（Metadata Lock）资源被大量占用。MDL锁是MySQL用于保护数据库结构（如表、索引、视图等）不被并发修改的关键机制。 为了深入了解和分析这个问题，首先要理解MDL锁的工作原理和不同类型的锁定。MDL锁包括DDL（Data Definition Language，数据定义语言）锁，如CREATE和DROP TABLE操作需要的锁；以及DDL+（Data Definition Language Plus，数据定义语言加）锁，例如CREATE TABLE AS SELECT会涉及到的数据加载过程。 对于MDL锁的分析，可以通过以下方法进行： 1. 在源代码层面，对MDL加锁函数添加日志输出，这有助于追踪特定语句何时获得和释放锁，但由于MDL加锁的瞬时性，性能_schema.metadata_locks可能捕捉不到全部信息。 2. 使用MySQL 5.7版本的performance_schema.metadata_locks监控工具，在数据库堵塞状态下，这个特性能提供更详尽的锁信息，帮助识别问题所在。 针对CREATE TABLE AS SELECT B这种发送数据的状态，它实际上代表了正在进行一个数据复制的过程，如果没有正确管理并发，就可能导致锁定冲突，从而阻塞其他操作。 总结来说，解决USE DB堵塞问题的关键在于理解和优化SQL语句的执行策略，确保在并发环境下正确地管理DDL和DDL+锁的获取与释放，避免长时间占用MDL资源。同时，根据具体环境和隔离级别的设置，调整事务处理和锁模式，以提高数据库的整体性能和稳定性。