MySQL Online Schema Changes Without Locking Large Tables

Many schema changes on large MySQL tables can run online with InnoDB's native online DDL, using ALGORITHM=INPLACE or INSTANT and an explicit LOCK clause so the statement fails instead of silently blocking writes. When a change cannot be done online, external tools copy the table in the background and swap it in. The hidden danger is the brief metadata lock, which can queue behind a long transaction and block everything.

Which Changes Can InnoDB Do Online?

InnoDB supports several algorithms for ALTER TABLE, and which one applies depends on the operation and your MySQL version. The three to know are COPY, which rebuilds the table and blocks writes; INPLACE, which avoids a full copy for many operations and permits concurrent reads and writes; and INSTANT, which changes only metadata and finishes immediately for supported operations.

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

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

Specifying the algorithm and lock level is a safety feature. If MySQL cannot satisfy the request, the statement fails with an error instead of falling back to a blocking table copy. That failure is far better than discovering a locked production table.

The list of operations supported by each algorithm changes between versions, and some operations, such as changing a column type, usually need a rebuild. Always consult the online DDL section of the MySQL documentation for your exact version.

Why Do Metadata Locks Cause Outages?

Even an online ALTER needs a brief exclusive metadata lock at the start and the end. To get it, the statement waits for all earlier transactions that touched the table to finish. While it waits, every new query on that table queues behind it.

The result is a pile-up. A long-running transaction, or one that was left open by a forgotten session, blocks the ALTER, and the ALTER blocks all new reads and writes. The application appears to freeze, even though the change itself would take milliseconds.

• Check for long-running transactions before starting a schema change.
• Set lock_wait_timeout to a small value for the session, so the ALTER gives up quickly instead of causing a queue.
• Run changes during low traffic, and keep transactions in the application short.
• Be prepared to kill the waiting ALTER if the queue starts to grow.

When Do You Need an External Tool?

Some changes cannot be done in place, or you may want more control over load and the ability to pause. External online schema change tools create a shadow copy of the table with the new structure, copy rows in small batches, keep the copy in sync with ongoing changes, and then swap the tables with a quick rename.

Different tools implement synchronization differently, some using triggers and others reading the binary log. Each has trade-offs around performance, foreign keys, replication behavior, and operational complexity. Evaluate them against your environment and read their documentation, since limitations are significant.

• Throttle copying based on replica lag and server load.
• Test the full process on a copy of production data to estimate duration and disk needs.
• Check free disk space, because a shadow copy temporarily needs room for the whole table.
• Understand how the tool handles foreign keys and triggers before running it.

How Do You Roll Out Schema Changes Safely?

Schema changes and application deployments must be compatible with each other during the transition. Use the expand and contract pattern: first add new columns or tables in a backward-compatible way, deploy code that works with both shapes, backfill data, and only later remove the old structure.

• Add nullable columns or columns with defaults, so older code keeps working.
• Backfill in small batches with pauses, to avoid replication lag and lock contention.
• Deploy application code that reads from the new structure only after the backfill is complete.
• Drop old columns in a separate, later change.
• Watch replicas, since a long change on the primary can cause lag, depending on your replication setup.

Always keep a rollback plan, and test the migration on staging with realistic data sizes. A change that takes a second on a thousand rows may take hours on a billion.

Frequently Asked Questions

Does ALGORITHM=INSTANT lock the table?

It needs only a very brief metadata lock, but that can still queue behind long transactions.

Why did my ALTER TABLE hang the whole application?

It was probably waiting for a metadata lock behind a long transaction, and new queries queued behind it.

Can I add an index online?

Generally yes, with InnoDB in-place operations that allow concurrent reads and writes. Confirm for your version.

Do I always need an external tool?

No. Native online DDL covers many changes. Use external tools when you need an unsupported operation or finer control.

Summary

Use native online DDL with explicit algorithm and lock clauses, guard against metadata lock queues, and consider external tools for unsupported changes. Roll out with expand and contract steps. Check the MySQL documentation for your version.