How to find which transaction causes the status "Pending metadata table"?

Question

I am trying to execute some DDL in the table, and SHOW PROCESSLIST results in the message "Waiting for the metadata table".

How do I know which transaction is not yet closed?

I am using MySQL v5.5.24.

Answer 1

 SHOW ENGINE INNODB STATUS \G 

Find the section -

 TRANSACTIONS 

We can use tables INFORMATION_SCHEMA .

Useful Requests -

To check, all lock transactions are waiting -

 USE INFORMATION_SCHEMA SELECT * FROM INNODB_LOCK_WAITS; 

Blocking Transaction List

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

The list of locks on the percussion table:

 SELECT * FROM INNODB_LOCKS WHERE LOCK_TABLE = db_name.table_name; 

List of pending transactions:

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

Link - MySQL troubleshooting: what to do when queries don't work , chapter 6 - page 96.

Answer 2

If you cannot find the table locking process (because it is dead), it could be a thread still clearing, like this

TRANSACTION section

 show engine innodb status; 

in the end

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

as indicated in the comment in Clear transactional deadlock?

you can try to kill the transaction flow directly, here with

  KILL 5208136; 

worked for me.

Answer 3

mysql 5.7 provides metadata locking information through the performance_schema.metadata_locks table.

Documentation here

Answer 4

I had a similar problem with Datagrip and none of these solutions worked.

Once I restarted the Datagrip Client, this is no longer a problem, and I could reset the tables again.