TopolearnAltering a big table the wrong way can block writes for hours or stall the whole application on a metadata lock.
InnoDB's online DDL can change many schemas without blocking writes. Request the algorithm explicitly so MySQL errors out instead of silently choosing a blocking one:
- `ALGORITHM=INSTANT`: changes metadata only, finishing in milliseconds regardless of table size. Adding a column (anywhere, since 8.0.29), dropping a column (8.0.29+), renaming a column, setting a default, and extending some ENUM definitions.
- `ALGORITHM=INPLACE, LOCK=NONE`: rebuilds or modifies the table while allowing concurrent reads and writes, e.g. adding a secondary index. It still does heavy I/O, and changes made meanwhile are buffered in an online log that must be applied at the end.
- `ALGORITHM=COPY`: copies the whole table and blocks writes — e.g. changing a column's data type.
Every DDL needs an exclusive metadata lock (MDL) briefly at the start and end. If a long transaction has touched the table, the DDL waits — and every new query on the table queues behind the waiting DDL. Set lock_wait_timeout low for the DDL session and check performance_schema.metadata_locks or SHOW PROCESSLIST ("Waiting for table metadata lock").
On replicas, an INPLACE alter replays only after it completes on the source, causing replication lag. For huge tables, tools like gh-ost and pt-online-schema-change copy into a shadow table in throttled chunks and swap it in; they control load and lag better.
Example
CREATE TABLE orders (id BIGINT PRIMARY KEY, customer_id BIGINT, note VARCHAR(100));
SET SESSION lock_wait_timeout = 5; -- don't queue forever on the MDL
ALTER TABLE orders ADD COLUMN source VARCHAR(20) NULL, ALGORITHM = INSTANT;
-- Query OK, 0 rows affected (0.01 sec)
ALTER TABLE orders ADD INDEX idx_customer (customer_id),
ALGORITHM = INPLACE, LOCK = NONE; -- reads and writes continue
ALTER TABLE orders MODIFY note TEXT, ALGORITHM = INPLACE, LOCK = NONE;
-- ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.
-- Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
SELECT object_name, lock_type, lock_status, owner_thread_id
FROM performance_schema.metadata_locks WHERE object_name = 'orders';Remember
- Specify ALGORITHM (and LOCK) so MySQL refuses blocking plans
- INSTANT for many column changes; INPLACE LOCK=NONE for indexes
- Metadata lock waits queue every query behind the DDL; set lock_wait_timeout
Watch out for
- Running ALTER TABLE while a long transaction holds the table, freezing all traffic behind the metadata lock.