79543

Locking Options for Online DDL

While an InnoDB table is being changed by a DDL operation, the table may or may not be locked,

depending on the internal workings of that operation and the LOCK clause of the alter table

statement. By default, MySQL uses as little locking as possible during a DDL operation; you specify

the clause either to make the locking morę restrictive than it normally would be (thus limiting concurrent

DML, or DML and queries), or to ensure that some expected degree of locking is allowed for an

operation. If the lock clause specifies a level of locking that is not available for that specific kind of

DDL operation, such as lock=shared or lock=none while creating or dropping a primary key, the

clause works like an assertion, causing the statement to fail with an error. The following list shows the

different possibilities for the lock clause, from the most permissive to the most restrictive:

•    For DDL operations with lock=none, both queries and concurrent DML are allowed. This clause

makes the ALTER TABLE fail if the kind of DDL operation cannot be performed with the requested

type of locking, so specify lock=none if keeping the table fully available is vital and it is OK to

cancel the DDL if that is not possible. For example, you might use this clause in DDLs for tables

involving customer signups or purchases, to avoid making those tables

unavailable by mistakenly

issuing an expensive alter table statement.

•    For DDL operations with lock=shared, any writes to the table (that is, DML operations) are

blocked, but the data in the table can be read. This clause makes the ALTER TABLE fail if the kind

of DDL operation cannot be performed with the requested type of locking, so specify lock=shared

if keeping the table available for queries is vital and it is OK to cancel the DDL if that is not possible.