我如何确定什么SQL语句是线程ID显示在元数据锁信息行(SELECT * FROM information_schema.metadata_lock_info)上的MariaDB?
Server version: 10.0.15-MariaDB MariaDB Server
所有相关的问题都深入到"Waiting for table metadata lock";从MySQL的角度来看,但这对MariaDB没有帮助,因为他们的自省实现与我所知道的不同。在谷歌上搜索不会出现很多。
A "显示完整的processlist"给出如下行:
| 57295 | main | localhost | joints | Execute | 50 | Waiting for table metadata lock | select ...
显示语句,但也不显示它有锁。因此,我打开了元数据锁信息,如下所示[0]。这只提供锁持有者的线程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 |
+-----------+--------------------------+-----------------+----------------------+--------------+----------------+
我真正想看到的是"join"。这两个输出在发生锁定时的状态。我没有看到连接这两个"表"中的数据的方法。因为前者看起来并不是一张桌子。我想避免得到:
ERROR 1933 (HY000): Target is not running an EXPLAINable command
当尝试实时执行时,由于线程在检查时结束。
[0] https://mariadb.com/kb/en/mariadb/metadata_lock_info/
THREAD_ID
映射到information_schema.PROCESSLIST.ID
(show
[full] processlist;
的第一列)。即:
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'
表示一些连接池或其他(主要是只读的)程序持有对它们有锁的打开连接时。