Posted inMySQL

Handling MySQL Hangs Due to Metadata Locking: Don’t Let an ALTER Command Crash Your System

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Background: When ALTER TABLE Becomes a Production Nightmare

Imagine you just ran an ALTER TABLE command to add a status column to the orders table. On staging, this took less than a second. But as soon as you hit Enter on production, Slack starts blowing up with 504 Gateway Timeout alerts. Database CPU spikes to 90%, and every query to the orders table suddenly freezes.

I once learned this the hard way with a 50GB MySQL 8.0 database containing over 40 million records. The culprit wasn’t a typical row lock, but Metadata Locking (MDL). This is MySQL’s mechanism for protecting table structure. When a transaction is reading data, MySQL blocks any schema changes to ensure consistency.

Here’s the catch: All it takes is one long-running background SELECT or a transaction that “forgot” to commit, and your ALTER TABLE is stuck in a queue. Worse, this ALTER command blocks the head of the line, causing all subsequent SELECT and INSERT queries to hang as well. The result? Your entire application becomes paralyzed.

Reproducing Metadata Locking in 3 Steps

To fix the problem, we need to understand how it manifests. You can simulate this scenario on your local machine using two terminal windows.

Step 1: Data Preparation

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=InnoDB;

INSERT INTO users (name) VALUES ('An'), ('Bình'), ('Chi');

Step 2: Create a “Hanging” Transaction (Session 1)

Open the first session and start a transaction, but do NOT COMMIT.

START TRANSACTION;
SELECT * FROM users WHERE id = 1; 
-- Keep this session open; do not close it or type anything else.

Step 3: Run the ALTER Command (Session 2)

In the second window, try adding a new column.

ALTER TABLE users ADD COLUMN email VARCHAR(255);

At this point, Session 2 will hang. If you open Session 3 and run a simple SELECT, it will also freeze. Welcome to the world of Metadata Locking.

How It Works: Why Does MySQL Behave This Way?

Since version 5.5.3, MySQL has used MDL to manage access to table structures. There are two types of locks you need to distinguish:

  • Shared Metadata Lock (SU): Triggered when you read or write data (SELECT, INSERT…). Multiple users can hold this lock simultaneously.
  • Exclusive Metadata Lock (X): Triggered when changing the structure (ALTER, DROP…). Only one user can hold this lock at a time.

In the example above, Session 1 holds a Shared Lock. Session 2 wants an Exclusive Lock, so it has to wait. The tricky part is that while the Exclusive Lock is waiting, it prioritizes blocking all new Shared Locks. This is the domino effect that quickly brings down the system.

A common mistake is leaving the lock_wait_timeout at its default value. MySQL sets this to 31,536,000 seconds (that’s one year!). This means the ALTER command will wait until the server crashes. I recommend lowering it to about 60 seconds.

-- Check current configuration
SHOW VARIABLES LIKE 'lock_wait_timeout';

-- Limit wait time to 60s to protect the system
SET SESSION lock_wait_timeout = 60;

How to “Rescue” the Database When It Hangs

When you see the system start to slow down, don’t rush to restart MySQL. This only makes things worse because the crash recovery process will take a long time.

1. Search Using SHOW PROCESSLIST

Check how many connections are in the Waiting for table metadata lock state.

SHOW FULL PROCESSLIST;

Note: This command only shows who is waiting; it doesn’t indicate who is currently holding the lock.

2. Use Performance Schema to Find the “Culprit”

On MySQL 5.7 and above, this is the most powerful tool. First, enable MDL monitoring:

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

Then, run this query to find the exact thread ID that is blocking everything:

SELECT 
    OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, THREAD_ID, PROCESSLIST_ID
FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'GRANTED';

3. Quick Rescue with the sys Schema

If you’re too lazy to type long queries, the sys schema has a very intuitive view:

SELECT * FROM sys.schema_table_lock_waits;

Look at the blocking_pid column, find that ID, and KILL it immediately to release the table:

-- For example, if the found PID is 456
KILL 456;

Real-World Experience: Prevention is Better Than Cure

After handling many incidents on large systems, I’ve gathered 4 golden rules:

  • Use Online Schema Change tools: For tables over 10GB, never use ALTER TABLE directly. Use Percona’s pt-online-schema-change or GitHub’s gh-ost. They create temporary tables and copy data gradually without long table locks.
  • Check for long transactions: Before migration, check if any cron jobs or reports are running. A SELECT command lasting 10 minutes will trigger an MDL lock.
  • Choose off-peak hours: No matter how good the tools are, perform migrations during the lowest traffic periods (usually 2-3 AM).
  • Set a short timeout: Always set lock_wait_timeout for the session running the ALTER. It’s better for the migration to fail than for it to hang the entire website.

Handling Metadata Locks requires staying calm. When you see hundreds of connections hanging, remember: find the right ID holding the lock and deal with it, instead of panicking and restarting the server.

Share:
Tags: