5.5.3. SQL Syntax for Online DDL

Typically, you do not need to do anything special to enable online DDL when using the ALTER TABLE statement for InnoDB tables. See Table 5.9, "Summary of Online Status for DDL Operations" for the kinds of DDL operations that can be performed in-place, allowing concurrent DML, or both. Some variations require particular combinations of configuration settings or ALTER TABLE clauses.

You can control the various aspects of a particular online DDL operation by using the LOCK and ALGORITHM clauses of the ALTER TABLE statement. These clauses come at the end of the statement, separated from the table and column specifications by commas. The LOCK clause is useful for fine-tuning the degree of concurrent access to the table. The ALGORITHM clause is primarily intended for performance comparisons and as a fallback to the older table-copying behavior in case you encounter any issues with existing DDL code. For example:

To avoid accidentally making the table unavailable for reads, writes, or both, you could specify a clause on the ALTER TABLE statement such as LOCK=NONE (allow both reads and writes) or LOCK=SHARED (allow reads). The operation halts immediately if the requested level of concurrency is not available.
To compare performance, you could run one statement with ALGORITHM=INPLACE and another with ALGORITHM=COPY, as an alternative to setting the old_alter_table configuration option.
To avoid the chance of tying up the server by running an ALTER TABLE that copied the table, you could include ALGORITHM=INPLACE so the statement halts immediately if it cannot use the in-place mechanism. See Table 5.9, "Summary of Online Status for DDL Operations" for a list of the DDL operations that can or cannot be performed in-place.

See Section 5.5.2, "Performance and Concurrency Considerations for Online DDL" for more details about the LOCK clause. For full examples of using online DDL, see Section 5.5.5, "Examples of Online DDL".