Schema, DDL, and release acceptance | MySQL: development and safe operations

Concept and mechanism

DDL does not automatically share the rollback guarantees expected of DML. ALTER TABLE causes implicit commit; running UPDATE and ALTER in the same session does not create one unit reversible by ROLLBACK. Atomic DDL protects consistency of supported operations without turning DDL into freely user-controlled transactions. Specific exceptions exist, such as CREATE TEMPORARY TABLE without implicit commit, whose creation is also not undone by rollback. The alteration algorithm defines physical work and possible concurrency. INSTANT, INPLACE, and COPY have different capabilities depending on the operation and table structure.

Guided application

In a fictional rollout, quickly removing a column can break old instances still active. Introduce compatibility, migrate usage and data, confirm consumers, and only then retire the old structure. If INSTANT is rejected, reassess time, space, and locks before accepting another algorithm. Online DDL also needs metadata locks: an old transaction that only read the table can delay the change. Define a maximum wait, transaction owner, and deferral criterion. Acceptance should check schema, application, batch impact, and return path. Backup and an inverse change do not mean rollback is immediate or needs no reconciliation.

IN PRACTICE

A transaction after SELECT can retain a metadata lock and block ALTER TABLE until it ends.

Common pitfalls

Atomic as unrestricted rollback; INSTANT as compatible; online as lock-free; removing the algorithm without impact measurement.

Related topics: Types and data contracts · Deterministic queries and plans · InnoDB transactions and error paths

Take this idea with you

A release needs functional compatibility and a demonstrated operational window.

Create account

Reference: DDL and implicit commits · MySQL 9.7 LTS with InnoDB reference semantics