LEARN. PRACTICE. GROW.DML and DDL have different boundaries
Start with a simple control: in an InnoDB transaction, insert a note, change 100 to 90 and issue ROLLBACK. The lab observed 100 again and no note, showing that the data unit was undone. A second path inserts another note and executes CREATE TABLE before rollback. This time the note committed and the table remained. The creation statement introduced a commit boundary that the later rollback does not cross. A script beginning with START TRANSACTION does not turn every following statement into one reversible unit. Read each statement and identify the boundaries that actually affect recovery.
DDL failure does not undo the earlier commit
In the executed example, ALTER TABLE tries to add an id column that already exists and returns 1060. The note inserted before that statement remains visible to another connection after ROLLBACK. Schema validation failure does not mean earlier work was undone. This distinction matters when an application writes a marker before starting a migration: the marker can commit without the intended change occurring. Do not use that record’s presence as the only completion criterion. Compare observed schema, affected data and migration mechanism state before resuming the release. A technical error needs an outcome check at each relevant boundary.
Temporary does not mean fully reversible
CREATE TEMPORARY TABLE did not commit the pending note in the experiment. After inserting a row into the temporary table and rolling back, the note and row disappeared, but the table remained in the same session. DROP TEMPORARY TABLE did not commit the note either; however, rollback did not recreate the removed table. A process depending on that object for its next phase needs explicit cleanup or reconstruction. Separate object existence from the transactional content it stores. Those two properties can have different outcomes within the same code block and after the same rollback command.
Different operations on the same object
The observed exception for creating and dropping temporary tables should not be extended to every DDL operation on those objects. In the lab, ALTER TABLE scratch added a column to the temporary table and committed the earlier note. Subsequent rollback left the note committed and the column present. Therefore, temporary describes object scope but does not alone answer the commit question. A migration inventory should identify the specific statement, object type, open transaction and subsequent verification. Test the failure path as carefully as the path where every command succeeds, because a partially completed script can cross several irreversible transaction boundaries.
TRUNCATE and savepoints in a release
TRUNCATE TABLE removed both synthetic rows and committed the preceding note. ROLLBACK did not restore the data. On another path, a savepoint was created before ALTER TABLE added a column; attempting to return to that savepoint produced 1305 and the column remained. These results are not a production recovery strategy: they demonstrate why rollback and savepoint alone cannot undo those commands. Before a destructive change, the release owner needs recovery suitable for the service, with test data and acceptance criteria. The lab did not restore backups or exercise actual data loss. Keep that execution limit explicit in the handover.
Temporary tables and session reuse
A temporary items table containing row 9 hid the permanent table of the same name only on its creating connection. Another connection continued reading permanent rows 1 and 2. After DROP TEMPORARY TABLE, the first connection saw the permanent table again. In a workflow reusing connections, this property requires care with residual objects and script naming. The experiment used direct connections and did not validate a particular pool. Document the need to clean or reset session state and check the actual reuse mechanism before concluding that the next request receives a clean environment. Table naming alone does not identify which object a session is reading.
START TRANSACTION;
INSERT INTO notes VALUES(3,'synthetic migration marker');
ALTER TABLE items ADD COLUMN id INT; -- Existing column: observed error 1060.
ROLLBACK;
-- On another owned connection, the earlier note remains committed.
SELECT * FROM notes WHERE id=3A note inserted before an invalid ALTER TABLE remained committed despite error 1060 and a later ROLLBACK.
Common pitfalls
Assuming transactional DDL; trusting an earlier savepoint; treating all operations on temporary tables as equivalent.
Related topics: Atomicity and schema changes · Metadata locks and release acceptance
The recovery plan must follow actual commits and confirm schema, data and marker state.
Reference: Implicit commit and temporary exceptions · MySQL 9.7 LTS with InnoDB reference semantics