WIUNIX ACADEMY
MySQL online DDL: reproduce and diagnose metadata-lock waits
Page 2 of 3. Review the earlier steps and prerequisites before continuing.
YOUR SYSTEM. YOUR STEPS.
Where are you working?
Choose your OS and version. The prerequisites, commands and troubleshooting follow your environment.
USE YOUR OWN VALUES
Configure this tutorial
Set the example values for your environment; related explanations and copy panels update together. Never enter passwords, private keys or tokens. Values stay in this tab and are not sent to the server or share links. Check actual DNS, certificates and network policy before running commands.
Different IP addresses, domains or ports? Open the settings below, change the values and select “Apply to tutorial”, then copy the updated commands.
Set values for my environment3 editable values
LET’S BUILD
Open three independent sessions to the fixture
Run this in three separate terminals and label them A, B and C. Keep the same approved server endpoint. They must be separate connections: one terminal running all SQL cannot demonstrate inter-session metadata contention. C needs the existing observer privilege. Each session records its connection ID so the lock chain can be tied to the right participant.
Authorized MySQL8.4 staging administrator
mysql --defaults-extra-file='/home/academy/.mysql-lab.cnf' --database='academy_mdl_20261028' --init-command='SELECT CONNECTION_ID(),@@hostname,VERSION()'Three labeled independent connection IDs are recorded.
LET’S BUILD
A: retain a read transaction temporarily
Only in session A, start a transaction and read the tiny fixture. Deliberately leave it open for the next two steps. This is the cause under investigation, not recommended application behavior. Do not run this against an application table or leave the exercise unattended.
Staging mysql session A
START TRANSACTION;
SELECT CONNECTION_ID(),count(*) FROM mdl_probe;A holds a transaction-scoped metadata lock after its read.
LET’S BUILD
B: request a bounded online index build
In session B, set only this session’s metadata lock wait timeout and request the secondary index explicitly with INPLACE and LOCK=NONE. These clauses make an unsupported operation fail instead of silently choosing a more intrusive path. The statement should wait for A’s metadata lock and eventually time out if A remains open. The timeout is lock_wait_timeout, not innodb_lock_wait_timeout for row locks.
Staging mysql session B
SET SESSION lock_wait_timeout=30;
ALTER TABLE mdl_probe ADD INDEX idx_category(category),ALGORITHM=INPLACE,LOCK=NONE;B waits on table metadata; if not released, error1205 bounds the exercise.
LET’S BUILD
C: inspect the waiter and blocker while B waits
Run promptly in C during the wait. The sys view reports waiting and blocking connection IDs and age without printing complete SQL or kill suggestions. The raw metadata_locks view distinguishes pending from granted locks; join OWNER_THREAD_ID to the threads table for client IDs. If the wait already timed out, an empty result is expected; repeat only the same bounded fixture after confirming its index state.
Staging mysql session C, observer
SELECT object_schema,object_name,waiting_pid,blocking_pid,waiting_query_secs FROM sys.schema_table_lock_waits WHERE object_schema='academy_mdl_20261028' AND object_name='mdl_probe';
SELECT t.PROCESSLIST_ID,m.LOCK_TYPE,m.LOCK_DURATION,m.LOCK_STATUS FROM performance_schema.metadata_locks m LEFT JOIN performance_schema.threads t ON t.THREAD_ID=m.OWNER_THREAD_ID WHERE m.OBJECT_SCHEMA='academy_mdl_20261028' AND m.OBJECT_NAME='mdl_probe';A’s granted lock and B’s pending metadata request explain the chain.
LET’S BUILD
A: end only your own synthetic transaction
First wait for B’s bounded timeout and record the error. Then roll back A’s read-only transaction. In an incident, terminating somebody else’s connection can trigger a large rollback or break the application; that is not the default remedy. Identify the owner and business consequence. In this fixture only your own three-row read transaction is ended.
Staging mysql session A
ROLLBACK;
SELECT CONNECTION_ID(),@@autocommit;A’s transaction ends and its metadata lock is released.
LET’S BUILD
B: recheck and build after the blocker is gone
Verify the index is absent after the timed-out DDL. If it exists, stop and inspect rather than issuing the same ADD again. Retry once after A has ended, then confirm the definition. DDL performs implicit commits and is not reversed by a later ROLLBACK. A three-row success does not establish production duration, disk requirement or replica impact.
Staging mysql session B
SHOW INDEX FROM mdl_probe;
ALTER TABLE mdl_probe ADD INDEX idx_category(category),ALGORITHM=INPLACE,LOCK=NONE;
SHOW CREATE TABLE mdl_probe;The secondary index is present only after the blocker is released.