How to Query MySQL Metadata Locks Using the MySQL Shell Plugin

MySQL's Metadata Lock (MDL) mechanism protects the consistency of concurrent access to data objects; DDL, table‑level locks, and global locks all generate MDLs, causing write operations to block until the lock is released, and sessions holding MDLs are invisible in the processlist.

When other sessions become blocked, identifying the root cause can be difficult; a previous article titled "Quickly Locate Painful Global Locks" discussed this problem.

While experimenting with MySQL Shell plugins, a convenient method was discovered to query metadata locks using the community plugin ext.check.get_locks() , which works on MySQL 8.0+ and leverages a CTE query.

For MySQL 5.7, the metadata instrument must be enabled first.

call sys.ps_setup_enable_instrument('wait/lock/metadata/sql/mdl%');

The following SQL statement retrieves metadata lock information, showing the locking session, SQL, and lock type in a combined view:

SELECT ps.*, lock_summary.lock_summary FROM sys.processlist ps INNER JOIN ( SELECT owner_thread_id, GROUP_CONCAT( DISTINCT CONCAT( mdl.LOCK_STATUS, ' ', mdl.lock_type, ' on ', IF(mdl.object_type = 'USER LEVEL LOCK', CONCAT(mdl.object_name, ' (user lock)'), CONCAT(mdl.OBJECT_SCHEMA, '.', mdl.OBJECT_NAME) ) ) ORDER BY mdl.object_type ASC, mdl.LOCK_STATUS ASC, mdl.lock_type ASC SEPARATOR '\n' ) AS lock_summary FROM performance_schema.metadata_locks mdl GROUP BY owner_thread_id ) lock_summary ON (ps.thd_id = lock_summary.owner_thread_id);

The article also includes community updates and a prompt to read the original source for more details.