Как определить, какая транзакция вызывает состояние "Ожидание блокировки метаданных таблицы"?

Question

Я пытаюсь выполнить некоторые DDL на столе и SHOW PROCESSLIST приводит к сообщению "Ожидание блокировки метаданных таблицы".

Как я могу узнать, какая транзакция еще не закрыта?

Я использую MySQL v5.5.24.

Answer 1

SHOW ENGINE INNODB STATUS \G

Ищите раздел -

TRANSACTIONS

Мы можем использовать таблицы INFORMATION_SCHEMA.

Полезные Запросы

Для проверки всех блокировок транзакции ждут:

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);

Список замков на перикулярном столе:

SELECT * FROM INNODB_LOCKS 
WHERE LOCK_TABLE = db_name.table_name;

Список транзакций, ожидающих блокировки:

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

Справочник - Устранение неполадок MySQL: что делать, если запросы не работают, Глава 6 - Страница 96.

Answer 2

Если вы не можете найти процесс, блокирующий таблицу (потому что он уже мертв), это может быть поток, который все еще чистит

раздел СДЕЛКА

show engine innodb status;

в конце

---TRANSACTION 1135701157, ACTIVE 6768 sec
MySQL thread id 5208136, OS thread handle 0x7f2982e91700, query id 882213399 xxxIPxxx 82.235.36.49 my_user cleaning up

как упомянуто в комментарии в Очистить тупик транзакции?

Вы можете попытаться убить поток транзакции напрямую, здесь с

 KILL 5208136;

работал на меня.

Answer 3

MySQL 5.7 предоставляет информацию о блокировке метаданных через performance_schema.metadata_locks Таблица.

Документация здесь

Answer 4

Для версии MySQL>= 5.7.3Схема производительности теперь предоставляет информацию о блокировке метаданных. https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html

Запустите этот запрос, чтобы узнать, кто держит ваши метаданные блокировки

      SELECT OBJECT_TYPE,
       OBJECT_SCHEMA,
       OBJECT_NAME,
       LOCK_TYPE,
       LOCK_STATUS,
       THREAD_ID,
       PROCESSLIST_ID,
       PROCESSLIST_INFO
FROM performance_schema.metadata_locks
INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID
WHERE PROCESSLIST_ID <> CONNECTION_ID();

Answer 5

У меня была похожая проблема с Datagrip, и ни одно из этих решений не сработало.

Как только я перезапустил Datagrip Client, это больше не было проблемой, и я мог снова удалить таблицы.

Answer 6

У меня была эта проблема, и ни один из приведенных выше запросов не показал блокировки. Но у меня было заблокировано изменение с этим сообщением «Ожидание блокировки метаданных таблицы». Я обнаружил, что существует длительный запрос (он выполнялся более двух часов). Я убил этот запрос, и алтер сразу же разблокировался.

Answer 7

Ни один из этих ответов полностью не работал у меня на Mysql 5.6. Для блокировки транзакций, которые не отображаются в списке процессов, мне пришлось использовать

      SHOW ENGINE INNODB STATUS \G

И посмотри вTransactionsраздел, как другие предложили. Обычно нижний ряд транзакций должен быть чем-то не слишком старым, например

      ---TRANSACTION 1746333130055, ACTIVE 20 sec
MySQL thread id 2078245871, OS thread handle 0x7fb1ab3bb700, query id 64608927411 10.0.200.123 app_user
Trx read view will not see trx with id >= 1746333130056, sees < 1746332958368

Но иногда это может больше походить на

      ---TRANSACTION 1742251019746, ACTIVE 283392 sec
230145 lock struct(s), heap size 29685288, 1531889 row lock(s), undo log entries 1527774
MySQL thread id 1891102408, OS thread handle 0x7f7d8c132700, query id 59116541146 10.0.200.224 app_user
Trx read view will not see trx with id >= 1742251019747, sees < 1742250438378

^ Обратите внимание на283392 secчто указывает на то, что эта транзакция является виновником заблокированной таблицы. Связанный процесс помечен как «идентификатор потока», который1891102408в этом случае. Чтобы определить основную причину того, что этот процесс вызвал блокировку, я запускаю из командной строки сервера БД:

      mysql -u root -e 'show processlist' | grep "1891102408"

Что указывает, с какого сервера исходит запрос, а также уникальный идентификатор (номер порта), например:

      32907376794 app_user    10.0.200.224:46092  production_database Sleep   124144      NULL    0   1

После входа на сервер 10.0.200.224 можно использоватьnetstat -apN | grep 46092чтобы увидеть, какой процесс на сервере отвечает за процесс Mysql, и убить его из его источника, отладив то, что пошло не так, как хотелось бы.