Diagnosing and Resolving DDL Blocking in MySQL Using sys.schema_table_lock_waits

Background – When a DDL statement (e.g., ALTER TABLE ) is blocked, all subsequent operations on the same table are also blocked and show the state Waiting for table metadata lock . This article describes how to detect the blockage, find the offending session, and clear it.

1. How to determine if a DDL is blocked

Run SHOW PROCESSLIST and look for rows whose State column contains Waiting for table metadata lock . If such a row exists for a DDL, the DDL is blocked.

Example test steps:

mysql> use test;
Database changed

mysql> CREATE TABLE `test` (
    `id` int(11) AUTO_INCREMENT PRIMARY KEY,
    `name` varchar(10)
);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd');
Query OK, 1 row affected (0.01 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM test;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
|  4 | ddd  |
+----+------+
4 rows in set (0.00 sec)

# Simulate metadata lock blocking
# Session 1
mysql> LOCK TABLES test READ;
Query OK, 0 rows affected (0.00 sec)

# Session 2 (blocked)
mysql> ALTER TABLE test ADD c1 varchar(25);
# blocked …

# Session 3 – view processlist
mysql> SHOW PROCESSLIST;
+-------+------------+---------------------+------+----------+------+---------------------------+-------------------------------------+
| Id    | User       | Host                | db   | Command  | Time | State                     | Info                                |
+-------+------------+---------------------+------+----------+------+---------------------------+-------------------------------------+
| 28452 | root       | 10.186.64.180:45674 | test | Query    | 7    | Waiting for table metadata lock | ALTER TABLE test ADD c1 varchar(25) |
+-------+------------+---------------------+------+----------+------+---------------------------+-------------------------------------+

When a DDL is blocked, the message Waiting for table metadata lock appears for the blocking session.

Solution: Kill the DDL operation itself or kill the session that is holding the lock.

2. How to find which sessions are blocking the DDL

MySQL 5.7 introduced the sys.schema_table_lock_waits view to locate blocking sessions.

SELECT * FROM sys.schema_table_lock_waits\G

The view returns rows such as:

object_schema: test
object_name: test
waiting_thread_id: 28490
waiting_pid: 28454
waiting_lock_type: EXCLUSIVE
waiting_query: alter table test add c1 varchar(25)
blocking_thread_id: 28488
blocking_pid: 28452
blocking_lock_type: SHARED_READ_ONLY
sql_kill_blocking_connection: KILL 28452

Although only one ALTER statement was issued, two rows may appear because MySQL records both the exclusive lock held by the DDL and the shared lock held by other sessions. The row with blocking_lock_type = 'SHARED_READ_ONLY' is the one that should be killed.

Precise query to obtain the kill command:

SELECT sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE blocking_lock_type <> 'SHARED_UPGRADABLE'
  AND waiting_query = 'alter table test add c1 varchar(25)';

This returns KILL 28452 , which terminates the blocking session.

MySQL 5.7 considerations for sys.schema_table_lock_waits

The view depends on the performance_schema.metadata_locks table, which in MySQL 5.7 is empty unless the instrument wait/lock/metadata/sql/mdl is enabled.

SELECT * FROM performance_schema.setup_instruments
WHERE name='wait/lock/metadata/sql/mdl';

Typical output shows the instrument disabled:

NAME                                 ENABLED  TIMED
wait/lock/metadata/sql/mdl           NO       NO

Enable it temporarily:

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

Use SHOW PROCESSLIST to detect the Waiting for table metadata lock state, indicating a blocked DDL.

Locate the blocking session with sys.schema_table_lock_waits (or alternative queries) and kill the appropriate connection.

In MySQL 5.7, ensure the MDL instrument is enabled before using the view.

These steps work for both MySQL 5.7 and MySQL 8.0.