Investigate metadata locks

from the Artful MySQL Tips List

The tail of the result of ...

show engine innodb status\G

...will show current transaction activity.

Before MySQL 5.7

Information_schema queries:

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

A stored procedure for MySQL before 5.7 to do all the above including fetching lock data on a particular table ...

drop procedure if exists metadata_locks;
delimiter go
create procedure metadatalocks( ptbl varchar(1024) )
begin
  select * from information_schema. innodb_lock_waits;

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

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

  select * from information_schema.innodb_locks where lock_table = ptbl;

  select trx_id, trx_requested_lock_id, trx_mysql_thread_id, trx_query 
  from information_schema.innodb_trx 
  where trx_state = 'lock wait';
end;
go
delimiter ;

Since MySQL 5.7

Enable performance_schema metadata lock instrumentation ...

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

... then use these queries to investigate metadata locks...

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

Explainer for finding metadata locks and waits since 5.7 ... https://dev.mysql.com/doc/refman/8.0/en/metadata-locking.html

Last updated 1 Aug 2020

Return to the Artful MySQL Tips page