The tail of the result of ...
show engine innodb status\G...will show current transaction activity. Before MySQL 5.7Information_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.7Enable 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 |
![]() |