ALTER TABLE without locking large tables

The default ALTER TABLE in older MySQL took a metadata lock on the table for the whole operation. On a multi-million-row table that means minutes to hours of blocked traffic. Online DDL — ALGORITHM=INPLACE, LOCK=NONE — modifies the table in place while reads and writes continue. The catch is that not every alteration supports it.

ALTER TABLE tbl_test
  MODIFY column_1 DATETIME,
  ALGORITHM=INPLACE,
  LOCK=NONE;

If MySQL cannot run the alteration with the requested algorithm or lock level, it errors out instead of silently downgrading. That’s the safety property you want — better a clear error than a surprise table lock at 2 a.m.

The three algorithms

MySQL 8.0 added ALGORITHM=INSTANT for a narrow set of alterations (mostly adding a nullable column at the end). When it applies, the change is metadata-only and completes in milliseconds regardless of table size — always try it first.

The lock levels

LOCK=NONE permits concurrent reads and writes. LOCK=SHARED permits reads only. LOCK=EXCLUSIVE blocks everything for the duration. The algorithm decides which lock levels are even available.

1. Try ALGORITHM=INSTANT first

   ALTER TABLE … ALGORITHM=INSTANT, LOCK=NONE;. Completes immediately if supported (adding a nullable column at the end, dropping a column in MySQL 8.0.29+, renaming a column). Errors out if not — at which point you’ve lost nothing.

2. Fall back to ALGORITHM=INPLACE, LOCK=NONE

   Most index additions, most column type widenings (e.g. VARCHAR(100) → VARCHAR(255) when the byte-length category stays the same), foreign key additions, and column nullability changes are supported. The operation runs in the background; concurrent writes are buffered and reapplied at the end.

3. If MySQL refuses INPLACE, learn why before retrying

   The error message names the reason — for example, “cannot use online DDL because operation requires table rebuild”. You then have a choice: accept ALGORITHM=COPY and a write lock during the maintenance window, or change the alteration to something that does support online DDL.

4. For multi-step alterations, batch them

   ALTER TABLE lets you combine many changes in a single statement. That’s often the difference between one INPLACE operation and three table rewrites — batch related changes whenever the algorithm and lock requirements line up.

What does and doesn’t support INPLACE+NONE

The MySQL manual has the authoritative list per version; the broad categories that tend to work:

• Adding a secondary index (CREATE INDEX is the same operation under the hood).
• Dropping an index.
• Adding a column at the end (often INSTANT in 8.0+).
• Renaming a column or index.
• Modifying a column’s default value (metadata only).
• Widening VARCHAR within the same length-prefix category. The prefix is 1 byte when the column’s maximum byte length is ≤ 255 and 2 bytes when > 255 — crossing that boundary triggers a table rewrite. The boundary is in bytes, not characters, so for utf8mb4 (up to 4 bytes/char) the cutoff is around 64 chars; for latin1 it’s 255 chars.

Operations that usually require ALGORITHM=COPY (table rewrite, write block):

• Changing a column’s data type to an incompatible type.
• Dropping a primary key without simultaneously adding a new one.
• Converting between character sets.
• Adding FULLTEXT or SPATIAL indexes when the table didn’t have one before (the first such index requires a rebuild).

What to remember

The phrase ALGORITHM=INPLACE, LOCK=NONE is the keep-the-lights-on incantation, but it isn’t magic — it works only for a specific set of alterations, and MySQL errors out instead of silently degrading. Try INSTANT first, then INPLACE+NONE, and only then reach for COPY or a shadow-table tool. When you’re operating at a scale where DDL becomes a planning event, you’re also approaching the kind of scale that pushes the questions raised in 0011 scaling MySQL before sharding · MySQL .