MySQL Instant DDL: Metadata Locks, Waiting Writers, and Query Pileups

A transaction reads one row and then remains open. Later, a deployment adds a column using ALTER TABLE ... ALGORITHM=INSTANT. The migration waits. A new request issues an ordinary SELECT against the same table, and that request waits too.

The original query has already finished. The schema change requires no table rebuild. The new query accesses a different row. Yet all three sessions participate in the same blocking sequence.

The explanation sits above InnoDB’s row-lock layer. The open transaction retains a metadata lock, the schema change requests an incompatible lock, and that pending request can make later queries queue behind it.

Instant DDL reduces the work required to change a table. It does not eliminate the coordination required to begin or complete that change.

Reproduce the Queue With Three Connections

Use a disposable database and three independent MySQL connections. A tiny table keeps data volume out of the explanation:

CREATE DATABASE mdl_demo;

CREATE TABLE mdl_demo.orders (
    id BIGINT PRIMARY KEY,
    status VARCHAR(20) NOT NULL
) ENGINE=InnoDB;

INSERT INTO mdl_demo.orders VALUES
    (1, 'NEW'),
    (2, 'NEW'),
    (3, 'DONE');

In Session 1, start a transaction and read one row:

START TRANSACTION;

SELECT *
FROM mdl_demo.orders
WHERE id = 1;

-- Leave the transaction open.

The query returns immediately. Do not commit yet.

In Session 2, request the schema change:

ALTER TABLE mdl_demo.orders
    ADD COLUMN source VARCHAR(32) NULL,
    ALGORITHM=INSTANT;

After the ALTER begins waiting for a metadata lock, issue another read in Session 3:

SELECT *
FROM mdl_demo.orders
WHERE id = 2;

This later read can also wait, despite accessing a different row. Committing Session 1 allows the migration to proceed, followed by the queued read.

MySQL’s verified Bug #106480 documents this three-connection behavior for MySQL 8.0. Test the sequence against your deployed version rather than assuming every DDL operation has identical locking details.

Separate Execution Cost From Lock Admission

For supported operations, ALGORITHM=INSTANT changes metadata without rebuilding the table. Whether a particular alteration qualifies depends on the operation, table characteristics, and server version. MySQL publishes these restrictions in its online DDL operation matrix.

That optimization removes potentially expensive data movement. It does not promise a maximum elapsed time.

The migration’s duration includes time spent acquiring required locks. A metadata change that executes quickly after admission can still wait behind an existing transaction for minutes.

This distinction should influence deployment planning. Estimating migration risk from table size alone misses the failure demonstrated here. A three-row table can experience the same admission problem as a much larger one.

The operational question is therefore twofold: how expensive is the change once admitted, and how long may it wait while affecting other requests?

The Blocker Can Be Idle

For tables used within a transaction, MySQL retains transactional metadata locks until the transaction ends. Finishing a SELECT does not necessarily release them. See MySQL’s metadata-lock lifetime documentation.

Consequently, searching only for slow queries can miss the session responsible.

Application code commonly stretches transactions beyond their SQL work. An endpoint reads data, calls a remote service, performs additional processing, and commits afterward. The database query takes milliseconds, but the transaction remains open throughout the external work.

A connection can therefore appear idle while still preventing a schema change.

Measure transaction age separately from query duration. Review transaction boundaries around network calls, user interaction, and background work. Moving unrelated operations outside a transaction can reduce exposure without changing the migration itself.

Preflight checks help identify existing blockers, but they cannot guarantee that another transaction will not start immediately afterward.

Why Later Readers Wait

Without the pending ALTER, the two reads in the example could coexist. The DDL request changes the scheduling situation.

MySQL normally prioritizes metadata write-lock requests over read-lock requests, with documented exceptions. That policy prevents a continuous stream of readers from indefinitely starving a writer. The priority rules appear in the metadata-lock acquisition documentation.

On a busy table, this creates a widening queue. One open transaction delays the migration, and the waiting migration delays newly arriving work.

Those requests can occupy application connections until the pool fills. Subsequent requests may then wait for a connection before reaching MySQL at all.

This is why a migration incident can look like general database saturation even when CPU and storage activity remain modest. The database is coordinating access, not necessarily executing expensive queries.

Inspect Ownership and Pending Requests

Start with a diagnostic query that connects waiting sessions to potential blockers:

SELECT
    object_schema,
    object_name,
    waiting_pid,
    waiting_lock_type,
    blocking_pid,
    blocking_lock_type
FROM sys.schema_table_lock_waits
WHERE object_schema = 'mdl_demo'
  AND object_name = 'orders';

The sys view documentation describes these session and lock relationships.

Use the result alongside transaction age and application ownership. Identify which connection belongs to the migration runner and which belongs to the application.

For lower-level investigation, inspect performance_schema.metadata_locks and correlate its owner thread identifiers with performance_schema.threads. Distinguish granted locks from pending requests rather than treating every displayed lock as an active blocker.

Do not automatically terminate the oldest connection. It may hold legitimate work whose rollback is expensive. When the migration is deferrable, removing its waiting request may be the less disruptive intervention.

Set the Correct Timeout

The familiar innodb_lock_wait_timeout controls InnoDB row-lock waits. It does not bound the metadata-lock wait responsible for this incident.

Configure the migration connection instead:

SET SESSION lock_wait_timeout = 3;

ALTER TABLE mdl_demo.orders
    ADD COLUMN source VARCHAR(32) NULL,
    ALGORITHM=INSTANT;

In MySQL 8.4, lock_wait_timeout governs metadata-lock acquisition attempts and defaults to 31,536,000 seconds. The timeout applies separately to each acquisition attempt, so it is not a universal three-second statement deadline. See the server variable reference.

Three seconds is illustrative. Choose a budget based on acceptable application disruption.

Apply the setting on the actual connection executing the migration. Setting it on a separate administrative connection has no effect on the migration session. A dedicated connection also avoids leaving unexpected timeout settings in an application pool.

Verify That Timeout Releases the Queue

Extend the reproduction using a fresh table definition.

Keep Session 1’s transaction open. Start the migration with a short metadata-lock timeout, then issue Session 3’s read while the migration is waiting.

When the ALTER times out and its pending request is removed, the later read should proceed in this isolated scenario even though Session 1 remains open.

That observation identifies the queue boundary. The two reads were compatible; the waiting schema change prevented the later one from advancing.

A failed migration must remain visible to deployment automation. Do not mark it complete merely because application traffic recovered. Retry with backoff and a bounded attempt count rather than repeatedly recreating the same queue.

After a client disconnect or ambiguous execution result, inspect the actual schema before retrying.

Make Admission Part of the Deployment Contract

A production migration policy should specify the permitted algorithm, metadata-lock wait budget, retry behavior, and conditions for postponement.

Monitor pending DDL alongside transaction age, connection-pool usage, and request latency. Alerting only on migration duration can detect the problem after application capacity has already been consumed.

Test both successful admission and blocked admission before deployment. The latter proves that the migration yields when production conditions are unfavorable.

Instant DDL remains valuable because it avoids unnecessary table reconstruction. Its safety depends on the surrounding lifecycle: transactions must finish, lock requests must be admitted, and waiting migrations must have a bounded opportunity to interfere with traffic.