show engine innodb status\G

Before MySQL 5.7

select * from innodb_lock_waits;

select * 
from innodb_locks 
where lock_trx_id in (
  select blocking_trx_id from innodb_lock_waits
);

select a.* 
from innodb_locks a
join innodb_lock_waits b on a.lock_trx_id = b.blocking_trx_id;

select * from innodb_locks where lock_table = ...

select trx_id, trx_requested_lock_id, trx_mysql_thread_id, trx_query 
from innodb_trx 
where trx_state = 'lock wait';

Since MySQL 5.7

use performance_schema;
update setup_consumers 
  set enabled = 'yes' where name = 'global_instrumentation';
update setup_instruments 
  set enabled = 'yes' where name = 'wait/lock/metadata/sql/mdl';

select * from sys.innodb_lock_waits;

select * from performance_schema.data_lock_waits;

select * from performance_schema.metadata_locks;

show engine innodb status;

select * from sys.innodb_lock_waits;

select * from performance_schema.data_lock_waits;

select * from performance_schema.metadata_locks;

select 
  object_type, object_schema, object_name, lock_type, lock_status,
  thread_id, processlist_id, processlist_info
from performance_schema.threads t 
join performance_schema.metadata_locks l 
  on t.thread_id = l.owner_thread_id
where t.processlist_id <> connection_id();

Excellent explainer for finding metadata locks and waits since 5.7.3 ...

http://mysql.wisborg.dk/2014/01/13/who-holds-the-metadata-lock-mysql-5-7-3-brings-help/

Last updated 2 Sep 2019