MySQL RDS执行ALTER TABLE加外键时元数据锁阻塞全量查询求助

核心原因 #

你遇到的问题本质是元数据锁（MDL）的等待阻塞：即使是只读SELECT语句，只要处于未提交事务中，就会持有对应表的MDL读锁；而添加外键的ALTER TABLE操作需要获取表A和表B的MDL写锁，写锁会等待所有读锁释放。至于无关表的SELECT也被阻塞，大概率是因为这些查询所在的事务已持有其他表的MDL锁，或是客户端工具关闭了autocommit，导致新查询都进入同一个未提交事务，被MDL锁队列阻塞。

排查步骤 #

1. 检查活跃未提交事务
   执行以下SQL查看所有未提交的事务：

   SELECT trx_id, trx_started, trx_state, trx_query 
   FROM INFORMATION_SCHEMA.INNODB_TRX;
   

   重点关注启动时间早、状态为RUNNING或LOCK WAIT的事务，尤其是涉及表A/B的，或是无明确trx_query的空事务（可能是客户端开启事务后未提交）。

2. 查看MDL锁持有与等待情况
   执行以下SQL获取当前MDL锁的详情：

   SELECT 
     OBJECT_SCHEMA, OBJECT_NAME, 
     LOCK_TYPE, LOCK_STATUS, 
     PROCESSLIST_ID, PROCESSLIST_INFO
   FROM INFORMATION_SCHEMA.METADATA_LOCKS;
   

   这里能直接看到哪个进程持有了表A/B的MDL读锁，以及你的ALTER TABLE进程在等待什么锁。

3. 验证autocommit配置
   检查当前会话和全局的autocommit状态：

   SELECT @@autocommit, @@GLOBAL.autocommit;
   

   如果@@autocommit=0，意味着每个SELECT都会开启一个事务，除非手动执行COMMIT或ROLLBACK，这些未提交的事务会一直持有MDL读锁。

解决方案 #

1. 终止未提交的长事务
   找到阻塞的进程ID（PROCESSLIST_ID）后，执行KILL命令终止：

   KILL [PROCESSLIST_ID];
   

   注意：终止前要确认该事务没有执行关键业务操作。

2. 确保autocommit开启
   如果是客户端工具导致autocommit关闭，要么在工具中开启自动提交，要么每次查询后手动执行COMMIT。也可以全局开启：

   SET GLOBAL autocommit=1;
   

3. 优化ALTER TABLE操作
   对于空表B添加外键，可指定ALGORITHM=INPLACE减少锁开销（MySQL 8.0.14+支持）：

   ALTER TABLE B 
   ADD CONSTRAINT fk_b_product_id FOREIGN KEY (product_id) REFERENCES A(id)
   ALGORITHM=INPLACE;
   

   该算法无需复制整张表，能更快获取MDL写锁。

4. 低峰期执行DDL
   尽量在业务流量最低的时段执行ALTER TABLE，减少被未提交事务阻塞的概率。

内容的提问来源于stack exchange，提问作者GAURAV KUMAR JHA