Understanding MySQL DDL Execution and Online DDL Mechanisms

Overview

MySQL distinguishes between DDL (Data Definition Language) for schema changes and DML (Data Manipulation Language) for row‑level operations. DML statements can be wrapped in transactions and rolled back, while DDL statements are implicitly committed and cannot be rolled back.

In everyday development, many engineers are familiar with DML execution paths but pay little attention to how DDL is processed. Understanding DDL execution helps avoid hidden pitfalls, especially when altering large tables.

Online DDL Introduction

MySQL provides two ways to execute DDL: the native online DDL (available since 5.6) and third‑party tools such as pt‑osc. Online DDL aims to modify table structures without blocking concurrent DML operations.

DDL Execution Algorithms

Copy algorithm : creates a temporary table, locks the original table for writes, performs the DDL on the temporary table, copies all rows, then swaps the tables. This method blocks DML and requires extra storage.

Inplace algorithm : modifies the original table directly without a full copy. It is further divided into:

rebuild – requires rebuilding the clustered index (e.g., adding indexes, changing column types).

no‑rebuild – only metadata changes (e.g., dropping indexes, renaming columns).

Inplace DDL uses a row‑log to capture DML changes during the rebuild and re‑applies them after the new structure is ready.

Execution Phases

Prepare phase : determines allowed concurrency, acquires a shared metadata lock (MDL), creates temporary .frm files, decides the algorithm, and allocates row‑log structures.

DDL execution phase : holds a shared MDL, scans the old table, builds new index entries, sorts them in a buffer, and records incremental changes in the row‑log.

Commit phase : upgrades the lock to exclusive MDL, replaces the old table definition with the new one, updates the InnoDB data dictionary, and finalizes the transaction.

Common Pitfalls

Online DDL still requires exclusive MDL at certain points, which can cause deadlocks if other sessions hold shared MDL (e.g., an open transaction). Example sessions illustrate how a long‑running SELECT can block an ALTER TABLE until the transaction ends.

To resolve stuck sessions, you can identify the offending thread in information_schema.innodb_trx and issue KILL <thread_id> . If the DDL itself blocks many sessions, consider aborting the DDL or using a tool like pt‑osc or gh‑ost.

Limitations

Only InnoDB fully supports online DDL; other engines fall back to COPY.

All DDL operations acquire a short exclusive lock at start and end.

Foreign‑key constraints, ON … CASCADE/SET NULL, and certain ALTER operations still require table copy or lock.

Online DDL is replicated to replicas, but replication itself is single‑threaded and can cause lag.

Summary

The article covered DDL vs DML, detailed the online DDL mechanisms (copy vs inplace), described the three execution phases, highlighted common deadlock scenarios, listed current limitations, and mentioned alternative tools (pt‑osc, gh‑ost) and the new MySQL 8.0 INSTANT DDL option.