Troubleshooting·MySQL

MySQL Metadata Locks and Blocked DDL

An `ALTER TABLE` hangs behind a transaction that only ever ran a `SELECT`. No rows are locked - the block is a metadata lock, held until that transaction commits, and it is why migrations stall behind idle connections.

Troubleshooting Guide 38 of 45 Advanced

OSUbuntu 26.04 LTS (resolute)
MySQL8.4.10-0ubuntu0.26.04.1
Topologydb-a01 source, db-b01 and db-c01 replicas
TimeAbout 15 min
Reviewed27 August 2026

Written against the versions above. Metadata locks were introduced in 5.5 and are held for the whole transaction, not just the statement. That is the detail that surprises people: a `SELECT` inside an uncommitted transaction blocks DDL indefinitely.

Every command on this page ran on db-a01.
Server Name	IP Address	OS	Roles	CPU	RAM	HDD
db-a01	192.168.0.81	Ubuntu 26.04 LTS	Primary / Source / Replica Set Member 1	2 Core	4 GB	50 GB

Before you start

The customers table.
Two connections - the transcript backgrounds them.

Block a DDL with a plain SELECT
The first connection opens a transaction, runs a SELECT COUNT(*), and sleeps without committing. It has read data and changed nothing.

The second runs ALTER TABLE customers ADD COLUMN, with its own lock_wait_timeout set to four seconds so the demonstration ends.

ERROR 1205: Lock wait timeout exceeded - the same error number as a row lock timeout, which is genuinely confusing, because no rows are locked. This is a *metadata* lock: a SELECT takes SHARED_READ on the table's definition, and a DDL needs an exclusive one.

The metadata_locks table confirms it: customers, SHARED_READ, GRANTED, owned by the sleeping thread. The killer detail is that the lock is held until the transaction commits, not until the SELECT finishes. An idle connection with an open transaction blocks every migration on that table, forever, while looking completely idle in the process list.
bash Example session
```
$sudo bash -c 'mysql appdb -e "START TRANSACTION; SELECT COUNT(*) FROM customers; SELECT SLEEP(8);" >/dev/null 2>&1 & sleep 2; mysql appdb -e "SET lock_wait_timeout=4; ALTER TABLE customers ADD COLUMN tmp_col INT;" ; echo "exit=$?"; mysql --table -e "SELECT OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, OWNER_THREAD_ID FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA=\"appdb\" LIMIT 5"; wait'ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transactionexit=1+-------------+-------------+-------------+-----------------+| OBJECT_NAME | LOCK_TYPE   | LOCK_STATUS | OWNER_THREAD_ID |+-------------+-------------+-------------+-----------------+| customers   | SHARED_READ | GRANTED     |             160 |+-------------+-------------+-------------+-----------------+
```
Expected resultERROR 1205, exit=1, and a SHARED_READ metadata lock GRANTED to another thread.

Success conditionYou can recognise a metadata lock and know it is not about rows.

Map the lock to a connection you can act on

metadata_locks gives an OWNER_THREAD_ID, which is not the connection id you need for KILL. Joining to performance_schema.threads bridges them.

Now the row is actionable: the table, the lock type, and the PROCESSLIST_ID to kill, plus PROCESSLIST_COMMAND and PROCESSLIST_TIME showing what that connection is doing and for how long.

Expect the command to read Sleep. That is the point of this guide - the connection blocking your migration is not running anything, and nothing in the process list alone would tell you it was responsible.

bash Example session

$sudo bash -c 'mysql appdb -e "START TRANSACTION; SELECT COUNT(*) FROM customers; SELECT SLEEP(7);" >/dev/null 2>&1 & sleep 2; mysql --table -e "SELECT m.OBJECT_NAME, m.LOCK_TYPE, m.LOCK_STATUS, t.PROCESSLIST_ID, t.PROCESSLIST_COMMAND, t.PROCESSLIST_TIME FROM performance_schema.metadata_locks m JOIN performance_schema.threads t ON t.THREAD_ID = m.OWNER_THREAD_ID WHERE m.OBJECT_SCHEMA = \"appdb\""; wait'+-------------+-------------+-------------+----------------+---------------------+------------------+| OBJECT_NAME | LOCK_TYPE   | LOCK_STATUS | PROCESSLIST_ID | PROCESSLIST_COMMAND | PROCESSLIST_TIME |+-------------+-------------+-------------+----------------+---------------------+------------------+| customers   | SHARED_READ | GRANTED     |             21 | Query               |                2 |+-------------+-------------+-------------+----------------+---------------------+------------------+

Expected resultA metadata lock row joined to its connection id, command and time.

Success conditionYou can go from a blocked DDL to the connection id responsible.

Let the transaction commit and watch the DDL go through

The same collision with the timeout raised to 30 seconds and the blocking transaction committing after four.

The ALTER waits, the commit releases the metadata lock, and the DDL completes with alter exit=0. DESCRIBE confirms tmp_col is there.

Nothing was killed. The lock was never a fault - it was doing its job, preventing a table definition from changing underneath a transaction that had already read it. The column is dropped afterwards to leave the schema as it was.

The practical lesson is about timeout choice. Four seconds fails fast and tells you there is a blocker; thirty waits it out. Both are reasonable, and the default of a year is not.

bash Example session

$sudo bash -c 'mysql appdb -e "START TRANSACTION; SELECT COUNT(*) FROM customers; SELECT SLEEP(4); COMMIT;" >/dev/null 2>&1 & sleep 1; mysql appdb -e "SET lock_wait_timeout=30; ALTER TABLE customers ADD COLUMN tmp_col INT;" ; echo "alter exit=$?"; wait'alter exit=0$sudo mysql --table appdb -e "DESCRIBE customers"+------------+----------------------------+------+-----+-------------------+-------------------+| Field      | Type                       | Null | Key | Default           | Extra             |+------------+----------------------------+------+-----+-------------------+-------------------+| id         | int unsigned               | NO   | PRI | NULL              | auto_increment    || name       | varchar(80)                | NO   |     | NULL              |                   || email      | varchar(120)               | NO   | UNI | NULL              |                   || status     | enum('active','suspended') | NO   |     | active            |                   || credit     | decimal(10,2)              | NO   |     | 0.00              |                   || created_at | timestamp                  | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED || tmp_col    | int                        | YES  |     | NULL              |                   |+------------+----------------------------+------+-----+-------------------+-------------------+$sudo mysql appdb -e "ALTER TABLE customers DROP COLUMN tmp_col" ; echo "exit=$?"exit=0

Expected resultalter exit=0, tmp_col present in DESCRIBE, then dropped with exit=0.

Success conditionYou have seen a metadata lock resolve on its own and the DDL succeed.

Troubleshooting

An ALTER TABLE hangs with no row locks anywhere.

Why: A metadata lock held by an open transaction.

Fix:SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_NAME = 'table'. The OWNER_THREAD_ID maps to a process list entry - kill it or wait for the commit.
The blocking connection shows Sleep and looks idle.

Why: It is idle, and it still holds the lock because its transaction is open.

Fix:That is the whole problem. Set wait_timeout so abandoned connections close, and fix the application that leaves transactions open.
A migration blocked everything else on the table.

Why: The waiting DDL queues an exclusive request, and every query arriving after it queues behind that.

Fix:Always run DDL with a short lock_wait_timeout so it fails fast instead of building a queue behind it.

Official sources

← All MySQL guides