daniel-s
8/2/2016 - 3:32 PM

MySQL - Waiting for table metadata lock http://stackoverflow.com/a/13155778/3149679

MySQL - Waiting for table metadata lock http://stackoverflow.com/a/13155778/3149679

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;


SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_trx_id;
  
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX t 
JOIN INFORMATION_SCHEMA.PROCESSLIST p 
ON t.trx_mysql_thread_id = p.id;

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX t 
JOIN INFORMATION_SCHEMA.PROCESSLIST p 
ON t.trx_mysql_thread_id = p.id WHERE trx_weight > 0;

SELECT
	trx_id,
    trx_state,
    trx_started,
    trx_weight,
    trx_mysql_thread_id,
    trx_query,
    host,
    command,
    time,
    state,
    info
FROM INFORMATION_SCHEMA.INNODB_TRX t 
JOIN INFORMATION_SCHEMA.PROCESSLIST p 
ON t.trx_mysql_thread_id = p.id 
WHERE trx_weight > 0;
SHOW ENGINE INNODB STATUS \G

Look for the Section -

TRANSACTIONS

Or

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

We can use INFORMATION_SCHEMA Tables.

Useful Queries -

To check about all the locks transactions are waiting for -

USE INFORMATION_SCHEMA
SELECT * FROM INNODB_LOCK_WAITS;
A list of blocking transactions
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 perticular 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';

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