How to Diagnose and Resolve MySQL MDL Lock Blocking DDL Operations

1. Fault Background

In production, DBAs often encounter metadata lock (MDL) issues when executing DDL changes. Running show processlist may show "waiting for table metadata lock", indicating the problem.

2. Problem Reproduction

2.1 A risky script

A maintenance script uses a connection pool but never closes cursors or connections, leaving sessions open and causing MDL lock buildup.

import mysql.connector
from dbutils.pooled_db import PooledDB
# Database connection info
pool = PooledDB(
    creator=mysql.connector,  # use mysql.connector driver
    mincached=1,  # initial idle connections
    maxcached=10,  # max idle connections
    maxshared=3,  # max shared connections
    maxconnections=15,  # max total connections
    blocking=True,  # block when pool is exhausted
    host='xx.xx.xx.xx',
    user='wms',
    password='123456',
    database='wms',
    unix_socket='/data/mysql8.0.23-3306/mysql-8.0.23/mysql3306.sock'
)
try:
    conn = pool.connection()
    cursor = conn.cursor()
    sql = "SELECT * FROM wms.order_info LIMIT 1;"
    cursor.execute(sql)
    results = cursor.fetchall()
    for row:
        print(row)
    # connections are not closed to simulate leak
    while True:
        pass
except mysql.connector.Error as err:
    print(f"Error: {err}")

2.2 Simulating DDL operation

During a change window, a DDL statement is executed in one session while another session queries information_schema.processlist, showing the waiting state.

# Execute script
[root@11-186-63-123 opt]# python3.8 pool.py

# Session 1: ALTER TABLE, DDL hangs
ALTER TABLE wms.order_info MODIFY COLUMN status varchar(35);

# Session 2: Check processlist, see MDL lock
mysql> select * from information_schema.processlist where command != 'Sleep';
+--------+-----------------+---------------------+------+------------------+---------+---------------------------------------------------------------+---------------------------------------------------------------------+
| ID     | USER            | HOST                | DB   | COMMAND          | TIME    | STATE                                                         | INFO                                                                |
+--------+-----------------+---------------------+------+------------------+---------+---------------------------------------------------------------+---------------------------------------------------------------------+
| 57     | repl            | 11.186.63.118:36624 | NULL | Binlog Dump GTID | 2872846 | Master has sent all binlog to slave; waiting for more updates | NULL                                                                |
| 377524 | root            | localhost           | wms  | Query            | 37      | Waiting for table metadata lock                              | ALTER TABLE wms.order_info MODIFY COLUMN status varchar(35);      |
| 5      | event_scheduler | localhost           | NULL | Daemon           | 3022562 | Waiting on empty queue                                      | NULL                                                                |
| 378462 | root            | localhost           | NULL | Query            | 0       | executing                                                    | select * from information_schema.processlist where command != 'Sleep'; |
+--------+-----------------+---------------------+------+------------------+---------+---------------------------------------------------------------+---------------------------------------------------------------------+

The DDL hangs because the table is locked, severely impacting business.

3. Investigation Steps

3.1 Identify transactions holding MDL locks

select OBJECT_SCHEMA,OBJECT_NAME,COLUMN_NAME,LOCK_TYPE,LOCK_STATUS,OWNER_THREAD_ID from performance_schema.metadata_locks where OBJECT_NAME='order_info';

The query reveals a shared read lock and a shared upgradable lock held by thread IDs 392747 and 392740 respectively.

3.2 Map thread IDs to processlist IDs

select THREAD_ID,PROCESSLIST_ID from performance_schema.threads where thread_id in (392740,392747);

3.3 Retrieve the SQL text of those sessions

SELECT a.thread_id, a.sql_text FROM performance_schema.events_statements_current a WHERE a.THREAD_ID IN (SELECT b.THREAD_ID FROM performance_schema.threads b WHERE b.PROCESSLIST_ID IN (392568,392575));

The results show the ALTER TABLE statement and a SELECT query that keep the locks.

4. Solution

Terminate the transaction holding the shared read lock on order_info: kill 392575; After killing the session, the DDL completes successfully.

ALTER TABLE wms.order_info MODIFY COLUMN status varchar(30);
Query OK, 1000001 rows affected (14 min 53.45 sec)
Records: 1000001  Duplicates: 0  Warnings: 0

5. Summary

5.1 Lock Types

Row lock – InnoDB engine level, managed automatically.

MDL lock – Server level, protects table metadata; shows "Waiting for table metadata lock" when blocked.

Global lock – Server level, obtained via FLUSH TABLES WITH READ LOCK, blocks all writes.

5.2 Shared Upgradable Lock

The SHARED_UPGRADABLE MDL lock can be upgraded to EXCLUSIVE or SHARED_NO_WRITE when needed, ensuring exclusive access during DDL.

5.3 Preventive Measures

Enable the metadata_locks table to monitor MDL usage.

Set a smaller lock_wait_timeout to avoid long waits.

Commit transactions promptly and avoid large transactions.

Enhance monitoring and alerts for MDL lock occurrences.

Schedule DDL and backup operations during low‑traffic periods.

Close cursors and connections in scripts promptly.

Ensure maintenance scripts properly release resources.