Why adding FOREIGN KEY constraint does not lock the table

A "large" table :

mysql> select count(*) from MyTable;
+----------+
| count(*) |
+----------+
|  6594951 |
+----------+

Adding a FOREIGN KEY disallow to insert into the table

mysql> ALTER TABLE MyTable ADD CONSTRAINT FK_DC7F6DF281F7EA87 FOREIGN KEY (key_id) REFERENCES Key (id);

Take almost 20 minutes during it is impossible to add a new row.

My question: but why mysql does not consider the table as a locked table?

mysql> SHOW OPEN TABLES WHERE `Table` LIKE '%MyTable%' AND `Database` LIKE 'db';
+----------+-----------------------+--------+-------------+
| Database | Table                 | In_use | Name_locked |
+----------+-----------------------+--------+-------------+
| db       | MyTable               |      0 |           0 |
+----------+-----------------------+--------+-------------+
1 row in set (0.02 sec)

Thank you

A guess:

Even though the table is effectively locked, SHOW OPEN TABLES is oblivious to it.

LOCK TABLES is a very old DDL statement in MySQL. It was probably a kludge to let users manually simulate transactions. At about the same time (version 3?) SHOW OPEN TABLES was added.

Once InnoDB was added, it became clear that LOCK TABLES was no longer needed; InnoDB does row-level locking for DML and transactions.

There are a few DDL statements that effectively "lock" a table even in InnoDB, but they probably come through a different part of the code. That is (remember, I am guessing), LOCK TABLES and SHOW OPEN TABLE know about each other, but ALTER runs in a different circle.

Feel free to complain at bugs.mysql.com .

I checked 122 systems; according to Com_show_open_tables, only 11 used SHOW OPEN TABLES since startup.

StackExchange Database Administrators Q#191510, answer score: 2