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
executing the statement.
Data definition language (DDL)
statements that define or modify database objects.
ALTER DATABASE ... UPGRADE DATA DIRECTORY
CREATE TABLE and
DROP TABLE statements do not
commit a transaction if the
keyword is used. (This does not apply to other operations on
temporary tables such as
INDEX, which do cause a commit.) However, although
no implicit commit occurs, neither can the statement be rolled
back, which means that the use of such statements causes
transactional atomicity to be violated. For example, if you
TEMPORARY TABLE and then roll back the transaction,
the table remains in existence.
Beginning with MySQL 5.1.15,
CREATE TABLE ...
SELECT causes an implicit commit before and after
the statement is executed when you are creating nontemporary
tables. (No commit occurs for
CREATE TEMPORARY TABLE
... SELECT.) This is to prevent an issue during
replication where the table could be created on the master
after a rollback, but fail to be recorded in the binary log,
and therefore not replicated to the slave. For more
information, see Bug #22865.
Statements that implicitly use or modify
tables in the
Beginning with MySQL 5.1.3,
DROP USER, and
RENAME USER cause an implicit
commit. Beginning with MySQL 5.1.23,
SET PASSWORD statements cause
an implicit commit.
TABLES commits a transaction only if any tables
currently have been locked with
TABLES. This does not occur for
FLUSH TABLES WITH READ
LOCK because the latter statement does not acquire
Transactions cannot be nested. This is a consequence of the
implicit commit performed for any current transaction when you
TRANSACTION statement or one of its synonyms.
Statements that cause an implicit commit cannot be used in an
XA transaction while the transaction is in an
statement differs from the use of the
keyword that starts a
END compound statement. The latter does not cause an
implicit commit. See Section 13.6.1, “BEGIN ... END Compound-Statement Syntax”.
Data loading statements.
INFILE. Before MySQL 5.1.12,
INFILE caused an implicit commit for all storage
engines. As of MySQL 5.1.12, it causes an implicit commit only
for tables using the
engine. For more information, see Bug #11151.