Can MySQL 8.0 Atomic DDL undo schema changes during a transaction rollback?

Question

MySQL 8.0 introduced atomic DDL for InnoDB to ensure that data dictionary changes are applied consistently. However, DDL statements typically trigger implicit commits, which prevents the use of a standard ROLLBACK to revert a schema change if a subsequent operation in the same session fails.

While atomic DDL ensures the server does not leave the data dictionary in a corrupted state during a crash, it is unclear how this interacts with user-initiated transaction boundaries when mixing DDL and DML. Specifically, when using ALGORITHM=INSTANT for adding columns, the metadata is updated immediately.

Does the atomicity of DDL provide any mechanism to revert a schema change within a single session's transaction?
  Which specific constraints prevent ROLLBACK from undoing an ALTER TABLE operation in MySQL 8.0?

Zoha · Answer

Short answer
No.  In MySQL 8.0 the "atomic DDL" feature guarantees that a DDL statement is either fully applied or fully rolled back in the event of a crash, but it does not make the statement transactional in the sense that a user‑initiated ROLLBACK can undo it.

Why the difference?

Implicit commits: Every DDL statement (CREATE, ALTER, DROP, RENAME, TRUNCATE, etc.) automatically commits the current transaction before it starts and after it finishes. The statement therefore never runs inside an open user transaction that could later be rolled back.
Atomicity scope: "Atomic DDL" refers to the internal handling of the data dictionary, storage engine changes and binary‑log entries. The engine writes all of those parts as a single atomic unit; if the server crashes mid‑DDL, recovery will either commit or roll back that unit. This is crash‑safety, not user‑transaction semantics.
ALGORITHM=INSTANT: For instant‑add‑column operations only the data‑dictionary metadata changes. The change is committed immediately and is not part of any transaction. A compensating ALTER TABLE … DROP COLUMN is the only way to revert it.

What you can do instead

Take a backup (full dump or filesystem snapshot) before performing a schema change that you might want to undo.
Generate a “reverse” DDL script in advance (e.g., ALTER TABLE t DROP COLUMN c) and run it manually if the subsequent operation fails.
Use an online‑schema‑change tool (gh‑ost, pt-online-schema-change, or MySQL’s own ALTER TABLE … ALGORITHM=INPLACE when appropriate) that keeps the original table until the cut‑over is safe.
If you need true transactional schema changes, consider using a database that supports transactional DDL (e.g., PostgreSQL) or a migration framework that records the state and can roll back via separate scripts.

Missing detail that might affect the recommendation
Are you certain that the tables involved use the InnoDB engine? Non‑transactional engines (e.g., MyISAM) do not benefit from atomic DDL at all, and the behavior described above applies only to InnoDB in MySQL 8.0.

Can MySQL 8.0 Atomic DDL undo schema changes during a transaction rollback?

1 answer

Short answer

Why the difference?

What you can do instead

Missing detail that might affect the recommendation

Your perspective belongs here.

0 question comments