Description:
I'd like to get thorough explanation in the manual on the behavior and interactions between various MDL locks. See Bug #76588 where this is requested among other things.
As a part of that long term goal, I'd like to see more details about the online version of ALTER TABLE for InnoDB tables presented in the manual. For now we can see the following (https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html):
"Depending on the internal workings of the online DDL operation and the LOCK clause of the ALTER TABLE statement, an online DDL operation may require exclusive access to the table for a brief time during the initial and final phases of the DDL operation. Thus, an online DDL operation might wait before finishing if there is a long-running transaction performing inserts, updates, deletes, or SELECT ... FOR UPDATE on the table; and an online DDL operation might wait before finishing if a similar long-running transaction is started while the ALTER TABLE is in progress."
On another page, https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-concurrency.html, we see similar text:
"Depending on the internal workings of the online DDL operation and the LOCK clause of the ALTER TABLE statement, an online DDL operation may wait for currently executing transactions that are accessing the table to commit or roll back before completing because exclusive access to the table is required for a brief time during the initial and final phases of the DDL operation. These online DDL statements also wait for table-accessing transactions started while the DDL is in progress to commit or roll back before completing. Consequently, in the case of long running transactions performing inserts, updates, deletes, or SELECT ... FOR UPDATE on the table, an online ALTER TABLE operation can time out as it waits for exclusive access to the table. DML operations that are run on the table while the online ALTER TABLE operation waits for an exclusive table lock may also be blocked."
It would be nice to have these "initial" and "final" phases of the DDL operation explained, including "brief" time estimation etc.
I also wonder if exclusive locks are requested twice for all kinds of online ALTER TABLE, or for some of them (maybe those that require changes only to the table metadata, like STATS_PERSISTENT proiperty) may work with exclusive lock requested only once?
How to repeat:
Try to explain what MDL locks are set and released, in what order and when, for typical online ALTER TABLE cases for InnoDB table, based on the manual.
This is important to decide is it safe to proceed in production with specific online ALTER or one has to rely on 3rd party tools (like pt-osc or gh-ost) better...
Suggested fix:
Describe all kinds of metadata locks used by MySQL, their interactions and order of acquision and release for most important SQL statements, inlcuding (but not limited to) all kinds of online ALTER TABLE statements for InnoDB tables.
