MySQL Online Schema Changes for Large Tables Without Long Locks

Quick answer: InnoDB supports many schema changes online, meaning reads and writes continue during the operation, but the exact behavior depends on the statement and the MySQL version. Specify the algorithm and lock level explicitly so the statement fails instead of silently locking the table, watch for metadata lock pile-ups, and use staged migrations for risky changes.

What Does Online DDL Actually Do?

Altering a large table used to mean copying it, which blocked writes for as long as the copy took. InnoDB's online DDL reduces this by performing many operations in place, or even instantly, while allowing concurrent reads and writes. The behavior is controlled by two clauses: the algorithm, which says how the change is applied, and the lock, which says how much concurrency is permitted.

The common algorithm choices are instant, in place, and copy. Instant changes only metadata, so they finish almost immediately regardless of table size, and recent versions support it for operations like adding a column in many cases. In place avoids a full table copy for supported operations but may still rebuild data. Copy creates a new table and is the most disruptive. Which operations qualify for which algorithm differs by version, so check the online DDL documentation for your release.

ALTER TABLE orders
    ADD COLUMN notes VARCHAR(255) NULL,
    ALGORITHM=INSTANT;

ALTER TABLE orders
    ADD INDEX idx_customer (customer_id),
    ALGORITHM=INPLACE, LOCK=NONE;

Stating the algorithm and lock explicitly is a safety feature. If the requested combination is not supported for that operation, MySQL returns an error instead of falling back to a more disruptive method. That turns a surprise outage into a harmless failure you can investigate.

Why Do Online Changes Still Cause Outages?

The most common problem is not the data change itself but the metadata lock. An ALTER needs a brief exclusive metadata lock at the start and the end of the operation. If a long-running transaction has touched the table, the ALTER waits for it. While it waits, every new query on that table queues behind the pending ALTER, so a table that looked healthy suddenly stops serving traffic.

This pile-up happens even for operations that would otherwise be instant. The fix is operational. Check for long transactions and idle sessions with open transactions before running DDL, set a short lock wait timeout for the session so the ALTER gives up quickly instead of blocking everyone, and run changes during lower traffic.

• Set a low lock wait timeout in the migration session so a blocked ALTER fails fast.
• Look for long-running or idle-in-transaction sessions touching the table before starting.
• Monitor replication lag during the change, since replicas must apply the same DDL.
• Check disk space, because rebuilds need room for temporary data and logs.

How Do You Handle Changes That Cannot Be Online?

Some operations, such as changing a column's data type or certain character set conversions, require rebuilding the table and may block writes. For those, there are two common strategies. External online schema change tools create a shadow copy of the table, keep it synchronized with triggers or the binary log, copy existing rows in small batches, and then swap the tables with a short lock. They give you throttling and the ability to pause, at the cost of added complexity.

The other strategy is an application-level expand and contract migration. Add the new column or table first, deploy code that writes to both old and new structures, backfill existing rows in small batches, switch reads to the new structure, and finally remove the old one. Each step is small and reversible, and none requires a long lock.

Whatever you choose, rehearse on a copy of production-sized data to learn how long the change takes, and have a rollback plan. Large backfills should be throttled so they do not cause replication lag or saturate disk I/O.

Frequently Asked Questions

Does LOCK=NONE guarantee no blocking?

It requests no blocking during the operation, but brief metadata locks at the start and end still apply and can queue behind long transactions.

Is adding a column always instant?

Not always. It depends on your MySQL version, the column position, and table characteristics. Specify the instant algorithm so the statement fails if it is not possible.

Why did my ALTER hang and block the whole table?

Likely it waited for a metadata lock held by an open transaction, and later queries queued behind it. Find and end the blocking transaction, and use a short lock timeout next time.

Do I need an external tool for every change?

No. Many changes run well with built-in online DDL. Use external tools when the operation requires a table rebuild that blocks writes.

Summary

Use online DDL with explicit algorithm and lock settings, guard against metadata lock pile-ups, and monitor replication during changes. For blocking operations, use shadow-table tools or expand and contract migrations. Confirm supported operations and algorithms in the MySQL documentation for your version.