A "large" table :

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

Adding a FOREIGN KEY disallow to insert into the table


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 really solid question. Jul 7 '18 at 0:12
  • 1
    adding a constraint is pretty quick, the large part of those 20mins was ensuring all the existing data met the constraint.
    – danblack
    Sep 16 '18 at 5:31

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.


The documentation is crap, and there is an open issue on it: I added a comment on MDEV-15877 linking to this post.

I believe there are two types of locks in play here.

  • Metadata lock (Strong MDL). This stops table-modifications, but NOT writes and reads. I believe this is what we're referring to as a TABLE LOCK.
  • Row-level locks. This stops modification of rows. This is being used by as the rows are being checked and held untill the DDL statement is complete.

In addition there is "TABLE LOCK" that you can get with LOCK TABLE. The command SHOW OPEN TABLES presumably only reports this type of lock, which includes a MDL lock, because only this type of lock prevents reads and writes on the entire table.

This functionality predates MDL locking, and the docs indicate that it has since been modified,

With the implementation of the metadata locking improvements in MariaDB 5.5, LOCK TABLE... WRITE acquires a strong MDL lock, and concurrent connections will wait on this MDL lock, so any subsequent LOCK TABLE... WRITE will not increment In_use.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.