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