Skip to content

Instantly share code, notes, and snippets.

@gembin
Last active December 22, 2023 11:09
Show Gist options
  • Save gembin/3ccff22199d45b56f11892f6138607cb to your computer and use it in GitHub Desktop.
Save gembin/3ccff22199d45b56f11892f6138607cb to your computer and use it in GitHub Desktop.
MySQL - Waiting for table metadata lock
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';

SQL

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;

Reference

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment