How to Detect and Resolve Blocked DDL Operations in MySQL

Background

Developers often encounter DDL statements that appear to run forever because they are blocked by metadata locks.

How to tell if a DDL is blocked

Run SHOW PROCESSLIST and look for a row whose State is Waiting for table metadata lock. If such a row exists, the DDL is blocked, and any subsequent operations on the same table will also wait.

Method 1 – Using sys.schema_table_lock_waits

The view introduced in MySQL 5.7 exposes metadata‑lock wait information. Example output shows waiting and blocking thread IDs, lock types, and ready‑to‑run kill commands.

SELECT sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE blocking_lock_type <> 'SHARED_UPGRADABLE'
  AND waiting_query LIKE 'alter table sbtest.t1 add c1 datetime';

Filtering out rows where blocking_lock_type equals SHARED_UPGRADABLE removes noise, because that lock type is used only in the first phase of a DDL and never actually blocks the operation.

Method 2 – Killing sessions that hold open transactions

When the blocking session is a long‑running transaction, SHOW PROCESSLIST shows it as Sleep. Query information_schema.innodb_trx to find transactions that started before the DDL lock and generate kill statements:

SELECT CONCAT('kill ', i.trx_mysql_thread_id, ';')
FROM information_schema.innodb_trx i,
     (SELECT MAX(time) AS max_time
      FROM information_schema.processlist
      WHERE state = 'Waiting for table metadata lock'
        AND (info LIKE 'alter%' OR info LIKE 'create%' OR
             info LIKE 'drop%' OR info LIKE 'truncate%' OR
             info LIKE 'rename%')) p
WHERE TIMESTAMPDIFF(SECOND, i.trx_started, NOW()) > p.max_time;

Enabling the MDL instrument in MySQL 5.7

The view relies on the performance_schema.metadata_locks table, which is disabled by default in 5.7. Enable it with:

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

For a permanent change, add the following to my.cnf:

[mysqld]
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

Summary

Run SHOW PROCESSLIST; if the state is Waiting for table metadata lock, the DDL is blocked.

Use sys.schema_table_lock_waits (filtering out SHARED_UPGRADABLE) or the transaction‑based query to locate and kill the blocking session.

The methods work for MySQL 5.7 and 8.0; remember to enable the MDL instrument in 5.7.