如何找到哪个事务导致“等待表元数据锁定"?状态?

问题描述

我正在尝试在表上执行某些DDL，并且SHOW PROCESSLIST导致出现正在等待表元数据锁定"消息.

I am trying to perform some DDL on a table and SHOW PROCESSLIST results in a " Waiting for table metadata lock " message.

如何找出尚未关闭的交易?

How can I find out which transaction is not yet closed?

我正在使用MySQL v5.5.24.

I'm using MySQL v5.5.24.

推荐答案

SHOW ENGINE INNODB STATUS \G

查找该部分-

TRANSACTIONS

我们可以使用 INFORMATION_SCHEMA 表.

We can use INFORMATION_SCHEMA Tables.

有用的查询

要检查所有正在等待的锁交易:

To check about all the locks transactions are waiting for:

USE INFORMATION_SCHEMA;
SELECT * FROM INNODB_LOCK_WAITS;

阻止交易列表:

SELECT *
FROM INNODB_LOCKS
WHERE LOCK_TRX_ID IN (SELECT BLOCKING_TRX_ID FROM INNODB_LOCK_WAITS);

OR

SELECT INNODB_LOCKS.*
FROM INNODB_LOCKS
JOIN INNODB_LOCK_WAITS
  ON (INNODB_LOCKS.LOCK_TRX_ID = INNODB_LOCK_WAITS.BLOCKING_TRX_ID);

特定表上的锁列表:

A List of locks on particular table:

SELECT * FROM INNODB_LOCKS
WHERE LOCK_TABLE = db_name.table_name;

等待锁的事务列表:

A list of transactions waiting for locks:

SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY
FROM INNODB_TRX
WHERE TRX_STATE = 'LOCK WAIT';

参考- MySQL故障排除:当查询不起作用时该怎么做，第6章-第96页.

Reference - MySQL Troubleshooting: What To Do When Queries Don't Work, Chapter 6 - Page 96.

这篇关于如何找到哪个事务导致“等待表元数据锁定"?状态?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！