Which MySQL Statements Commit a Transaction Automatically
Some MySQL statements can't run inside a transaction, so MySQL commits the transaction first and doesn't mention it. Your next ROLLBACK then has nothing to undo. It's why a failed migration can leave half its changes behind. Here's the behaviour, and the statements that cause it.
Prerequisites
- MySQL, and a table. The examples use MySQL 9.7.4 in Docker, the
mysqlclient from How to Connect to MySQL in a Docker Container, and aproductstable.
See It Happen
The quantity starts at 50:
START TRANSACTION;
UPDATE products SET QuantityOnHand = 49 WHERE Id = 3;
CREATE TABLE notes (Id int PRIMARY KEY);
ROLLBACK;
SELECT Id, Sku, QuantityOnHand FROM products WHERE Id = 3;
+----+-------------+----------------+
| Id | Sku | QuantityOnHand |
+----+-------------+----------------+
| 3 | CBL-HDMI-2M | 49 |
+----+-------------+----------------+
1 row in set (0.001 sec)
49. The CREATE TABLE committed the UPDATE, and the ROLLBACK had nothing left to undo. No warning, and no error.
MySQL's implicit commit page says: "The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a COMMIT before executing the statement."
Which Statements Do It
The page has the full list. These are the ones you're most likely to run:
| Kind | Statements |
|---|---|
| Changing the structure | CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE, RENAME TABLE, CREATE INDEX, DROP INDEX |
| Changing accounts | CREATE USER, ALTER USER, DROP USER, GRANT, REVOKE |
| Transaction control | BEGIN, START TRANSACTION, SET autocommit = 1 when it was 0 |
| Locking | LOCK TABLES, and UNLOCK TABLES when tables are locked |
It Commits Even If the Statement Fails
This one I didn't expect. The table in this TRUNCATE doesn't exist:
START TRANSACTION;
UPDATE products SET QuantityOnHand = 49 WHERE Id = 3;
TRUNCATE TABLE __nosuchtable;
ROLLBACK;
SELECT Id, Sku, QuantityOnHand FROM products WHERE Id = 3;
ERROR 1146 (42S02): Table 'inventory.__nosuchtable' doesn't exist
+----+-------------+----------------+
| Id | Sku | QuantityOnHand |
+----+-------------+----------------+
| 3 | CBL-HDMI-2M | 49 |
+----+-------------+----------------+
1 row in set (0.001 sec)
The statement failed, and the UPDATE was committed all the same.
Temporary Tables Don't Commit
CREATE TEMPORARY TABLE is the exception:
START TRANSACTION;
UPDATE products SET QuantityOnHand = 49 WHERE Id = 3;
CREATE TEMPORARY TABLE scratch (Id int PRIMARY KEY);
ROLLBACK;
SHOW WARNINGS;
SELECT Id, Sku, QuantityOnHand FROM products WHERE Id = 3;
+---------+------+-----------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------+
| Warning | 1751 | The creation of some temporary tables could not be rolled back. |
+---------+------+-----------------------------------------------------------------+
1 row in set (0.000 sec)
+----+-------------+----------------+
| Id | Sku | QuantityOnHand |
+----+-------------+----------------+
| 3 | CBL-HDMI-2M | 50 |
+----+-------------+----------------+
1 row in set (0.001 sec)
The UPDATE was rolled back this time, and 50 is back. The temporary table stays, though, and that's what the warning is about: Fixed: Warning 1751.
Why This Matters for Migrations
EF Core wraps each migration in START TRANSACTION and COMMIT, as How to Generate a SQL Script from EF Core Migrations shows. Most migrations are ALTER TABLE or CREATE TABLE, so each statement commits as it runs. If the third statement of a migration fails, the first two stay. Keep each migration small, and test it on a copy first.
Common Mistakes
| Mistake | What happens | Do this instead |
|---|---|---|
Mixing ALTER TABLE with data changes in one transaction | The data changes are committed at the ALTER | Change the structure separately |
| Expecting a failed DDL statement to leave the transaction open | It commits anyway | Finish the transaction first |
Relying on ROLLBACK to undo a migration | The changes are already permanent | Write a way back, or test on a copy |
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
ROLLBACK didn't undo my change | A statement in between committed | The fix |
| A migration failed halfway, and left changes behind | DDL commits as it goes | Revert it, or fix it by hand |
Warning 1751 after a ROLLBACK | A temporary table can't be rolled back | Drop the table yourself |
Conclusion
Statements that change the structure, change accounts or start a new transaction commit whatever you had open, even when they fail. Finish your transaction before running them, and don't count on ROLLBACK to undo a migration.
Additional Resources
- Statements That Cause an Implicit Commit — the full list.
