14.14.4 Concurrency Considerations for Fast Index Creation

While an InnoDB secondary index is being created or dropped, the
table is locked in shared mode. Any writes to the table are
blocked, but the data in the table can be read. When you alter the
clustered index of a table, the table is locked in exclusive mode,
because the data must be copied. Thus, during the creation of a
new clustered index, all operations on the table are blocked.

A CREATE INDEX or
ALTER TABLE statement for an InnoDB
table always waits for currently executing transactions that are
accessing the table to commit or roll back.
ALTER TABLE statements that
redefine an InnoDB primary key wait for all
SELECT statements that access the table to
complete, or their containing transactions to commit. No
transactions whose execution spans the creation of the index can
be accessing the table, because the original table is dropped when
the clustered index is restructured.

Once a CREATE INDEX or
ALTER TABLE statement that creates
an InnoDB secondary index begins executing, queries can access the
table for read access, but cannot update the table. If an
ALTER TABLE statement is changing
the clustered index for an InnoDB table, all queries wait until
the operation completes.

A newly-created InnoDB secondary index contains only the committed
data in the table at the time the CREATE
INDEX or ALTER TABLE
statement begins to execute. It does not contain any uncommitted
values, old versions of values, or values marked for deletion but
not yet removed from the old index.

Because a newly-created index contains only information about data
current at the time the index was created, queries that need to
see data that was deleted or changed before the index was created
cannot use the index. The only queries that could be affected by
this limitation are those executing in transactions that began
before the creation of the index was begun. For such queries,
unpredictable results could occur. Newer queries can use the
index.