MySQL lockhunter query

Tags: , ,

This requires that metadata lock instrumentation be enabled.

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';

Once that’s in place, you can do this:

SELECT
  ml.object_schema,
  ml.object_name,
  p.id,
  p.user,
  p.state,
  ml.lock_type,
  ml.lock_duration,
  ml.lock_status,
  p.time,
  LEFT(p.info, 100)
FROM
  performance_schema.metadata_locks ml
  JOIN performance_schema.threads t
    ON t.thread_id = ml.owner_thread_id
  JOIN information_schema.processlist p
    ON p.id = t.processlist_id
WHERE ml.object_name = 'dim_member'
ORDER BY p.time DESC;