mdl lock summary

mdl_lock_summary.sql

DROP PROCEDURE IF EXISTS procShowMetadataLockSummary;

delimiter //

CREATE PROCEDURE procShowMetadataLockSummary()
BEGIN

  DECLARE table_schema VARCHAR(64);
  DECLARE table_name VARCHAR(64);
  DECLARE id bigint;
  DECLARE time bigint;
  DECLARE info longtext;
  DECLARE curMdlCount INT DEFAULT 0;
  DECLARE curMdlCtr INT DEFAULT 0;
  DECLARE curMdlCheckCount INT DEFAULT 0;
  DECLARE curMdl CURSOR FOR
    SELECT * FROM tmp_blocked_metadata;

  DECLARE curmdlCheck CURSOR FOR
    SELECT
      *
    FROM
      performance_schema.setup_instruments
    WHERE
      NAME = 'wait/lock/metadata/sql/mdl'
    AND
    (
      enabled = 'NO'
      or timed = 'NO'
    );

  OPEN curmdlCheck;
  SET curMdlCheckCount = ( SELECT FOUND_ROWS() );
  CLOSE curmdlCheck;

  IF curMdlCheckCount = 1 THEN
    UPDATE
      performance_schema.setup_instruments
    SET
      ENABLED = 'YES',
      TIMED = 'YES'
    WHERE
      NAME = 'wait/lock/metadata/sql/mdl';
  END IF;

  DROP TEMPORARY TABLE IF EXISTS tmp_blocked_metadata;

  CREATE TEMPORARY TABLE IF NOT EXISTS tmp_blocked_metadata (
    table_schema varchar(64),
    table_name varchar(64),
    id bigint,
    time bigint,
    info longtext,
    PRIMARY KEY(table_schema, table_name)
  );

  REPLACE tmp_blocked_metadata(
    table_schema,
    table_name,
    id,
    time,
    info
  )
  SELECT
    mdl.OBJECT_SCHEMA,
    mdl.OBJECT_NAME,
    t.PROCESSLIST_ID,
    t.PROCESSLIST_TIME,
    t.PROCESSLIST_INFO
  FROM
    performance_schema.metadata_locks mdl
  JOIN
    performance_schema.threads t ON mdl.OWNER_THREAD_ID = t.THREAD_ID
  WHERE
    mdl.LOCK_STATUS = 'PENDING'
  AND
    mdl.LOCK_TYPE = 'EXCLUSIVE'
  ORDER BY
    mdl.OBJECT_SCHEMA,
    mdl.OBJECT_NAME,
    t.PROCESSLIST_TIME ASC;

  OPEN curMdl;
  SET curMdlCount = ( SELECT FOUND_ROWS() );

  WHILE (curMdlCtr < curMdlCount) DO FETCH curMdl INTO table_schema,
    table_name,
    id,
    time,
    info;

    SELECT
      CONCAT_WS(
        ' ',
        'PID',
        t.PROCESSLIST_ID,
        'has a metadata lock on',
        CONCAT(mdl.OBJECT_SCHEMA, '.', mdl.OBJECT_NAME),
        'with current state',
        CONCAT_WS('', '[', t.PROCESSLIST_STATE, ']'),
        'for',
        t.PROCESSLIST_TIME,
        'seconds and is currently running',
        CONCAT_WS('', "[", t.PROCESSLIST_INFO, "]")
      ) AS 'Process(es) that have metadata locks'
    FROM
      performance_schema.metadata_locks mdl
    JOIN
      performance_schema.threads t ON t.THREAD_ID = mdl.OWNER_THREAD_ID
    WHERE
      mdl.LOCK_STATUS = 'GRANTED'
    AND
      mdl.OBJECT_SCHEMA = table_schema
    AND
      mdl.OBJECT_NAME = table_name
    AND
      mdl.OWNER_THREAD_ID
    NOT IN
    (
      SELECT
        mdl2.OWNER_THREAD_ID
      FROM
        performance_schema.metadata_locks mdl2
      WHERE
        mdl2.LOCK_STATUS = 'PENDING'
      AND
        mdl.OBJECT_SCHEMA = mdl2.OBJECT_SCHEMA
      AND
        mdl.OBJECT_NAME = mdl2.OBJECT_NAME
    );

    SELECT
      CONCAT_WS(
        ' ',
        'PID',
        id,
        'has been waiting for the metadata lock on',
        CONCAT(table_schema, '.', table_name),
        'for',
        time,
        'seconds to execute',
        CONCAT_WS('', '[', info, ']')
      ) AS 'Oldest process waiting for a metadata lock';

    SET curMdlCtr = curMdlCtr + 1;

    SELECT
      CONCAT_WS(
        ' ',
        'PID',
        t.PROCESSLIST_ID,
        'has been waiting for metadata lock on',
        CONCAT(table_schema, '.', table_name),
        'for',
        t.PROCESSLIST_TIME,
        'seconds to execute',
        CONCAT_WS('', '[', t.PROCESSLIST_INFO, ']')
      ) AS 'Other queries waiting for metadata lock'
    FROM
      performance_schema.metadata_locks mdl
    JOIN
      performance_schema.threads t ON t.THREAD_ID = mdl.OWNER_THREAD_ID
    WHERE
      mdl.LOCK_STATUS = 'PENDING'
    AND
      mdl.OBJECT_SCHEMA = table_schema
    AND
      mdl.OBJECT_NAME = table_name
    AND
      mdl.OWNER_THREAD_ID
    AND
      t.PROCESSLIST_ID <> id;
  END WHILE;

  CLOSE curMdl;

END //

delimiter;