Which ALTER TABLE operations lock or rebuild a MySQL table?

MySQL 8.0 runs many ALTER TABLE operations online, but "online" has three very different meanings: instant (metadata only), in place with a rebuild (every row rewritten, writes usually allowed), and copy (writes blocked). The manual's operations matrix says which is which; this page summarises the cases that matter for migrations.

The matrix, condensed (MySQL 8.0, InnoDB)

Condensed from the MySQL 8.0 Reference Manual, Online DDL Operations; the asterisked entries carry conditions in the manual (table type, row format, existing indexes, SQL mode). Check the manual row for your exact case.

Three ways a migration hurts

1. Writes blocked — "Permits Concurrent DML: No". Every INSERT/UPDATE/DELETE on the table waits for the ALTER to finish. On a large table that is minutes to hours.
2. Rebuild — writes continue, but every row is rewritten: disk I/O, temporary space, replica lag, and a long-running operation that holds the table's metadata lock at start and end.
3. Metadata-lock queue — any ALTER needs an exclusive metadata lock briefly. If a long transaction holds the table, the ALTER waits, and all new queries on the table queue behind the waiting ALTER (Metadata Locking). This is why even an "instant" ALTER can cause a pile-up in production.

Always assert ALGORITHM and LOCK

MySQL picks the algorithm itself unless you say otherwise, and silently falls back to a copy when the requested one is unsupported. Declaring the expectation turns a silent outage into an immediate error:

ALTER TABLE orders ADD INDEX idx_customer (customer_id), ALGORITHM=INPLACE, LOCK=NONE;
-- If MySQL cannot do this in place without locking, the statement fails at once: ERROR 1846 / 1845

See ALTER TABLE statement for the ALGORITHM and LOCK clauses.

How Presift flags it

▲ MG002 WARNING  migration.sql:1
    ALTER TABLE orders: MODIFY COLUMN amount: potential table copy if the data type changes
    Why: Changing a column's data type requires ALGORITHM=COPY and blocks concurrent DML. Extending a VARCHAR within the same length-byte class, changing NULL/NOT NULL (rebuild, DML permitted) or adding ENUM/SET members at the end are cheaper. The current column definition is not visible to this tool, so this is a potential risk only.
    Confidence: medium (statement-only analysis; current schema not visible)

▲ MG003 WARNING  migration.sql:1
    ALTER TABLE orders: no ALGORITHM/LOCK clause on a potentially blocking change
    Do:  Append ', ALGORITHM=INPLACE, LOCK=NONE' (or 'ALGORITHM=INSTANT' for instant-capable changes). If the operation genuinely requires COPY, declare 'ALGORITHM=COPY' to make the intent explicit and plan the window.

name: migration-safety
on: [pull_request]
permissions:
  contents: read
  id-token: write          # lets Presift start your 30-day evaluation
jobs:
  presift:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: nishu-sde/presift@v1
        with:
          paths: db/migrations
          fail-on: error