MySQL Online DDL Still Needs a Lock Budget

“Online” DDL does not mean “no locking.” An InnoDB operation may allow concurrent reads and writes during much of its execution while still needing metadata locks at important boundaries. A long-running transaction can turn an apparently routine schema change into an application queue.

Treat algorithm, concurrency and lock acquisition as separate deployment decisions. Request the behavior you require so an unsupported operation fails instead of silently taking an unexpectedly disruptive path.

The concrete problem

This MySQL 8.4 example adds an index to a table that already exists. It is a deployment pattern, not a promise that every ALTER supports the same options. Confirm the operation-specific online DDL support matrix before using the statement.

Figure: Migration requests MDL → Concurrent transaction holds MDL → DDL begins after boundary.

A worked example

-- Run in a dedicated migration session.
SET SESSION lock_wait_timeout = 10;
ALTER TABLE orders
  ADD INDEX by_customer_created (customer_id, created_at),
  ALGORITHM=INPLACE,
  LOCK=NONE;
-- Observe metadata locks from a separate session:
SELECT OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS
FROM performance_schema.metadata_locks
WHERE OBJECT_NAME = 'orders';

Why the behavior matters

ALGORITHM expresses how MySQL should perform the change. LOCK expresses the concurrency level you require. Explicit clauses make the deployment contract visible: if the requested operation cannot satisfy them, investigate the rejection rather than removing safeguards reflexively.

Metadata-lock waits are different from InnoDB row-lock waits. lock_wait_timeout controls metadata lock acquisition; it is not interchangeable with innodb_lock_wait_timeout. A transaction that touched a table can retain metadata locks until it ends. The waiting DDL and subsequently queued application requests may create a larger incident than the original long transaction.

Failure modes and design tradeoffs

A low timeout limits one class of waiting; it does not bound every phase of the operation or eliminate resource pressure. Index construction still uses CPU, I/O and temporary space. Online modification logs and concurrent write volume also affect feasibility. Aborting an operation can require cleanup, so do not assume cancellation is instantaneous.

For an “instant” eligible change, check the exact MySQL version and restrictions. The fact that one ADD COLUMN is instant says nothing about a different alteration. Test the real table definition, including generated columns, indexes and row format.

What to verify before adoption

Rehearse against a representative copy. Hold a transaction that reads the target table, start the ALTER in another session and observe timeout behavior. Then release it and measure the actual operation under concurrent writes. Record disk headroom, replication effects and rollback procedure. Keep production execution behind the normal deployment review.

Primary reference

• MySQL documentation. Verify the exact deployed version and driver behavior before using the example in a production runbook.