Debugging metadata locking in MySQL 5.5

MySQL 5.1 added a long-needed feature: INFORMATION_SCHEMA tables for inspecting locks held and waited-for inside of InnoDB. With this addition, it became possible to figure out who is blocking whom.

MySQL 5.5 changed a lot more things inside the server. One of the new features is improved metadata locking. This solves a lot of inconsistencies and bugs that were previously possible. The output of SHOW PROCESSLIST is also changed; instead of the venerable Locked status, there are more fine-grained status indicators such as Waiting for table metadata lock. Unfortunately, the additional locking is not possible for the DBA to inspect. Where InnoDB’s storage-engine-level locking used to be opaque and was then made transparent, now server-level locking is a mystery, and in my experience much more likely to become a problem than it used to be. (The server now does a lot more locking of things it previously neglected to lock.)

I have recently run into a number of circumstances where metadata locking prevents users from doing any work. The locking is not inside InnoDB, so the INFORMATION_SCHEMA tables show nothing at all. I tried using mysqladmin debug and looking at the contents of the server’s error log, but the table in question was shown as unused in the output there. I tried examining each of the other connections to the server, but was unable to find any connection that I believed could have touched the table and locked it. The lock wait was ongoing for a long time, and no other transaction had been started previous to that, so it did not seem that a long-running transaction could have touched the table and was keeping a metadata lock open.

It feels like a bug in metadata locking to me, but I will need to dig in more deeply the next time I see it happen. Unfortunately, it’s unlikely that I’ll be able to create a reproducible test case, because there’s no way to actually see what is happening. I hope that a future version of MySQL will include a more comprehensive set of tables for inspecting locks, requests, and waits at all layers of the server.

About The Author

Baron is the founder and CEO of VividCortex. He is the author of High Performance MySQL and many open-source tools for performance analysis, monitoring, and system administration. Baron contributes to various database communities such as Oracle, PostgreSQL, Redis and MongoDB.