MySQL Rollback: When It Leaves Half the Work Behind

MySQL rollback is supposed to undo a transaction completely, but under certain conditions it can leave part of the work behind. This happens when the transaction mixes transactional tables (like InnoDB) with non-transactional tables (like MyISAM), or when DDL statements (like CREATE, ALTER, DROP) are used inside the transaction, because MySQL cannot roll back those operations. In this article, you’ll learn exactly when this happens, why it happens, and how to prevent it.

Background: How MySQL Transactions Work

MySQL’s transactional behavior depends on the storage engine. InnoDB, the default engine since MySQL 5.5, supports ACID transactions with COMMIT and ROLLBACK. MyISAM, a legacy engine, does not support transactions at all—every statement is auto-committed immediately. When you run a transaction that touches both types of tables, the non-transactional changes are already permanent before you issue ROLLBACK.

Additionally, MySQL’s own documentation states that DDL statements (like CREATE, ALTER, DROP, RENAME, TRUNCATE) cause an implicit commit before and after they execute. This means even if you wrap them in a transaction, they cannot be rolled back. This is a common source of confusion for developers coming from other databases like PostgreSQL, where DDL is transactional.

How ROLLBACK Works in InnoDB

InnoDB uses an undo log to track changes made during a transaction. When you issue ROLLBACK, it reads that log and reverses each change, restoring the original row versions. This works for INSERT, UPDATE, and DELETE statements on InnoDB tables. However, the undo log does not capture DDL changes or changes to non-transactional engines.

For example, consider a transaction that updates a row in an InnoDB table and then drops a column from that table. The DROP COLUMN is a DDL statement that triggers an implicit commit, so the update is committed and the drop is permanent. A subsequent ROLLBACK will do nothing because the transaction is already over.

Common Scenarios Where Rollback Fails

The most frequent scenario is mixing storage engines. Suppose you have an InnoDB table orders and a MyISAM table order_log. You start a transaction, insert into orders, insert into order_log, then decide to roll back. The insert into order_log is already committed, so it remains. Only the InnoDB insert is undone.

Another scenario involves DDL inside a transaction. For example:

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
ALTER TABLE accounts ADD COLUMN last_update TIMESTAMP;
ROLLBACK;

The ALTER TABLE causes an implicit commit, so the UPDATE is committed and the column is added. The ROLLBACK has no effect. This is a classic mistake that leads to partial work.

How to Prevent Partial Rollbacks

To avoid this, follow these guidelines:

• Use only transactional storage engines (InnoDB) for all tables involved in a transaction.
• Do not include DDL statements inside a transaction. Move them outside or accept that they are non-rollbackable.
• If you must use MyISAM tables, treat them as auto-commit and design your application to handle partial failures.
• Consider using a transaction-safe logging pattern, such as writing to a separate InnoDB table, instead of a non-transactional log.
• Use SAVEPOINT to create partial rollback points within a transaction, but note that DDL still cannot be rolled back.

Comparing MySQL with Other Databases

PostgreSQL and Oracle support transactional DDL, meaning you can roll back CREATE and ALTER statements. MySQL does not. This is a fundamental difference that often surprises developers. In MySQL, even a simple CREATE TEMPORARY TABLE inside a transaction causes an implicit commit. This is documented behavior, but it’s easy to overlook.

Another difference is that MySQL’s ROLLBACK does not undo changes to system tables or to tables in the mysql database. Those are always auto-committed. So if you modify a user or privilege inside a transaction, it is immediately permanent.

Best Practices for Safe Transactions

To keep your data consistent, adopt these habits:

1. Check the engine of every table you touch: SHOW TABLE STATUS LIKE 'table_name';
2. Keep transactions short and focused on data manipulation (DML) only.
3. Use SET autocommit=0 and explicit COMMIT/ROLLBACK to control the transaction scope.
4. If you need to perform DDL, do it in a separate session or before starting the transaction.
5. Test your rollback logic in a development environment with a mix of engines to see what actually happens.

Frequently Asked Questions

Can I roll back a CREATE TABLE statement in MySQL?

No. CREATE TABLE is DDL and causes an implicit commit. It cannot be rolled back, even if you wrap it in a transaction. The table will exist after the statement, regardless of a later ROLLBACK.

What happens if I mix InnoDB and MyISAM tables in a transaction?

Changes to MyISAM tables are auto-committed immediately. If you later roll back, only the InnoDB changes are undone. The MyISAM changes remain, leaving partial work.

Does ROLLBACK undo changes to auto-increment counters?

No. InnoDB does not roll back auto-increment values. If you insert a row with an auto-increment column and then roll back, the counter is not reset. This is intentional to avoid concurrency issues.

Is there a way to make DDL rollbackable in MySQL?

No, not in the standard MySQL server. Some forks or tools like Percona Toolkit can help with online schema changes, but they don’t provide transactional DDL. You must design your schema changes to be reversible manually.

What is the difference between ROLLBACK and SAVEPOINT?

ROLLBACK undoes the entire transaction. SAVEPOINT creates a marker within the transaction, and you can roll back to that marker, undoing only the work after it. However, SAVEPOINTs do not help with DDL or non-transactional tables.

Why does my transaction still show changes after ROLLBACK?

This usually means you have non-transactional tables or DDL statements in the transaction. Check the engine of the tables and review your SQL for any DDL commands. Also, verify that autocommit is disabled for the session.

Related

• Will Leaving Location On Drain Battery? Facts
• Fusion Concepts: One Repository Each, No Real Machine?
• Washing Machine Water & Energy Use Explained

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2