Locking Options for Online DDL

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 more 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.
... zobacz całą notatkę