问题
在运行的 MySQL 服务器中，如何检索表级或者全局读取锁定状态？
解决方法
在 MySQL 5.7 之前，无法计算每个会话持有的表级或者全局读锁。
从 MySQL 5.7 开始，添加了一个新的性能模式表 metadata_locks。
这种性能模式可以达到目的。
请看下面的例子。
mysql> use performance_schema ... snip ... mysql> update performance_schema.setup_instruments set enabled='yes', timed='yes' where name = 'wait/lock/metadata/sql/mdl'; ... snip ... mysql> select * from performance_schema.metadata_locks; +-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+ | GLOBAL | NULL | NULL | 140149010871792 | SHARED | EXPLICIT | GRANTED | lock.cc:1108 | 28 | 305 | | COMMIT | NULL | NULL | 140149010788016 | SHARED | EXPLICIT | GRANTED | lock.cc:1192 | 28 | 305 | | TABLE | performance_schema | metadata_locks | 140149077965408 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:5927 | 29 | 186 | +-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+ 3 rows in set (0.00 sec)
如我们所见，默认情况下未启用此性能模式表的检测。
应事先启用检测。
查询结果有三行。
第一行代表全局读锁，它是通过'FLUSH TABLES WITH READ LOCK'命令获取的。
这是通过 OBJECT_TYPE 是 GLOBAL 并且 LOCK_TYPE 是 SHARED 的事实来识别的。
第二行也是通过'FLUSH TABLES WITH READ LOCK'命令获取的，因为在持有全局读锁时不允许提交到存储引擎。
所以，FLUSH TABLES WITH READ LOCK 实际上会同时获取这两个锁。
请注意，OWNER_THREAD_ID 中显示的线程标识符不代表 SHOW PROCESSLIST 命令中显示的 Id 字段。
我们可以在 performance_schema.threads 表中找到线程 ID。
如果我们想立即检索 SHOW PROCESSLIST 命令中显示的连接标识符和锁定状态，请加入这些表。
第三行代表查询“select * from metadata_lock”本身所需的锁。
可以看到锁是表级锁，目标表是performance_schema.metadata_lock。
接下来，下面的例子是当一个表正在等待另一个会话持有的表级锁时，metadata_lock 表的内容。
mysql> select * from performance_schema.metadata_locks; +-------------+--------------------+----------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+----------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+ | GLOBAL | NULL | NULL | 139997689989408 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5440 | 27 | 5370 | | SCHEMA | world | NULL | 139997689910048 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5425 | 27 | 5370 | | TABLE | world | City | 139997680320816 | SHARED_NO_READ_WRITE | TRANSACTION | GRANTED | sql_parse.cc:5937 | 27 | 5370 | | TABLE | world | City | 139997411860784 | SHARED_READ | TRANSACTION | PENDING | sql_parse.cc:5937 | 28 | 13 | | TABLE | performance_schema | metadata_locks | 139997278198112 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:5937 | 30 | 94 | +-------------+--------------------+----------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+ 5 rows in set (0.00 sec)
在执行此查询之前，发出了两个查询。
一个是来自一个会话的“LOCK TABLES City WRITE”，另一个是来自另一个会话的“SELECT * FROM City”。
这会导致第二个会话被阻止。
第一个和第二个会话的 OWNER_THREAD_ID 分别为 27 和 28.
第一个会话“LOCK TABLES City WRITE”获取三个锁。
这包括全局级别的锁，但它的锁类型是 INTENTION_EXCLUSIVE。
这意味着 LOCK TABLES t WRITE 和 FLUSH TABLES WITH READ LOCK 将相互冲突。
第二行是模式级锁，它会阻塞 ALTER DATABASE 命令，直到表级锁被释放。
第三行表示表锁。
第一行表示“SELECT * FROM City”所需的等待表锁。
可以看到锁还没有被获取，因为LOCK_STATUS是PENDING。
我们可能认为 metadata_locks 的输出并不直观和方便找出哪个会话被哪个会话阻塞。
MySQL 5.7 通过新引入的“sys”模式解决了这个问题，它是用于服务器监控的有用视图和其他类型对象的集合。
schema_table_lock_waits 表计算出与上面相同的锁争用，如下所示。
mysql> select * from sys.schema_table_lock_waits\G
*** 1. row ***
object_schema: world
object_name: City
waiting_thread_id: 28
waiting_pid: 3
waiting_account: msandbox@localhost
waiting_lock_type: SHARED_READ
waiting_lock_duration: TRANSACTION
waiting_query: select * from City
waiting_query_secs: 4
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
blocking_thread_id: 27
blocking_pid: 2
blocking_account: msandbox@localhost
blocking_lock_type: SHARED_NO_READ_WRITE
blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 2
sql_kill_blocking_connection: KILL 2
1 row in set (0.01 sec)
这个输出完美地描述了哪个会话由于表级锁而阻塞了哪个会话。
但是，全局读锁和表级锁之间的冲突无法通过这个 sys 架构视图 schema_table_lock_waits 来检查。
唯一可行的解决方案是检查 performance_schema.metadata_lock 表。