MySQL 5.7 alter table blocking operations

Table 14.12 Online DDL Support for Column Operations

Operation In Place Rebuilds Table Permits Concurrent DML Only Modifies Metadata
Adding a column Yes Yes Yes* No
Dropping a column Yes Yes Yes No
Renaming a column Yes No Yes* Yes
Reordering columns Yes Yes Yes No
Setting a column default value Yes No Yes Yes
Changing the column data type No Yes No No
Extending VARCHAR column size Yes No Yes Yes
Dropping the column default value Yes No Yes Yes
Changing the auto-increment value Yes No Yes No*
Making a column NULL Yes Yes* Yes No
Making a column NOT NULL Yes* Yes* Yes No
Modifying the definition of an ENUM or SET column Yes No Yes Yes

Usage example:

Available lock types


    Permits concurrent queries and DML.

    For example, use this clause for tables involving customer signups or purchases, to avoid making the tables unavailable during lengthy DDL operations.


    Permits concurrent queries but blocks DML.

    For example, use this clause on data warehouse tables, where you can delay data load operations until the DDL operation is finished, but queries cannot be delayed for long periods.


    Permits as much concurrency as possible (concurrent queries, DML, or both). Omitting the LOCK clause is the same as specifying LOCK=DEFAULT.

    Use this clause when you know that the default locking level of the DDL statement does not cause availability problems for the table.


    Blocks concurrent queries and DML.

    Use this clause if the primary concern is finishing the DDL operation in the shortest amount of time possible, and concurrent query and DML access is not necessary. You might also use this clause if the server is supposed to be idle, to avoid unexpected table accesses.

Similar Posts