Extending the SYS schema to show metadata locks

Starting with MySQL 5.7, performance_schema has been enhanced to instrument metadata locks, and can be turned on by adding the following line to your my.cnf file:

(At runtime, it can also be enabled by modifying the setup_instruments table in performance_schema.)

From here, you can now query performance_schema.metadata_locks to reveal all currently open metadata locks on your server:

Using SYS

As I have previously demonstrated, SYS is the DBA’s companion to performance_schema and contains a number of views that are more task-oriented. Seeing the raw locks is not always immediately useful, what is preferable is a view that shows this next to user sessions.

SYS has a view called session, but in this example I am going to join metadata locks to sys.processlist instead. Processlist is a superset of session, that also includes background threads.

On my testing system I have saved this query as a view called sys.session_metadata_locks. I have also opened BUG #80823 so that the sys developers can evaluate if this should be included as part of a future release.

It is always interesting to hear from DBAs on what information they find useful to be included in views. Please try it out, and let me know your feedback πŸ™‚

One thought on “Extending the SYS schema to show metadata locks

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.