LEARN. PRACTICE. GROW.A plain read can delay a schema change
Absence of FOR UPDATE does not mean absence of every lock. In an open transaction, the lab’s plain SELECT retained a metadata lock on items. Another connection attempted to change the structure and became pending. No balance update was needed to reproduce the wait. The lock protects use of the object during the transaction, rather than reserving a business value like a row-locking read. In a release incident, identify the open transaction and its purpose; treating every wait as a conflict between writers can hide the actual holder preventing the change. Query completion and transaction completion are separate events.
Observe metadata and identity
The script queried performance_schema.metadata_locks and joined OWNER_THREAD_ID to performance_schema.threads to obtain PROCESSLIST_ID. It confirmed a granted lock with TRANSACTION duration on the holder connection and a pending request on the ALTER connection. This connects observation to clients created by the experiment itself. Do not derive connection identifiers from fields representing locks or internal addresses. In an actual incident, add the transaction owner, purpose and impact before intervening. The lab observer was local root, so the experiment does not establish that an operations account has these privileges or validate the team’s authorization process.
Metadata timeout and already committed work
The ALTER connection had lock_wait_timeout set to one second and received 1205 while another transaction retained metadata. The intended column was not added. However, the note inserted before ALTER committed because that statement had implicitly ended the earlier transaction. This observation combines two boundaries: waiting for schema access and committing preceding work. Do not choose recovery policy from 1205 alone, since the same number appeared in the InnoDB contention lab with another statement and scope. Record the statement, relevant variable and committed state before deciding to retry or reconcile. A shared error number does not establish an identical business outcome.
LOCK=NONE and the change window
The second path added an index with ALGORITHM=INPLACE and LOCK=NONE. Nevertheless, the request remained pending while the other transaction’s read retained metadata. After controlled rollback on that connection, the index was created. The option does not promise zero latency, absence of metadata locks or negligible cost. This experiment has only two rows and does not measure production-table duration, I/O impact or replica lag. Use the result to frame acceptance questions: have old transactions been identified, has the wait deadline been set and is someone accountable for the decision if the change exceeds its window?
Index failure and the meaning of atomic DDL
Both rows had amount=100. Attempting to add a UNIQUE index on that column returned 1062; the original rows remained and the intended index did not exist. The result demonstrates a controlled SQL failure with state observed after the operation. Documentation describes atomic DDL guarantees, but that does not turn DDL into statements reversible through the user’s transaction. Nor does it permit claiming that this script exercised crash recovery: the server was not interrupted during the change. In release review, keep DDL operation integrity, commitment of preceding work and recovery from infrastructure failures separate, since those failures require their own tests.
Define acceptance with bounded evidence
A useful handover identifies expected schema, data outcome, migration markers and consumer tests. The fourteen local groups help build that reasoning but do not automatically approve a migration framework or banking application. The experiment used one disposable native MySQL 9.7.2 server, three owned connections and two concurrent statements, closing clients and server at the end. There was no TCP, real credential, pool, replica, crash, benchmark or human workshop. The actual service still needs a representative window, agreed rollback or recovery criteria, application testing and review by its technical owner. Record which claims come from execution and which remain acceptance work.
-- Connection A keeps a transaction open after a plain read.
START TRANSACTION;
SELECT * FROM items;
-- Connection B runs concurrently and may wait for metadata.
ALTER TABLE items ADD INDEX by_amount(amount), ALGORITHM=INPLACE, LOCK=NONE;
-- Connection A ends its transaction after the owned wait is observed.
ROLLBACKA transaction that only ran SELECT retained a metadata lock. ALTER TABLE with LOCK=NONE waited until that transaction ended.
Common pitfalls
Reading LOCK=NONE as zero waiting; diagnosing through row locks alone; claiming crash recovery from a controlled SQL failure.
Related topics: Atomicity and schema changes · Metadata locks and release acceptance
A release needs to observe waiting, confirm the outcome and establish the application contract within the scope actually tested.
Reference: Transaction duration of metadata locks · MySQL 9.7 LTS with InnoDB reference semantics