MySQL Online DDL Mechanics

Table of Contents- MySQL Online DDL Mechanics - Introduction - Usage - Algorithm Options - Suitable Scenarios (from 8.0) - Unsupported DDLs - Execution Flow - Summary

Introduction

Data Definition Language (DDL) operations in MySQL, such as adding or removing columns and indexes, traditionally required a full table recreation before applying changes. Prior to MySQL 5.6, this process involvde:

1. Creating a new table based on the schema of table A
2. Locking table A for writing
3. Applying the DDL operation to the new table
4. Copying data from table A to the new table
5. Releasing the write lock on table A
6. Dropping the old table A
7. Renaming the new table to match the original name

This method was inefficient for large tables due to extended copying times and extra disk space usage. Additionally, during steps 2 through 4, all write operations were blocked, making the database unavailable for service.

To address these limitations, MySQL 5.6 introduced Online DDL, enabling DDL operations without interrupting database services.

Usage

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

Algorithm Options

ALGORITHM values: INPLACE / COPY / DEFAULT

• COPY creates a temporary table (locks the table)
• INPLACE avoids table copying and allows concurrent DML operations
• DEFAULT lets MySQL choose based on the operation type
• INSTANT modifies only metadata (available in 8.0 for limited cases like renaming)

The ALTER statement supports specifying ALGORITHM and LOCK parameters to control how DDL executes and how DML concurrency is handled:

• ALGORITHM=INPLACE ensures no table copying occurs; DML operations can proceed concurrently. This approach reduces I/O and CPU load compared to COPY and minimizes impact on the buffer pool.
• Using ALGORITHM=COPY follows the legacy method with full table replication and blocks all DML during execution.
• Setting ALGORITHM=DEFAULT allows MySQL to select the best strategy for maximizing DML concurrency.

LOCK values: NONE / SHARED / DEFAULT / EXCLUSIVE

• LOCK=NONE permits all DML operations during DDL
• LOCK=SHARED allows SELECT but blocks INSERT, UPDATE, DELETE (suitable for data warehouses where write delays are acceptable)
• LOCK=EXCLUSIVE holds a exclusive lock, blocking everything else (ideal for quick completion or idle systems)
• LOCK=DEFAULT automatically chooses based on maximizing concurrency

Suitable Scenarios (from 8.0)

- Index Management
- Primary Key Changes
- Column Modifications
- Generated Columns
- Foreign Key Adjustments
- Table-Level Actions
- Tablespace Handling
- Partitioning Tasks

Refer to:
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

Unsupported DDLs

Not every DDL can be executed with INPLACE. For instance, modifying column types isn't supported in-place. Attempting to change a FLOAT column to INT will fail:

ALTER TABLE tbl_name MODIFY COLUMN column_name INT, ALGORITHM=INPLACE, LOCK=NONE;

Results in error:
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

Execution Flow

1. Initialization: Based on storage engine, operation type, and specified ALGORITHM/LOCK settings, determine allowed concurrency. Acquire a shared metadata lock to protect table schema.
2. Execution: Decide whether to promote shared metadata lock to exclusive (during preparation phase). Execute the statement. Shared lock prevents other DDLs but allows DML.
3. Commit: Upgrade shared metadata lock to exclusive, remove old schema definition, and commit the new one.

The exclusive metadata lock phase is very fast and does not block DML operations significantly.

However, before or during DDL execution, other transactions may hold MDL locks. Since exclusive MDL is needed, the DDL waits until conflicting transactions complete.

Example:
Online DDL requires MDL locks. In MySQL 5.5, MDL was introduced to ensure consistency between reads and writes. When performing DML, a read MDL is acquired; during DDL, a write MDL is taken.

Even though INPLACE enables concurrent DML, there's still a rissk of deadlock if a transaction already holds a shared MDL and doesn't release it promptly.

For example, Session 1 performs a SELECT within a transaction, acquiring a shared MDL. Since the transaction is active, the lock remains until it ends.

Summary

Although Online DDL minimizes locking issues, it still requires metadata locks. If a table is already locked and not released, DDL operations may wait indefinitely, potentially causing deadlocks.

It's recommended to use tools like Percona Toolkit for index and column additions.