Aurora MySQL 5.7执行Spring自动Schema变更遇元数据锁等待问题求助

环境与现象 #

使用Aurora MySQL 5.7.mysql_aurora.2.10.0，通过Java Spring框架的schema.sql + spring.sql.init.mode=always配置自动执行数据库结构变更（如CREATE TABLE IF NOT EXISTS、ALTER TABLE）。运行一段时间后数据库出现Waiting for table metadata lock错误，导致Kubernetes Pod因CPU使用率超限崩溃。但相同代码对接GCP BigQuery时无此问题。

schema.sql内容 #

CREATE TABLE IF NOT EXISTS job_detail (
  job_id varchar(50) NOT NULL,
  created_by varchar(100) DEFAULT NULL,
  dataflow_id varchar(50) DEFAULT NULL,
  periodic_tasks longtext,
  error_details longtext DEFAULT NULL,
  end_date datetime DEFAULT NULL,
  executed_date datetime DEFAULT NULL,
  failed_records longtext,
  total_records int(11) DEFAULT 0,
  created_date datetime DEFAULT NULL,
  updated_by varchar(100) DEFAULT NULL,
  updated_date datetime DEFAULT NULL,
  job_status varchar(100) DEFAULT NULL,
  job_summary longtext,
  job_name varchar(100) DEFAULT NULL,
  project_id varchar(50) NOT NULL,
  PRIMARY KEY (job_id)
);

ALTER TABLE  job_detail CHANGE COLUMN created_by created_by VARCHAR(255) NULL DEFAULT NULL;
ALTER TABLE  job_detail CHANGE COLUMN updated_by updated_by VARCHAR(255) NULL DEFAULT NULL;

原因分析 #

1. 数据库锁机制差异：MySQL（含Aurora）的DDL操作需要获取元数据表锁，若此时有未提交的读写事务占用该表，DDL会进入等待队列。大量等待线程会持续占用CPU资源，最终导致Pod崩溃。而BigQuery是无锁架构的数据仓库，DDL逻辑不依赖元数据锁，因此无此问题。
2. 重复执行不必要的DDL：spring.sql.init.mode=always会在每次服务启动时执行全部schema脚本，即便表结构已经符合要求，两次ALTER TABLE仍会触发元数据锁检查，大幅提升冲突概率。
3. 并发场景冲突：多实例部署时多个Pod同时执行DDL，或业务运行中有未及时提交的事务，都会触发元数据锁等待。

解决方案 #

1. 优化schema.sql，避免重复执行DDL #

直接在建表时设置正确的列长度，去掉重复的ALTER操作：

CREATE TABLE IF NOT EXISTS job_detail (
  job_id varchar(50) NOT NULL,
  created_by varchar(255) DEFAULT NULL, -- 直接设为目标长度
  dataflow_id varchar(50) DEFAULT NULL,
  periodic_tasks longtext,
  error_details longtext DEFAULT NULL,
  end_date datetime DEFAULT NULL,
  executed_date datetime DEFAULT NULL,
  failed_records longtext,
  total_records int(11) DEFAULT 0,
  created_date datetime DEFAULT NULL,
  updated_by varchar(255) DEFAULT NULL, -- 直接设为目标长度
  updated_date datetime DEFAULT NULL,
  job_status varchar(100) DEFAULT NULL,
  job_summary longtext,
  job_name varchar(100) DEFAULT NULL,
  project_id varchar(50) NOT NULL,
  PRIMARY KEY (job_id)
);

如果需要兼容历史表结构，可通过查询information_schema判断后再执行ALTER（需用存储过程或Java代码逻辑实现）：

-- 示例存储过程逻辑（需在脚本中创建并执行）
DELIMITER //
CREATE PROCEDURE adjust_job_detail_columns()
BEGIN
    DECLARE created_by_len INT;
    DECLARE updated_by_len INT;
    
    SELECT CHARACTER_MAXIMUM_LENGTH INTO created_by_len
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'job_detail' AND COLUMN_NAME = 'created_by';
    
    IF created_by_len = 100 THEN
        ALTER TABLE job_detail CHANGE COLUMN created_by created_by VARCHAR(255) NULL DEFAULT NULL;
    END IF;
    
    SELECT CHARACTER_MAXIMUM_LENGTH INTO updated_by_len
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'job_detail' AND COLUMN_NAME = 'updated_by';
    
    IF updated_by_len = 100 THEN
        ALTER TABLE job_detail CHANGE COLUMN updated_by updated_by VARCHAR(255) NULL DEFAULT NULL;
    END IF;
END //
DELIMITER ;

CALL adjust_job_detail_columns();
DROP PROCEDURE IF EXISTS adjust_job_detail_columns;

2. 改用版本化数据库迁移工具 #

放弃原生schema.sql的always模式，使用Liquibase或Flyway：

• 这类工具会记录已执行的迁移脚本，仅运行未执行过的变更，彻底避免重复DDL
• 支持精确的版本控制，便于追踪和回滚结构变更

3. 控制DDL执行的并发与时机 #

• 调整spring.sql.init.mode为never，配合迁移工具仅在需要时执行变更
• 多实例部署时，用分布式锁（如Redis锁）确保同一时间只有一个实例执行DDL
• 避免在业务高峰时段执行结构变更

4. 排查并修复未提交事务 #

出现锁等待时，用以下SQL定位问题：

-- 查看元数据锁等待详情
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- 查看所有未提交的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

找到长时间未提交的事务，排查业务代码中是否存在未及时提交/回滚的逻辑。

内容的提问来源于stack exchange，提问作者Mongol