May 12, 2025

How to Avoid MySQL MDL Locks?

Resolve "Waiting for table metadata lock" errors in MySQL. Learn to diagnose MDL contention, analyze thread states, and enforce best practices to prevent DDL blocking.

1. Incident Background

In production environments, DBAs frequently execute DDL (Data Definition Language) changes. One recurring challenge is MDL (Metadata Lock) contention, where DDL operations hang due to unresolved metadata locks.

When the show processlist command shows Waiting for table metadata lock, it indicates an MDL lock conflict. This article leverages real-world production failure cases to outline systematic troubleshooting steps for MDL-related issues.

‍

2. Reproducing the Issue

2.1 A Risky Script

A production script used a connection pool but failed to close cursors and connections, creating hidden MDL lock risks.

import mysql.connector
from dbutils.pooled_db import PooledDB

pool = PooledDB(
    creator=mysql.connector,
    mincached=1,
    maxcached=10,
    maxshared=3,
    maxconnections=15,
    blocking=True,
    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()
    # Intentionally omitting cursor.close() and conn.close()
    while True:  # Simulating unclosed connections
        pass
except mysql.connector.Error as err:
    print(f"Error: {err}")

‍

2.2 Simulating DDL Operations

Session 1: Executing a DDL statement:

ALTER TABLE wms.order_info MODIFY COLUMN status varchar(35);

‍

Session 2: Checking active sessions:

mysql> SELECT * FROM information_schema.processlist WHERE command != 'Sleep';
+--------+-----------------+---------------------+------+------------------+---------+---------------------------------------+-----------------------------------------------------------+
| ID     | USER            | HOST                | DB   | COMMAND          | TIME    | STATE                                 | INFO                                                      |
+--------+-----------------+---------------------+------+------------------+---------+---------------------------------------+-----------------------------------------------------------+
| 377524 | root            | localhost           | wms  | Query            |      37 | Waiting for table metadata lock       | ALTER TABLE wms.order_info MODIFY COLUMN status varchar(35) |
+--------+-----------------+---------------------+------+------------------+---------+---------------------------------------+-----------------------------------------------------------+

‍

Session 3: Blocked read/write operations:

mysql> INSERT INTO order_info VALUES(...);  -- Hangs indefinitely

‍

3. Troubleshooting Workflow

3.1 Identify Active MDL Locks

Query the performance_schema.metadata_locks table:

SELECT OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, OWNER_THREAD_ID 
FROM performance_schema.metadata_locks 
WHERE OBJECT_NAME = 'order_info';

‍

Output:

+---------------+-------------+-------------------+-----------+-----------------+
| OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE         | LOCK_STATUS | OWNER_THREAD_ID |
+---------------+-------------+-------------------+-----------+-----------------+
| wms           | order_info  | SHARED_UPGRADABLE | PENDING   |          392740 |
+---------------+-------------+-------------------+-----------+-----------------+

‍

Interpretation: A SHARED_UPGRADABLE lock (held by Thread 392740) blocks the ALTER TABLE operation.

3.2 Trace the Blocking Thread

Link thread IDs to processlist entries:

SELECT THREAD_ID, PROCESSLIST_ID 
FROM performance_schema.threads 
WHERE THREAD_ID IN (392740, 392747);

‍

Output:

+-----------+----------------+
| THREAD_ID | PROCESSLIST_ID |
+-----------+----------------+
|    392740 |         392568 |
+-----------+----------------+

‍

3.3 Resolve the Blocking Query

Terminate the offending session:

KILL 392568;  -- Kills the SELECT query holding the shared lock

‍

4. Solution & Best Practices

4.1 Lock Hierarchy Overview

4.2 Preventing MDL Contention

Close Connections: Ensure scripts release connections (e.g., add cursor.close() and conn.close()).
Set Timeouts: Reduce lock_wait_timeout to avoid long blockages.
Avoid Long Transactions: Commit transactions promptly.
Schedule DDL Off-Peak: Execute DDL during low-traffic periods.
Monitor Actively: Use tools like pt-deadlock-logger to track MDL conflicts.