•Online operations read less data into the buffer pool than table-copy operations, which reduces purging of frequently accessed data from memory. Purging of frequently accessed data can cause a temporary performance dip after a DDL operation. 

## The LOCK clause 

By default, MySQL uses as little locking as possible during a DDL operation. The LOCK  clause can be specified for in-place operations and some copy operations to enforce more restrictive locking, if required. If the LOCK  clause specifies a less restrictive level of locking than is permitted for a particular DDL operation, the statement fails with an error. LOCK  clauses are described below, in order of least to most restrictive: 

• LOCK=NONE : 

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. 

• LOCK=SHARED : 

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. 

• LOCK=DEFAULT : 

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 do not expect the default locking level of the DDL statement to cause any availability problems for the table. 

• LOCK=EXCLUSIVE : 

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. 

## Online DDL and Metadata Locks 

Online DDL operations can be viewed as having three phases: 

• Phase 1: Initialization 

In the initialization phase, the server determines how much concurrency is permitted during the operation, taking into account storage engine capabilities, operations specified in the statement, and user-specified ALGORITHM  and LOCK  options. During this phase, a shared upgradeable metadata lock is taken to protect the current table definition. 

• Phase 2: Execution 

In this phase, the statement is prepared and executed. Whether the metadata lock is upgraded to exclusive depends on the factors assessed in the initialization phase. If an exclusive metadata lock is required, it is only taken briefly during statement preparation. 

