MariaDB InnoDB表:如何查找导致“等待表元数据锁”的语句

Question

如何确定在information_schema.metadata_lock_info上的元数据锁信息行(SELECT * FROM MariaDB )中显示的线程ID的SQL语句？

Server version: 10.0.15-MariaDB MariaDB Server

从MySQL的角度来看，所有相关的问题都深入到“等待表元数据锁”中，但这对MariaDB没有帮助，因为它们的内省实现方式与我所能知道的不同。在谷歌上搜索并不会出现很多情况。

“显示完整处理列表”给出的行如下：

| 57295 | main  | localhost | joints | Execute |   50 | Waiting for table metadata lock | select ...

它确实显示了语句，但也没有显示它有锁。因此，我打开元数据锁信息，如这里所解释的。这只提供锁持有人的线程ID，但不提供语句：

MariaDB [joints]> SELECT * FROM information_schema.metadata_lock_info;
+-----------+--------------------------+-----------------+----------------------+--------------+----------------+
| THREAD_ID | LOCK_MODE                | LOCK_DURATION   | LOCK_TYPE            | TABLE_SCHEMA | TABLE_NAME     |
+-----------+--------------------------+-----------------+----------------------+--------------+----------------+
|     57322 | MDL_INTENTION_EXCLUSIVE  | MDL_EXPLICIT    | Global read lock     |              |                |
|     57322 | MDL_SHARED_NO_READ_WRITE | MDL_EXPLICIT    | Table metadata lock  | joints       | 16_study       |
|     57322 | MDL_INTENTION_EXCLUSIVE  | MDL_EXPLICIT    | Schema metadata lock | joints       |                |
|     57269 | MDL_SHARED_READ          | MDL_TRANSACTION | Table metadata lock  | joints       | authentication |
|     57301 | MDL_SHARED_READ          | MDL_TRANSACTION | Table metadata lock  | joints       | authentication |
|     57280 | MDL_SHARED_READ          | MDL_TRANSACTION | Table metadata lock  | joints       | authentication |
|     57317 | MDL_SHARED_READ          | MDL_TRANSACTION | Table metadata lock  | joints       | ship           |
|     57271 | MDL_SHARED_READ          | MDL_TRANSACTION | Table metadata lock  | joints       | administration |
|     57264 | MDL_SHARED_READ          | MDL_TRANSACTION | Table metadata lock  | joints       | server         |
+-----------+--------------------------+-----------------+----------------------+--------------+----------------+

我真正想要的是在锁定发生的时候看到这两个输出的“连接”。我看不出有什么方法可以连接这两个“表”中的数据，因为前者似乎不是一个表。我想避免：

ERROR 1933 (HY000): Target is not running an EXPLAINable command

当试图实时执行时，由于线程在被检查时结束.

0

Answer 1

THREAD_ID映射到information_schema.PROCESSLIST.ID ( show [full] processlist;中的第一列)。ie：

SELECT * FROM information_schema.METADATA_LOCK_INFO AS mli
JOIN information_schema.PROCESSLIST AS pl ON mli.THREAD_ID = pl.ID

为了更容易地看到正在发生的事情，我更喜欢下面这样的东西(不过，换行符对cli不起作用)：

SELECT
  mli.THREAD_ID, mli.LOCK_MODE, mli.LOCK_TYPE, 
  CAST(GROUP_CONCAT(DISTINCT CONCAT(mli.TABLE_SCHEMA, '.', mli.TABLE_NAME) ORDER BY mli.TABLE_SCHEMA, mli.TABLE_NAME SEPARATOR '\n') AS CHAR) AS locked_tables, 
  pl.USER, pl.HOST, pl.DB, pl.COMMAND, pl.TIME, pl.STATE, pl.INFO, pl.QUERY_ID, pl.TID
FROM information_schema.METADATA_LOCK_INFO AS mli
JOIN information_schema.PROCESSLIST AS pl ON mli.THREAD_ID = pl.ID
GROUP BY mli.THREAD_ID, mli.LOCK_MODE, mli.LOCK_TYPE
ORDER BY time DESC, pl.ID;

特别有趣的是，当pl.COMMAND = 'Sleep'表示某些连接池或其他(主要是只读的)程序持有具有锁的打开连接时。