Adding notes for tests as discussed with Carlos.

For the first two queries:

metadata_locks -> https://dev.mysql.com/doc/refman/5.7/en/metadata-locks-table.html
table_handles -> https://dev.mysql.com/doc/mysql-perfschema-excerpt/5.7/en/table-handles-table.html


Add the following to the my.cnf and restart:

performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
performance-schema-instrument='transaction=ON'


Run the following in one session:

use test;
create table table_read (id int) engine=innodb;
create table table_read_local (id int) engine=innodb;
create table table_write (id int) engine=innodb;
create table table_low_priority_write (id int) engine=innodb;
LOCK TABLES table_read READ, table_read_local READ LOCAL, table_write WRITE, table_low_priority_write LOW_PRIORITY WRITE;
-- leave the session here


In another session, run the queries, and you should see output like:


mysql [localhost] {msandbox} (performance_schema) > select t.processlist_id, ml.* from performance_schema.metadata_locks ml join performance_schema.threads t on (ml.owner_thread_id=t.thread_id);
+----------------+-------------+--------------------+--------------------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| processlist_id | OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME              | OBJECT_INSTANCE_BEGIN | LOCK_TYPE            | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+----------------+-------------+--------------------+--------------------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
...
|              3 | TABLE       | test               | table_low_priority_write |       140316715851664 | SHARED_NO_READ_WRITE | TRANSACTION   | GRANTED     | sql_parse.cc:6030 |              28 |             12 |
|              3 | TABLE       | test               | table_read               |       140316725468352 | SHARED_READ_ONLY     | TRANSACTION   | GRANTED     | sql_parse.cc:6030 |              28 |             12 |
|              3 | TABLE       | test               | table_write              |       140316725468448 | SHARED_NO_READ_WRITE | TRANSACTION   | GRANTED     | sql_parse.cc:6030 |              28 |             12 |
|              3 | TABLE       | test               | table_read_local         |       140316715910512 | SHARED_READ          | TRANSACTION   | GRANTED     | sql_parse.cc:6030 |              28 |             12 |
...



mysql [localhost] {msandbox} (performance_schema) > select t.processlist_id, th.* from performance_schema.table_handles th left join performance_schema.threads t on (t
+----------------+-------------+---------------+--------------------------+-----------------------+-----------------+----------------+---------------+----------------+
| processlist_id | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME              | OBJECT_INSTANCE_BEGIN | OWNER_THREAD_ID | OWNER_EVENT_ID | INTERNAL_LOCK | EXTERNAL_LOCK  |
+----------------+-------------+---------------+--------------------------+-----------------------+-----------------+----------------+---------------+----------------+
|              8 | TABLE       | test          | table_low_priority_write |       139881515232224 |              33 |             12 | NULL          | WRITE EXTERNAL |
|              8 | TABLE       | test          | table_read               |       139881515224496 |              33 |             12 | NULL          | READ EXTERNAL  |
|              8 | TABLE       | test          | table_read_local         |       139881514816176 |              33 |             12 | NULL          | READ EXTERNAL  |
|              8 | TABLE       | test          | table_write              |       139881514822096 |              33 |             12 | NULL          | WRITE EXTERNAL |

...