如何解决RDS for MySQL元数据锁MDL导致无法操作数据库的问题？

问题背景

在 MySQL 数据库中，元数据锁（Metadata Lock, MDL）是为了保证元数据的一致性而引入的一种表级锁，从 MySQL 5.5 版本开始，MDL 用于解决 DDL 操作与 DML 操作之间的一致性问题，在某些情况下，MDL 会导致后续对表的操作被阻塞，进而影响数据库的正常操作。

常见场景

1、创建、删除索引：当执行CREATE INDEX 或DROP INDEX 时，可能会触发 MDL。

2、修改表结构：如ALTER TABLE 操作。

3、表维护操作：如OPTIMIZE TABLE 和REPAIR TABLE。

4、获取表级写锁：某些情况下，需要获取表级的写锁。

解决方法

1. 检查并终止阻塞会话

通过 DMS 工具登录到 RDS for MySQL 数据库，然后执行以下 SQL 命令查看数据库所有线程的状态：

SHOW FULL PROCESSLIST;

查找等待状态：查看State 列是否存在大量Waiting for table metadata lock，这表明出现了 MDL 锁阻塞。

查找阻塞会话：根据Info 列找到对应的表操作，记录会话 ID。

终止阻塞会话：使用KILL 命令终止阻塞会话，例如KILL 267。

2. 优化建议

为了避免 MDL 锁阻塞的发生，可以采取以下优化措施：

1、开启自动提交：确保事务及时提交或回滚，避免长时间占用 MDL 锁。

2、设置锁等待超时时间：将参数lock_wait_timeout 设置为较小的值，使阻塞端主动停止。

3、业务低峰期操作：尽量在业务低峰期进行 DDL 操作及备份等操作。

4、事件终止长时间运行的事务：创建一个事件，定期终止执行时间超过设定阈值的事务。

   CREATE EVENT my_long_running_trx_monitor
   ON SCHEDULE every 60 minute
   STARTS '2015-09-15 11:00:00' ON COMPLETION PRESERVE ENABLE DO
   BEGIN
     DECLARE v_sql varchar(500);
     DECLARE no_more_long_running_trx integer default 0;
     DECLARE c_tid cursor FOR
       SELECT concat('kill ', trx_mysql_thread_id)
       FROM information_schema.innodb_trx
       WHERE timestampdiff(minute, trx_started, now()) > 60;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_long_running_trx = 1;
     OPEN c_tid;
     repeat
       FETCH c_tid INTO v_sql;
       IF no_more_long_running_trx = 0 THEN
         PREPARE stmt FROM v_sql;
         EXECUTE stmt;
         DECLARE done INT;
         DECLARE cur CURSOR FOR select * from information_schema.innodb_trx where trx_mysql_thread_id = connection_id();
         DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
         OPEN cur;
         read_loop: LOOP
           FETCH cur INTO @v_sql;
           IF done = 1 THEWN
             LEAVE read_loop;
           END IF;
         END LOOP;
         CLOSE cur;
       END IF;
     UNTIL no_more_long_running_trx = 1;
     CLOSE c_tid;
   END;

MDL 锁虽然保证了元数据的一致性，但在某些场景下可能会导致数据库操作阻塞，通过合理的优化措施和监控手段，可以有效减少 MDL 锁阻塞的发生，提高数据库的稳定性和性能。