MySQL Metadata Locks Visualization

The idea: every statement locks the table definition

InnoDB locks rows. But while a query runs, the table definition must not change under it: no column may vanish halfway through a SELECT. MySQL protects table definitions with metadata locks (MDL), kept by the server layer (sql/mdl.cc), not by InnoDB. Every statement takes an MDL ticket on each table it uses before it touches a row: SELECT takes SHARED_READ, INSERT/UPDATE/DELETE take SHARED_WRITE. These never conflict with each other. ALTER TABLE, DROP, RENAME and TRUNCATE need EXCLUSIVE, which conflicts with everything. A ticket is kept until the transaction ends, not the statement.

Sessions S1 to S4 send statements. Each first gets an MDL ticket on table orders in the server's MDL subsystem: S1 SHARED_READ granted, S2's ALTER holds SHARED_UPGRADABLE and has a pending EXCLUSIVE, S3 SHARED_READ pending and S4 SHARED_WRITE pending behind it. Only after the ticket is granted does the statement reach InnoDB and its row locks. — The server grants a metadata lock on the table before InnoDB sees the statement; a pending EXCLUSIVE makes every later request wait.

The canvas follows four sessions, S1 to S4 (processlist ids 11 to 14), on one table orders(id, amt) with six rows. Pick a session and a statement and press Execute; each statement moves the clock 1 s, and Wait 1 s lets time pass. The tickets show up in performance_schema.metadata_locks, the sessions in SHOW PROCESSLIST.

Lock types and who takes them

The MDL subsystem has ten lock types for a table; the page uses the five that matter for everyday DML and DDL.

Type	Taken by	Conflicts with granted
SHARED_READ (SR)	`SELECT`	X
SHARED_WRITE (SW)	`INSERT`, `UPDATE`, `DELETE`, `SELECT … FOR UPDATE`	SNW, X
SHARED_UPGRADABLE (SU)	the start of every `ALTER TABLE`	SU, SNW, X (one ALTER per table at a time)
SHARED_NO_WRITE (SNW)	`ALTER TABLE … ALGORITHM=COPY` while it copies	SW, SU, SNW, X
EXCLUSIVE (X)	every DDL, at least for a moment; `DROP`, `RENAME`, `TRUNCATE`	everything

A ticket lives as long as the transaction. With autocommit = 1 each statement is its own transaction, so its ticket goes away when it ends. After BEGIN the ticket stays until COMMIT or ROLLBACK, even while the session does nothing. A session that ran one SELECT in a transaction and then went to lunch shows as Command = Sleep in the processlist, and it still holds SHARED_READ on the table.

The pile-up: Waiting for table metadata lock

A request is granted only if it is compatible with every ticket granted to another session and with every request pending from another session. The second rule gives DDL priority: without it, a busy table would never be quiet long enough for an ALTER. The cost is the most common MySQL outage after a schema change:

S1 has an open transaction that read orders: SHARED_READ, granted. S1 is idle.
S2 runs ALTER TABLE orders …. It gets SHARED_UPGRADABLE, then asks for EXCLUSIVE, which conflicts with S1's SR: PENDING.
S3's plain SELECT asks for SR. It is compatible with S1's SR and with S2's SU, but not with the pending X: it waits too. So does every later SELECT and UPDATE.
The processlist fills up with Waiting for table metadata lock, connections run out, the application is down. Nothing moves until S1 commits.

Timeline: at 2 s S1 BEGIN and SELECT holds SHARED_READ and goes idle. At 3 s S2's ALTER gets SHARED_UPGRADABLE and a pending EXCLUSIVE. At 4 s S3's SELECT and at 5 s S4's UPDATE wait behind the pending EXCLUSIVE although they are compatible with S1. At 6 s S1 commits: the ALTER runs in an instant, then S3 and S4 run. — One idle open transaction plus one waiting ALTER block every new statement on the table.

Demo: one idle transaction blocks everyone plays exactly this. Even ALGORITHM=INSTANT causes it, because INSTANT still needs EXCLUSIVE for a moment. To find the culprit, look past the waiters: the blocker is usually a Sleep session with an open transaction. sys.schema_table_lock_waits joins the tickets and names it (blocking_pid, with a ready-made sql_kill_blocking_connection), and information_schema.innodb_trx shows how long its transaction has been open. You need performance_schema with the wait/lock/metadata/sql/mdl instrument on (the default since 8.0).

lock_wait_timeout: a year by default

A waiting MDL request gives up after lock_wait_timeout seconds with ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction. The default is 31536000 s, one year, so in practice an ALTER waits forever and the pile-up lasts as long as the blocker does. (InnoDB row-lock waits use a different setting, innodb_lock_wait_timeout, default 50 s.)

The fix is to run DDL in a session with a short timeout and retry: SET SESSION lock_wait_timeout = 3; then the ALTER. If it can't get its EXCLUSIVE within 3 s it fails, its pending request leaves the queue, and the waiting SELECTs go through at once (Demo: lock_wait_timeout rescue). Online schema change tools such as gh-ost and pt-online-schema-change do the same with their own short timeouts and retries.

Online DDL: INSTANT, INPLACE and COPY

ALTER TABLE picks an algorithm, or you name one with ALGORITHM= and it fails if that one can't be used. All three start with SHARED_UPGRADABLE (so reads and writes go on, but no second ALTER) and end with a short EXCLUSIVE. They differ in what happens in between.

Three timelines of the metadata lock held by ALTER TABLE. INSTANT: SU then a short X while the data dictionary changes. INPLACE: SU, a short X for prepare, a long SU while the index is built and concurrent DML goes to the online row log, then a short X to apply the log and commit. COPY: SU, a long SHARED_NO_WRITE while rows are copied into #sql-ib1047 and writes wait, then X for the rename. — Every algorithm needs EXCLUSIVE briefly; only COPY blocks writes for the whole run.

Algorithm	What it does	Concurrent DML	Example
`INSTANT`	changes only the data dictionary; rows are not touched (a row version tells old rows from new)	yes	`ADD COLUMN` (8.0.12+, any position since 8.0.29), `DROP COLUMN` (8.0.29+), default values
`INPLACE`	works inside InnoDB, no copy through the server; may still rebuild the table	yes with `LOCK=NONE`, kept in the online row log	`ADD INDEX`, `DROP INDEX`, rename column
`COPY`	creates `#sql-…`, copies every row through the server, renames	reads only (SHARED_NO_WRITE)	change a column type, `ALGORITHM=COPY` forced

INSTANT (Demo: INSTANT add column): the new column note is written into the data dictionary and the table's row version goes up by one; old rows keep their bytes, and a read fills in the column's default. INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS counts these changes; once it reaches the limit (64 in MySQL 8.0) the next column change has to rebuild the table.

INPLACE, LOCK=NONE (Demo: INPLACE add index, DML goes on): under a short EXCLUSIVE InnoDB creates the empty index and starts an online row log. It then downgrades to SHARED_UPGRADABLE and scans the clustered index, sorting keys into the new index. Writers get SHARED_WRITE as usual; each change they make to the table is also appended to the row log (its size is capped by innodb_online_alter_log_max_size, 128 MB). At the end the ALTER upgrades to EXCLUSIVE again, applies the row log so the index matches the rows, and commits. In the demo, S3 changes id 1 from 10 to 15 after the scan has passed it; the index holds the stale key (10,1) until the log replaces it with (15,1).

COPY (Demo: COPY blocks writes): the ALTER holds SHARED_NO_WRITE while it copies the rows into #sql-ib1047. SELECTs still read the old table; an UPDATE waits with Waiting for table metadata lock until the rename under EXCLUSIVE is done.

Note the trap in every algorithm: the last EXCLUSIVE has to wait for every transaction that touched the table during the build. A long transaction that starts in the middle of a 2-hour INPLACE build causes the same pile-up at the very end.

Compared with PostgreSQL

PostgreSQL has no separate metadata lock: its table locks (AccessShareLock for SELECT, AccessExclusiveLock for most ALTER TABLE) play the same role, and its lock queue gives the same pile-up when an ALTER waits behind a long transaction. The fix there is lock_timeout, whose default, 0, also means wait forever. See the PostgreSQL lock manager for the same pile-up step by step.

	MySQL	PostgreSQL
Table-level lock for DDL	metadata lock, EXCLUSIVE	AccessExclusiveLock
What SELECT takes	SHARED_READ	AccessShareLock
Waiters behind a queued DDL	yes: a pending X has priority	yes: FIFO, conflicts with queued requests
Timeout setting (default)	`lock_wait_timeout` (1 year)	`lock_timeout` (0 = forever)
Where to look	`performance_schema.metadata_locks`	`pg_locks`

See also How MySQL Runs a Query for the server and InnoDB layers, Two-Phase Locking and Deadlocks for InnoDB's row locks, InnoDB record, gap and next-key locks, InnoDB consistent and locking reads, and the InnoDB page and clustered index that an index build scans.

What the page leaves out

The other MDL lock types (SHARED, SHARED_HIGH_PRIO, SHARED_WRITE_LOW_PRIO, SHARED_READ_ONLY, SHARED_NO_READ_WRITE) and the scoped locks every write and DDL also takes: an INTENTION_EXCLUSIVE on the GLOBAL and SCHEMA namespaces (what FLUSH TABLES WITH READ LOCK and backups conflict with), plus locks on the tablespace and on foreign-key parents. The MDL deadlock detector, which picks a victim among waiters (DDL is preferred as the survivor). max_write_lock_count, the fast path for weak locks, and statement-duration tickets. InnoDB row locks taken by the UPDATE, the sort buffer and temporary files of an index build, the rebuild variant of INPLACE (ADD PRIMARY KEY, OPTIMIZE), rollback when the row log overflows, and how replicas apply DDL. Here each batch is 2 rows and each statement 1 s, so the timings are illustrative.