Implicit Commits

Some statements commit transactions even when you don’t call COMMIT explicitly. This situation is called an implicit commit, and if you aren’t aware you’re doing a commit, you can end up with an inconsistent state.

A lot of statements cause implicit commits. I won’t list them here, because they can vary from version to version. The general rule is that DDL, transaction-related, and administrative statements cause implicit commits, whereas those that work with data do not.

One symptom of an unanticipated implicit commit is when you see unwanted data in tables even though the statements inserting that data were supposed to be rolled back. Here’s an example:

mysql> CREATE TABLE t1(f1 INT) ENGINE=InnoDB;
Query OK, 0 rows affected (0.14 sec)

mysql> SELECT * FROM t1;
Empty set (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(100);
Query OK, 1 row affected (0.03 sec)

mysql> CREATE TABLE t2 LIKE t1;
Query OK, 0 rows affected (0.19 sec)

mysql> INSERT INTO t1 VALUES(200);
Query OK, 1 row affected (0.02 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)