Comparative Analysis of MySQL ALTER DDL Algorithm Mechanics and Metadata Locks

MySQL provides three ALTER DDL algorithms—Copy, In-Place, and Instant—to enhance service availability. Each algorithm differs clearly in terms of table copying, data rebuilding necessity, and Metadata Lock (MDL) acquisition methods.

Key Characteristics by Algorithm

• Copy Algorithm: The oldest method, which creates a new table and copies data. Write operations from other sessions are blocked until completion, resulting in high disk I/O and disk space usage.
• In-Place Algorithm (MySQL 5.6+): Allows reads and writes in most cases. If table rebuilding is required, it creates a temporary table and applies DML logs that arrived during the change. The innodb_online_alter_log_max_size setting is important.
• Instant Algorithm (MySQL 8.0+): Changes only metadata, making it the fastest regardless of table size. It supports operations like adding/dropping columns, but requires rebuilding after 64 operations, and its use is restricted for FULLTEXT indexes or compressed tables.

Metadata Lock Workflow

DDL execution is divided into Initialization, Execution, and Final stages, with lock states changing dynamically during the Execution stage.

1. Initial Stage: All algorithms acquire MDL_SHARED_UPGRADABLE, allowing reads and writes from other sessions.
2. In-Place Specifics: Before entering the ha_prepare_inplace_alter_table function, the lock is upgraded to MDL_EXCLUSIVE, blocking access from other sessions. After the function completes, it is downgraded back to MDL_SHARED_UPGRADABLE. However, when adding an Auto_increment column, it is downgraded to MDL_SHARED_NO_WRITE.
3. Instant Efficiency: The Instant algorithm returns immediately without substantial work during the ha_prepare_inplace_alter_table and ha_inplace_alter_table stages, resulting in extremely short lock wait times.
4. Commit Stage: Both algorithms upgrade to MDL_EXCLUSIVE before entering ha_commit_inplace_alter_table to finalize metadata changes.

Operational Recommendations

To prevent unexpected performance degradation and service outages, it is essential to explicitly specify the ALGORITHM clause in ALTER statements. Additionally, since all algorithms involve at least a brief MDL_EXCLUSIVE lock, long-running transactions or queries should be checked before executing DDL.