问题描述
我正在尝试对表执行一些 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.
推荐答案
适用于 MySql 版本 <5.7.3
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);
或
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.
这篇关于如何找到导致“等待表元数据锁定"的事务?状态?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！